I need help creating a specific report.  This report:

http://www.itninja.com/question/k1000-report-of-all-patches-missing-by-machine

is almost what I need except that I need the report to display like this:

 

"Machine name", # of patches missing this machine

list of patches missing

"machine name 2", # of patches missing on this machine

list of patches missing

etc

 

This is code code from the above link, I think it just needs to be modified slightly:

**SQL Statement**

Select CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME  WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN CONCAT(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME,

P.TITLE AS DISPLAY_NAME, P.IDENTIFIER as KB_ARTICLE from PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P

where 

MACHINE.ID = S.MACHINE_ID and

S.PATCHUID = P.UID and

S.STATUS != 'PATCHED'

order by MACHINE_NAME, P.TITLE

 

Thanks for any help you can provide!

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Bueller?
  • How about by path label - that way we know we are current within three months. I'm finding it's reporting on superseded patches which then is not accurate.
    • I'm terrible at this SQL thing which is why I'm asking.
Please log in to comment

Community Chosen Answer

1

Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME

FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

where MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */ 

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE

 

That should give you exactly what you are looking for. Here are a couple variations of the query that you may find useful.

With labels included:

 

Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME

FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

where MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */ 

and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%') 

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE


Filtering by patch impact:

 

Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME

FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */ 

and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%')

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE

 

Finally, adding in a period of time (only report against machines that have checked in within the last...)

 

Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, GROUP_CONCAT(PP.TITLE) AS PATCH_NAME

FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */ 

and L.NAME LIKE ('Windows Workstations (All)') and (M.NAME not like '%WINDOWS-%') and 

((TIMESTAMP(LAST_SYNC) <= NOW() AND TIMESTAMP(LAST_SYNC) > DATE_SUB(NOW(),INTERVAL 14 DAY)))

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE

Answered 01/07/2014 by: jparkins
Blue Belt

  • Jparkins. I know this is from over a year ago but thank you for these reports. I'm curious how would I edit this report to show the same columns except:

    1. Remove the 'patch name' column entirely
    2. I think these reports are accounting for all critical patches even if they don't apply to each computer. I have many fully patched systems (or close to it) and this report says they're missing dozens or hundreds. I Group my patches in patch catalog labels. Do you know the best way to add my patch labels to a query?

    Also do you have a recommendation of how I could learn what each of the items actually refer to? Is there a legend of some sort that explains it? How did you come to learn what each item means in general?

    Thanks,
    Ben
    • It looks to me like this report lists all the patches which are not installed on a given machine. This is not at all the same as patches which are missing. For example, the first PC listed on my report shows dozens of Office 2007 patches. However, the PC in question has Office 2010 installed, not Office 2007.
Please log in to comment

Answers

0
Hey guys!!! Thank you to jparkins!! This is almost exactly what I was looking for as well!

So I modified the code to do line breaks after the patches to be make the report more readable  like this:

============================================================================
Select M.NAME as MACHINE_NAME, M.USER, M.USER_FULLNAME, SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED, group_concat(distinct concat(PP.TITLE,' ---- (',IDENTIFIER,') ]') order by PP.TITLE separator '\n') AS PATCH_NAME

FROM ORG1.PATCHLINK_MACHINE_STATUS MS JOIN ORG1.MACHINE M ON M.ID = MS.MACHINE_ID 

JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID 

JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID 

JOIN ORG1.MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID) 

JOIN LABEL L ON (ML.LABEL_ID = L.ID) 

where PP.IMPACTID = ('Critical') AND MS.STATUS='NOTPATCHED' AND PPS.STATUS = 0 /* 0=active patches */ 

and L.NAME LIKE ('%Low Risk%')

group by MACHINE_NAME

order by MACHINE_NAME, PP.TITLE


============================================================================

I was hoping someone can help me modify the code so that it will filter patches based on a Smart Label for patches we have call Windows - Critical.

Any help would be greatly appreciated!!! Thanks guys!

Answered 09/18/2015 by: egas
Yellow Belt

  • No takers??
Please log in to comment
Answer this question or Comment on this question for clarity