/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Need help converting K1000 (Systems Management Appliance) patch report so it will work on version 10.x

01/09/2020 213 views

I had a perfect patching report that ran weekly which would generate a report of patched installed, patches needed and percentage patched.  Since the K1000 SMA 10.x update, the query will not run, citing the following access denied error:

mysqli error: [1142: SELECT command denied to user 'R1'@'localhost' for table 'PATCHLINK_PATCH'] in EXECUTE("SELECT M.NAME AS MACHINE_NAME, M.ID, 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 MACHINE M LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON M.ID = MS.MACHINE_ID LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON MS.PATCHUID = PP.UID LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON PP.UID = PPS.PATCHUID JOIN MACHINE_LABEL_JT ML ON M.ID = ML.MACHINE_ID JOIN LABEL L ON ML.LABEL_ID = L.ID WHERE L.NAME = 'Windows Servers - ALL - 8-8-18 - RN' AND PP.IS_SUPERCEDED = 0 # AND PPS.STATUS = 0 # GROUP BY M.NAME ORDER BY PERCENT_PATCHED, M.NAME")


Here is the full text of my query:

SELECT
      M.NAME AS MACHINE_NAME,
      M.ID,
      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     MACHINE M
         LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON M.ID = MS.MACHINE_ID
         LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON MS.PATCHUID = PP.UID
         LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON PP.UID = PPS.PATCHUID
         JOIN MACHINE_LABEL_JT ML ON M.ID = ML.MACHINE_ID
         JOIN LABEL L ON ML.LABEL_ID = L.ID
WHERE   L.NAME = 'Windows Servers - ALL - 8-8-18 - RN'
        AND PP.IS_SUPERCEDED = 0
        # AND PPS.STATUS = 0 #
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME


Can anyone out there please help me convert this to work with version 10.x of KACE?


-Russ N.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

Give this a try as I did not test it.


SELECT
      M.NAME AS MACHINE_NAME,
      M.ID,
      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     MACHINE M
         LEFT JOIN PATCH_MACHINE_STATUS MS ON M.ID = MS.MACHINE_ID
         LEFT JOIN KBSYS.PATCH PP ON MS.PATCH_ID = PP.ID
         LEFT JOIN PATCH_STATUS PPS ON PP.ID = PPS.PATCH_ID
         JOIN MACHINE_LABEL_JT ML ON M.ID = ML.MACHINE_ID
         JOIN LABEL L ON ML.LABEL_ID = L.ID
WHERE   L.NAME = 'Windows Servers - ALL - 8-8-18 - RN'
        AND PP.IS_SUPERCEDED = 0
        # AND PPS.STATUS = 0 #
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME


The following KB outlines the DB schema changes in 10.0

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

Answered 01/09/2020 by: KevinG
Second Degree Brown Belt

  • Kevin, THANK YOU SO MUCH...THIS WORKED PERFECTLY!! Thanks also for the link to the schema changes. You made my week!

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