Need help updating custom sql report from version 9 to version10 -> Patch listing completion rate by machine - concise
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 rlike 'DL_Dell_Devices_All|DL_Macintosh_Devices_All'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
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, 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 rlike 'DL_Dell_Devices_All|DL_Macintosh_Devices_All' GROUP BY M.NAME ORDER BY PERCENT_PATCHED, M.NAME")
Answers (1)
Hi,
version 10 has a new and overhauled Patching Module, probably all reports or custom reports and labels, might not work:
https://support.quest.com/kace-systems-management-appliance/kb/264454/deprecated-patching-items-in-the-10-0-release
I would start there.
and here:
https://support.quest.com/kace-systems-management-appliance/kb/309572/updating-custom-sql-queries-for-10-0
Comments:
-
Thanks for 50,000 ft answer but doesn't help with the above issue of correcting the report. - jgunter 5 years ago
-
you should check this post from another ITNinja member, asking about this same exact report
After accessing the KACE SMA Database and modding the SQL sentence a bit, it seems the report is working for them on version 10:
https://www.itninja.com/question/k1000-custom-report-broken-in-version-10 - Channeler 5 years ago-
^This helped. Grazie - jgunter 5 years ago
-
I was able to massage a report to get the same functionality, no guarantees, but should show the differences.
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 - htharp@omig.com 5 years ago
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. - Channeler 5 years ago