/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


List of PCs Missing Patches

02/05/2020 352 views

I came from an organization that used Shavlik for patching and in Shavlik it was easy to get a list of computers sorted by how many patches they are missing.  I am in a new job now and we use KACE SMA for patching.  I cannot figure out a way to get a list of PCs sorted by how many patches are missing.  Is there a way to do this?  Thanks in advanced for any help you can give.

1 Comment   [ + ] Show comment

Comments

  • Unfortunately none of the canned reports are what I need. I'm looking for a report that kind of looks like this:

    Computer Name Missing Patches
    Computer1 12
    Computer 7 8
    Computer 3 3

    This would give me an idea of which computers are having issues and would help me figure out where I need to start working on patching issues. Thanks again for the help.

All Answers

0

There are a number of ways this can be accomplished. There are some canned reports in the Reports module that may answer your question and custom reports are also possible. There were major changes to the patching module in version 10.x of the appliance, so custom reports may need to be updated if you have recently upgraded your SMA.

Answered 02/06/2020 by: chucksteel
Red Belt

0

You can find the list of canned reports in    Reporting -> Reports. Select "View By:"  Patching

Answered 02/06/2020 by: KevinG
4th Degree Black Belt

0

Here's what we've been using recently. This is in an interval of 1 month, so if you want it current you'll just need to take that out.

This report is for Recommended patches, so you'll need to adjust that at the bottom of the code for what you're wanting. I run 3 separate reports for Low, Recommended, and Critical.  If you want all severity in one report, just remove "  PP.SEVERITY != 'recommended'  "


Header is: Computer Name , Op System, Version, Last Username, IP, Agent Version, Last Inventory, Last Reboot, # installed patches, # Not installed, and percentage.  


SELECT

M.NAME AS Computername,

OS_NAME AS Operatingsystem,

CASE M.OS_BUILD

WHEN '7600' THEN 'Windows 7'

WHEN '7601' THEN 'Windows 7 SP1'

WHEN '9600' THEN '2012R2'

WHEN '10240' THEN '1507 (RTM)'

WHEN '10586' THEN '1511'

WHEN '14393' THEN '1607'

WHEN '15063' THEN '1703'

WHEN '16299' THEN '1709'

WHEN '17134' THEN '1803'

WHEN '17763' THEN '1809'

WHEN '18362' THEN '1903'

WHEN '18363' THEN '1909'

ELSE 'Unknown OS Build'

END

AS 'Version',

LAST_USER as Username,

M.IP as IPAdress,

CLIENT_VERSION as Agentversion,

M.LAST_SYNC as LastInventory,

M.LAST_REBOOT as LastReboot,

SUM(MS.DETECT_STATUS = 'PATCHED') AS Installed,

SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS Notinstalled,

FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =

'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS

Percent

FROM

PATCH_MACHINE_STATUS MS

JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)

JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)

JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)

WHERE (Last_Sync > CURDATE() - INTERVAL 1 MONTH) AND

PPS.STATUS = 0

and PP.SEVERITY != 'recommended' AND PPS.IS_SUPERCEDED = 0

GROUP BY MS.MACHINE_ID

ORDER BY Percent , M.NAME




This one is just for a certain label, which is Servers on this report. Only showing Machine name, Windows Version, Patched, Not Patched, and percentage. This is my Recommended patches for devices with label servers, also in 1 month interval.



SELECT 

M.NAME AS MACHINE_NAME,


OS_NAME AS WINDOWS_VERSION,


SUM(MS.DETECT_STATUS = 'PATCHED') AS PATCHED,

SUM(MS.DETECT_STATUS = 'NOTPATCHED') AS NOTPATCHED,

FLOOR(ROUND((SUM(MS.DETECT_STATUS = 'PATCHED') / (SUM(MS.DETECT_STATUS =

'PATCHED') + SUM(MS.DETECT_STATUS = 'NOTPATCHED'))) * 100,1)) AS

PERCENT_PATCHED


FROM 


PATCH_MACHINE_STATUS MS


JOIN KBSYS.PATCH PP ON (PP.ID = MS.PATCH_ID)

JOIN PATCH_STATUS PPS ON (PPS.PATCH_ID = PP.ID)

JOIN MACHINE M ON (M.ID = MS.MACHINE_ID)

JOIN KBSYS.SMMP_CONNECTION SC ON (M.KUID = SC.KUID)


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


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


WHERE (Last_Sync > CURDATE() - INTERVAL 1 MONTH) AND


PP.SEVERITY = 'Recommended'


AND PPS.STATUS = 0


AND PP.IS_SUPERCEDED = 0


AND L.NAME = 'Servers'


GROUP BY M.NAME


ORDER BY PERCENT_PATCHED, M.NAME




These reports work on v10

Hope this helps!

Answered 03/03/2020 by: creusser
White Belt

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