KACE Product Support

Please post the SQL for your current report. - chucksteel 3 years ago

SELECT M.NAME AS MACHINE_NAME,

USER_NAME,

OS_NAME AS OS_Name,

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

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

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

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)

JOIN LABEL L ON (ML.LABEL_ID = L.ID)

/*WHeRE PP.SEVERITY = 'Critical' */

AND PPS.STATUS = 0

AND PP.IS_SUPERCEDED = 0

/*AND PP.PUBLISHER = 'Microsoft Corporation'

AND L.NAME rlike 'Insert your group labels her'*/
WHERE
(L.NAME rlike 'nsii clients'
or M.IP LIKE '10.131%'
OR M.IP LIKE '10.134%'
OR M.IP LIKE '172.30%')
and M.OS_NAME NOT LIKE '%Server%'
AND M.USER_FULLNAME NOT LIKE '%Line%'
AND M.USER_FULLNAME NOT LIKE '%Production%'
AND M.USER_FULLNAME NOT LIKE '%Visitor%'
AND M.USER_FULLNAME NOT LIKE '%Assy%'
AND M.USER_FULLNAME NOT LIKE '%QA%'
AND M.NAME NOT LIKE 'G6YE416007L8'
AND M.NAME NOT LIKE 'GERY62600037'
AND M.NAME NOT LIKE 'GERY626000P3'
AND M.NAME NOT LIKE 'CRXH842'
AND M.NAME NOT Like 'NUCDISPLAYPC'
AND M.NAME NOT LIKE 'NSII-FUJI003-W'
AND M.Name not like 'NUC64200DDZ'
and M.name not like 'NUCG6SY642000DNY'
and M.name not like 'NUCG6SY642007LX'
AND M.NAME NOT LIKE '78LN3X1'
AND M.NAME NOT LIKE 'G087L02'
AND M.USER_FULLNAME NOT LIKE 'qc user'

AND M.IP NOT LIKE '10.131.20%'
AND M.IP NOT LIKE '172.30.20%'
AND M.IP NOT LIKE '10.134.20%'
AND PP.IS_SUPERCEDED = 0
AND PP.TITLE NOT LIKE '%Preview%'
AND PP.TITLE NOT LIKE '%Security Only%'
AND PP.TITLE NOT LIKE '%Visual Studio%'
AND PP.TITLE NOT LIKE '%SQL Server%'
AND PP.TITLE NOT LIKE '%Adobe Flash Player%'

GROUP BY M.NAME

ORDER BY PERCENT_PATCHED, M.NAME - NSII 3 years ago

You have no date criteria in your report and if I understand correctly, you maybe want to see what the average figure is over the last three months? To get that figure surely you have to store the data for each machine, so maybe the report you are asking for is not possible? - Hobbsy 3 years ago