/build/static/layout/Breadcrumb_cap_w.png

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

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.


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: KevinG 4 years ago
Red Belt
0

Top Answer

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


Comments:
  • Kevin, THANK YOU SO MUCH...THIS WORKED PERFECTLY!! Thanks also for the link to the schema changes. You made my week! - Rusty-Dog23 4 years ago

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