K1000 Custom Report broken in Version 10
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")
Answers (1)
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:
And here's a guide that will tell you how to access your KACE SMA Database and explore it:
With those three links, a SQL report, SQL label or manual label, could be updated.
Comments:
-
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 - imanolces.cespedes 5 years ago-
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 - christoph.charvat 5 years ago
-
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 - htharp@omig.com 5 years ago-
Hi and thanks. Where would you input a line to only return devices within a device label? - lama01 5 years ago
-
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 - j.catlin@nmscolo.org 4 years ago
