Machines missing patches report
Hi,
I am trying to create a report to list number of missing patches on each machine. This report is exactly what I am looking for
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
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
GROUP BY M.NAME
ORDER BY not_patched desc, M.NAME
But for some reason it doubles every value.
for example if a machine has 46 patches missing it'll list it here as 92.
for example if a machine has 46 patches missing it'll list it here as 92.
Any help is much appreciated.
2 Comments
[ + ] Show comments
Answers (0)
Please log in to answer
Be the first to answer this question
This is my first experience with SQL so don't hate on this query too bad, but if anyone needs this type of a report this is what i ended up doing
SELECT NAME,PS.PATCHUID from MACHINE M
join PATCHLINK_MACHINE_STATUS MS ON (M.ID = MS.MACHINE_ID)
JOIN PATCHLINK_PATCH_STATUS PS ON (MS.PATCHUID = PS.PATCHUID)
WHERE
MS.status != 'PATCHED' AND
PS.IS_SUPERCEDED = '0'
Generating report in an excel lets me use pivot tables to get counts of missing patches per machine.
I couldn't find which table had patchuid associated with KB #. If anyone knows that let me know so i can make initial report a bit more ppl readable.
If anyone knows of a good way of extracting this data please let me know. This is functional but is not pretty. - aemalas 5 years ago
SELECT NAME,PS.PATCHUID,IDENTIFIER from MACHINE M
join PATCHLINK_MACHINE_STATUS MS ON (M.ID = MS.MACHINE_ID)
JOIN PATCHLINK_PATCH_STATUS PS ON (MS.PATCHUID = PS.PATCHUID)
join KBSYS.PATCHLINK_PATCH KSYSPS ON (MS.PATCHUID = KSYSPS.UID)
WHERE
MS.status != 'PATCHED' AND
PS.IS_SUPERCEDED = '0' - aemalas 5 years ago