/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Patch Report with all Inventoried Devices Listed

01/14/2020 51 views

I have the patch report SQL queries that are easily found here but after I run them I notice I am seeing only 188 of 287 devices on the report listed. Why am I missing more than a few devices on my patch report? Here's the SQL query I am using: 


SELECT 

    M.NAME AS MACHINE_NAME,

    OS_NAME AS WINDOWS_VERSION,

    SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,

    SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,

    FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS = 'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) 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)

WHERE

    PPS.STATUS = 0 AND PP.IS_SUPERCEDED = 0

        AND PPS.IS_SUPERCEDED = 0

        AND PP.TITLE NOT RLIKE 'Silverlight|Java|Skype|Chrome|Flash|Adobe|7-Zip|Server Management Studio'

GROUP BY MS.MACHINE_ID

ORDER BY PERCENT_PATCHED , M.NAME


If I used the Asset Name Report I see all devices on the report. Here's what that query looks like. 


SELECT MACHINE.NAME AS SYSTEM_NAME  FROM MACHINE    ORDER BY SYSTEM_NAME


Appreciate any help you can provide. Just want to make sure I am accounting for all of my devices and giving an accurate report to my supervisors. 

0 Comments   [ + ] Show comments

Comments


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