/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Machines missing patches report

06/08/2018 906 views
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'

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ