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

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

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

Answered 04/24/2012 by: jaredv
Fourth Degree Green Belt

  • I just tried this and it is coming up blank for some reason even though I know my computers aren't patched.
  • 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?
Please log in to comment

Answers

0

how do I find my Machine Labels ?

Is it similar approach to computer Inventory labels ? going into adminui and looking at the URL ID=?***##

Answered 08/14/2012 by: JAaronAnderson
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity