/build/static/layout/Breadcrumb_cap_w.png

Supporting Windows Question


Critical patches missing by machine

10/23/2017 1346 views
I'm sure this has been asked before, and I apologize if it has but I did not find it while searching the site...

I have a report in my K1000 that I found on here:

SELECT M.NAME AS MACHINE_NAME,
USER_NAME,
OS_NAME AS OS_Name,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOT_PATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
+SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON (PP.UID = MS.PATCHUID)
JOIN PATCHLINK_PATCH_STATUS PPS ON (PPS.PATCHUID = PP.UID)
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)
WHERE PP.IMPACTID = 'Critical'
AND PPS.STATUS = 0
AND PP.IS_SUPERCEDED = 0
GROUP BY M.NAME
ORDER BY PERCENT_PATCHED, M.NAME

It's a wonderful report that tells me how many critical patches are missing per machine so that I can basically have a point to start at.  It's been a great tool that gave me a starting point as I tried to bring the network into compliance.  Now, however, I want to have a companion report to it...and to say that I suck at SQL would be a massive understatement (it's an upcoming class for me, so I hope to learn about it soon, but I just don't know it yet).

Now the important question...currently, this report gives me a machine name, user name, OS, Patched count, not patched count, and percentage patched.  I would like a report that gives me, broken down by machine, what the actual packages/patches are that are considered "Not Patched".  We have a server, for example, that is still missing 4 or 5 despite having been told to patch both application and OS patching....I want to know what those 5 are, but I would like to have it for all of the patches missing per machine as well if possible.


Thanks for any assistance that you can provide, and thanks for bearing with my rambling!

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
Hello,

This article may have the information you need to make the report you're looking for.  http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels

The other option is to see if you can get the report you're wanting to create by doing the reporting wizard.  There may also be a canned report called "Devices not compliant by patch".

Another option is to see if anyone else has any other ideas on the forums.

The last option would be to contact our pro services group to see if they can help you with it.  Pro services is fee based though.


Answered 10/30/2017 by: KACE_Irwin
Second Degree Brown Belt

  • That'll do it, thanks! The one contained in the previous post is exactly what I was looking for (and it seems to also be one of the originators for the original report I run).

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