/build/static/layout/Breadcrumb_cap_w.png
10/15/2019 139 views

The report below was used to give me Concise listing of completion rate for all active critical patches by machine (servers)



*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME = 'server'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME



What I see now after the upgrade to version 10:


Error Running Report

mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE("SELECT M.NAME AS MACHINE_NAME, OS_NAME AS WINDOWS_VERSION, PP.TITLE AS PATCH_NAME, SUM(MS.STATUS='PATCHED') AS PATCHED, SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED, ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED') +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED, DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED FROM PATCHLINK_MACHINE_STATUS MS JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID) JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID) JOIN MACHINE M ON (M.ID = MS.MACHINE_ID) JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) JOIN LABEL L ON (ML.LABEL_ID = L.ID) WHERE PP.IMPACTID = 'Critical' AND PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0 AND L.NAME = 'DS' GROUP BY M.NAME, PATCH_NAME ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE")

0 Comments   [ + ] Show comments

Comments


All Answers

2

SQL reports from versions 9.1 or older are not going to work with version 10 , the database changed a lot in this version, because of the new patching engine, those reports will not work.


You could attach to the KACE SMA Database using MySQL Workbench or HeidiSQL tools, and explore the database to perform the changes you need.


Database Schema Changes for version 10:

https://support.quest.com/es-es/kb/309180/kace-sma-10-0-database-schema-changes



Here's a Guide with tips to upgrade old reports to version 10:

https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0



And here's a guide that will tell you how to access your KACE SMA Database and explore it:

https://support.quest.com/kb/114992/can-i-access-the-k1000-appliance-database-using-a-third-party-reporting-tool-


With those three links, a SQL report, SQL label or manual label, could be updated.

Answered 10/15/2019 by: Channeler
Red Belt

  • Thanks! I have accessed to KACE SMA Database and i have changed the SQL Query like this:

    SELECT M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    SUM(MS.DETECT_STATUS='PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS='NOTPATCHED') AS NOTPATCHED,
    ROUND((SUM(MS.DETECT_STATUS='PATCHED')/(SUM(MS.DETECT_STATUS='PATCHED')
    +SUM(MS.DETECT_STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
    FROM PATCH_MACHINE_STATUS MS
    JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
    JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
    JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
    JOIN LABEL L ON (ML.LABEL_ID = L.ID)
    WHERE PPS.STATUS = 0
    AND PP.IS_SUPERCEDED = 0
    AND L.NAME = 'WINDOWS'
    GROUP BY M.NAME
    ORDER BY PERCENT_PATCHED, M.NAME
    • This content is currently hidden from public view.
      Reason: Removed by member request For more information, visit our FAQ's.
    • Thanks, that helpded me..
      i added also the "PP.ImpactID" which was changed to "PP.severity" to show the critical updates:
      Here is my new report:

      SELECT
      M.NAME AS MACHINE_NAME, USER_NAME, OS_NAME AS WINDOWS_VERSION, LAST_SYNC,
      SUM(MS.DETECT_STATUS='PATCHED') AS PATCHED,
      SUM(MS.DETECT_STATUS='NOTPATCHED') AS NOTPATCHED,
      ROUND((SUM(MS.DETECT_STATUS='PATCHED')/(SUM(MS.DETECT_STATUS='PATCHED')
      +SUM(MS.DETECT_STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
      FROM PATCH_MACHINE_STATUS MS
      JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
      JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
      JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
      JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
      JOIN LABEL L ON (ML.LABEL_ID = L.ID)
      WHERE PP.SEVERITY = 'Critical'
      AND PPS.STATUS = 0
      AND PP.IS_SUPERCEDED = 0
      AND L.NAME like 'Servers'
      GROUP BY M.NAME
      ORDER BY PERCENT_PATCHED, M.NAME
  • I have modified a new sample to work with version 10; The caveat is that because there is a new patching system with new tables, the history was not ported over, spent a couple of days trying to figure that out.

    SELECT
    M.NAME AS MACHINE_NAME,
    OS_NAME AS WINDOWS_VERSION,
    M.Notes as Notes,
    M.LAST_SYNC as Last_Inventory,
    M.LAST_REBOOT as Last_Re_Boot,
    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,
    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,
    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =
    'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS
    PERCENT_PATCHED
    FROM
    PATCH_MACHINE_STATUS MS
    JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)
    JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)
    JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)
    JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)
    WHERE

    PPS.STATUS = 0
    AND PPS.IS_SUPERCEDED = 0
    GROUP BY MS.MACHINE_ID
    ORDER BY PERCENT_PATCHED , M.NAME
    • Hi and thanks. Where would you input a line to only return devices within a device label?