Any Good Patching Reports in KACE out there since v10?
Is it just me or is the reporting in KACE nearly all unusable when it comes to giving an accurate and clear status in regard to PATCHING? Out of the dozens upon dozens report templates, not one gives a good report that says This machine is missing this many patches, has this many patches, has been last patched on this date etc. We have to completely build it from scratch, trial and error, it comes back with servers in multiple patch labels, or something else that basically makes the report unusable as it is. Then when we finally get a report about at the point where its acceptable, the v10 update totally kills all the reports we had and we are back at the starting point. Then to top it off, you would think the update would have new reports, or at least they would fix the templates to work in the new version but no. You run templates again and they are broken. So now we have broken custom reports and broken templates. Having the ability to provide easy, accurate reports in regards to patching seems like a high priority. I dont know why with KACE this seems to be a second thought.
With this being said, does anyone have any good patching reports that work in v10, and easy to customize based on a device label they are in, in one patch label?
One of the older reports out there that was quite good. I managed to modify. Here's the code I have so far. I can't remember who wrote it off the top of my head, but it's here on itninja.
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS OS_Name,
M.ID as Mid,
SUM(MS.DETECT_STATUS='PATCHED') AS PATCHED,
SUM(MS.DETECT_STATUS='NOTPATCHED') AS NOT_PATCHED,
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 PP.PUBLISHER = 'Microsoft Corporation'
AND L.NAME rlike 'Insert your group labels here'*/
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME
you can build your own reports.
These two reports are good examples: