/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Patching reports

07/22/2020 82 views

I have a report that pulls the patching percentages and I have been asked if there is a way to have the reports pull for a 3 month average instead of where it pulls it now 

3 Comments   [ + ] Show comments

Comments

  • Please post the SQL for your current report.
  • 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
  • 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?

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