SQL report for computer count and patches completed and not
Hey guys,
I saw a bunch of reports of how to make a list of servers and how to get data on each one whether they were patched or not. I was wondering if there is a way to make it one step high level. I was wondering if anybody has come across making a SQL report on the count of computers in a label and count or % of how they are patched as a whole group.
instead of seeing 30 servers i just wanted it to be like this
30 Servers
Total patches applied in the last 90 days
Total patches not applied in the last 90 days
Percentage of patches not applied in the last 90 days.
any help or insight would be awesome. I am not the best at SQL writing.
5 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
I don't think this would be a very good "compliance" report since some apps would have had multiple patches released in 90 days (e.g. Chrome) and some will have not had a patch released in that time (e.g. 7-zip). I would go by active and non-superseded patches instead of doing it based on release date.
Unless you mean you want the report to be about detect/deploy jobs which have happened in the last 90 days? - JasonEgg 6 years ago
SELECT M.NAME AS SUM,
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 RELEASEDATE > DATE_SUB(NOW(),INTERVAL 90 DAY)
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
AND L.NAME rlike 'NB VMWare Servers|PowerEdge 2950 Servers|PowerEdge R420 Servers'
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED DESC, M.NAME - ckiernik 6 years ago