Patch report needed to run against Machine Labels
Trying to find a report to run against a Machine Label to show how many patches the various servers have installed. I have one that when it's kicked off, it runs against one of my Patch labels, not Machine Label. Even using the "Label ID", the report comes back with nothing.... I'm not a SQL witer so I'm kind of lost...
Example:
Select DISTINCT MACHINE.NAME as 'Machine Name', P.TITLE AS 'Patch Name', P.IDENTIFIER as 'KB Article', P.IMPACTID as 'Impact', if(P.IS_APP='0' ,'OS' ,'Application') as 'Patch Type', S.STATUS_DT as 'Detect Date', S.DEPLOY_STATUS_DT as 'Deploy Date', S.DEPLOY_STATUS as 'Deployed', PLJT.LABEL_ID as 'label' from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P join PATCHLINK_PATCH_LABEL_JT PLJT on P.UID=PLJT.PATCHUID where MACHINE.ID = S.MACHINE_ID and S.PATCHUID = P.UID and PLJT.LABEL_ID = '46' order by 'label' asc, 'Patch Name' asc, 'Detect Date' desc
Answers (2)
Try this, but replace the '46' (second to last line) with your machine label ID:
Select DISTINCT
MACHINE.NAME as 'Machine Name',
P.TITLE AS 'Patch Name',
P.IDENTIFIER as 'KB Article',
P.IMPACTID as 'Impact',
if(P.IS_APP = '0', 'OS', 'Application') as 'Patch Type',
S.STATUS_DT as 'Detect Date',
S.DEPLOY_STATUS_DT as 'Deploy Date',
S.DEPLOY_STATUS as 'Deployed'
from MACHINE
join PATCHLINK_MACHINE_STATUS S on S.MACHINE_ID = MACHINE.ID
join KBSYS.PATCHLINK_PATCH P ON P.UID = S.PATCHUID
join PATCHLINK_PATCH_LABEL_JT PLJT ON P.UID = PLJT.PATCHUID
join MACHINE_LABEL_JT MLJT ON MLJT.MACHINE_ID = MACHINE.ID
where MACHINE.ID = S.MACHINE_ID and S.PATCHUID = P.UID and MLJT.LABEL_ID = '46'
order by MACHINE.NAME desc, P.TITLE desc,S.STATUS_DT desc
Comments:
-
I just tried this and it is coming up blank for some reason even though I know my computers aren't patched. - BFonts 12 years ago
-
This report works for me. Are you sure the machine label is assigned to the servers you have in inventory? Maybe trying a different machine ID will help? - brian_klocek 12 years ago
how do I find my Machine Labels ?
Is it similar approach to computer Inventory labels ? going into adminui and looking at the URL ID=?***##
Comments:
-
Yes - brian_klocek 12 years ago
