Patch Compliancy report
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
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 ,
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
) AS COMPLIANCE ON COMPLIANCE.COMPUTER_NAME=MACHINE.NAME
No database table changes in 12.0 in regards to patching.
What is the error/issue when using the SQL statement?
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.