/build/static/layout/Breadcrumb_cap_w.png

K1000 Report of All Patches Missing by Machine, displayed in a specific way...

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!


2 Comments   [ + ] Show comments
  • Bueller? - nick2585 10 years ago
  • 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. - anaccarato 8 years ago
    • I'm terrible at this SQL thing which is why I'm asking. - anaccarato 8 years ago

Answers (2)

Answer Summary:
Posted by: jparkins 10 years ago
Second Degree Blue Belt
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


Comments:
  • 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 - bens401 8 years ago
    • 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. - MichaelMc 8 years ago
Posted by: egas 8 years ago
Orange Belt
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!


Comments:
  • No takers?? - egas 8 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ