Need help with a report on 3rd party patching
I am 2 months into implementing K1000 (11.0) for my company. We have setup MS Windows patching and I fought through getting a SQL report working and then for the Microsoft Patches for all 4 environments. I basically just swapped out the L.NAME = Test1 for L.NAME = Test2 and so forth.
I now need to get a report for 3rd party patches. We are testing on 3-4 pretty standard Apps: Chrome, Adobe Reader, etc. I tried to copy the working MS patch report and replace the L.NAME with the 3rd party patching schedule and this does not appear to work. Here is the working reports SQL:
select M.NAME as 'MACHINE_NAME',USER_NAME as 'User' ,OS_NAME as 'OS_Name' ,sum(MS.PATCH_INSTALLED) as 'PATCHED'
,sum(!MS.PATCH_INSTALLED) as 'NOT_PATCHED'
,round((sum(MS.PATCH_INSTALLED) / (sum(MS.PATCH_INSTALLED) + sum(!MS.PATCH_INSTALLED))) * 100, 0) as 'PERCENT_PATCHED'
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)
(PP.SEVERITY = 'Critical' OR PP.SEVERITY = 'Important')
and PPS.STATUS = 0
and PP.IS_SUPERCEDED = 0
and L.NAME = 'SRV UD Servers'
Bottom line is I want to be able to get a patching report for machines in a label, reporting just on the patches in a specific patching schedule. I know little about SQL queries as this is my first real attempt at it. TIA for any help.