Anyone have a new link to the KACE Excellence reports? I had several of them that dealt with patching and when version 10 was applied they all quit working.
Here is the query for the one I use the most. It was called Patch listing completion rate by machine - detailed
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 LIKE 'P-EDG%'
GROUP BY M.NAME, PATCH_NAME
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE
Answers (1)
The SQL below was posted the other day by a forum member. This may work for you , if not it may help understand what changes you need to make to your existing report.
Here is a KB that lists changes that were made to accommodate the new patching module in 10.x.
https://support.quest.com/kb/309572/updating-custom-sql-queries-for-10-0
SELECT M.NAME, OS_NAME,SUM(MS.PATCH_INSTALLED) as 'Installed',SUM(MS.PATCH_APPLICABLE) as 'Needed',GROUP_CONCAT(IF(DETECT_STATUS = 'NOTPATCHED', TITLE, null)) AS 'Needed Patches',FLOOR(ROUND((SUM(PATCH_INSTALLED) / COUNT(MS.PATCH_ID)) * 100,1)) AS PERCENT_PATCHEDFROM ORG1.PATCH_MACHINE_STATUS MSJOIN 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)WHEREPPS.STATUS = 0AND PPS.IS_SUPERCEDED = 0AND YEAR(PP.CREATION_DATE) = YEAR(NOW())GROUP BY M.IDORDER BY M.NAME
