/build/static/layout/Breadcrumb_cap_w.png

Patch Compliancy report

Hi


Is there a standard patch compliancy report that can show the percentage that a device has patched.  I had a report working before that was getting number of patches installed compared with the total number of patches required for the computer based on checking on the patch label, and this worked well until the Kace appliance was upgraded to Kace 12.  It seems the tables have changed somehow and the report is not working anymore.  I want to understand where the tables changed to and if there is any report I can compare with to know what changed.


My report was using this code section to create the report:

select CASE WHEN machine_name is null then 'Total' Else machine_name end as COMPUTER_NAME,PATCHED_COUNT,NOTPATCHED_COUNT, ROUND(((PATCHED_COUNT/total_p)*100),2) as COMPLIANCY

  from

  (

    SELECT   M.NAME as machine_name,count(S.Product_Installed) as total_p,

      ( SUM(if(S.Product_Installed='1',1,0)) ) AS PATCHED_COUNT,

      ( SUM(if(S.Product_Installed='0',1,0)) ) AS NOTPATCHED_COUNT

FROM   ORG1.MACHINE M,

      ORG1.PATCH_MACHINE_STATUS S ,

       KBSYS.PATCH P

LEFT JOIN ORG1.PATCH_LABEL_JT PL ON  P.ID=PL.PATCH_ID

LEFT JOIN ORG1.LABEL LL ON  PL.LABEL_ID=LL.ID

WHERE  M.ID = S.MACHINE_ID

       AND S.PATCH_ID = P.ID

       AND S.Product_Installed IN ( '1' ,'0')

       AND LL.NAME= 'Production Approved NEW Patches'

        GROUP BY M.NAME

        ) T

) AS COMPLIANCE ON  COMPLIANCE.COMPUTER_NAME=MACHINE.NAME





0 Comments   [ + ] Show comments

Answers (3)

Posted by: KevinG 2 years ago
Red Belt
0

No database table changes in 12.0 in regards to patching.

https://support.quest.com/kace-systems-management-appliance/kb/336101/kace-sma-12-0-database-schema-changes

What is the error/issue when using the SQL statement?

Posted by: tuyen 2 years ago
Purple Belt
0
There is no error, but the values for my queries are showing up as NULL because it is not able to read the correct values from the report now I think.
Posted by: tuyen 2 years ago
Purple Belt
0

Actually, I removed the search against the Patch label name and it is reporting the patch count correctly now, but I am not sure exactly what patch list it is comparing against... maybe it is smart enough to compare against all the patches that are supposed to be deployed to it, which is what I want.  I think I am good with this now.


Comments:
  • Mind sharing your query? Is it scoped down to a group or label, or does it show all devices? - JS_DC 2 years ago

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