/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


K1000 Custom Report broken in Version 10

10/15/2019 448 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?
      • This is what I added to include a specified device label. I needed to add 2 joins and that allowed me to specify label.name = "Server Device Patch Test Group." You of course will want to change that name to one that is meaningful to you and team.

        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,
        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)

        JOIN
        MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = M.ID
        JOIN
        LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID


        WHERE
        PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0
        AND PPS.IS_SUPERCEDED = 0
        AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'
        AND SC.CLIENT_CONNECTED = 1
        and LABEL.NAME = "Server Device Patch Test Group"


        GROUP BY MS.MACHINE_ID
        ORDER BY PERCENT_PATCHED , M.NAME

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ