/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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.
Any help is much appreciated. 
2 Comments   [ + ] Show comments

Comments

  • well, it appears the query above wasnt just doubling all values, it was doubling some, halfing other an accurate with the middle ones. Not sure what was going on with it.
    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.
  • found it, the patch UID table is in a different database. Here is the query with KB's.
    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'
Please log in to comment

There are no answers at this time


Answers

Share