Configmgr SQL query to get the list of clients that require a specific software update patch

This is quick blog post about getting the list of clients that require a specific software update contained (it can be based on title,article ID(KB),bulletin ID).

You can get the client list using the default software update compliance reports but it doesn't give you the inventory information about client ,like ip address,hardware scan,software update scan ,OS etc and creating custom SQL allow you to filter lot more like collection ID,hostname contains,OS not like etc.

Adobe has released a security update (APSB17-32) for Adobe Flash Player for Windows, Macintosh, Linux and Chrome OS. This update addresses a critical type confusion vulnerability that could lead to code execution.

Security team has requested to get this updated on all machines that are required by this update ,so in order to proceed further, you need to identify the list of clients prior and notify to application team(mainly for servers) and follow the change request etc.

Below is the SQL query to get list of clients that are required by bulletin ID: APSB17-32 .I have also added couple of filter options like excluding server OS 2003 and include only server OS.

I have also added title,article ID,collection ID,hostname which are commented in the query .If you want filter the results using these ,you can simply uncomment them (removing the dashes - - )

You can use this SQL query to create SSRS to run the report directly from the URL , without running it from SQL server management studio.

select vrs.Name0 [Host],os.Caption0 [OS],
IP.IPAddress AS [IP Address],
CONVERT(VARCHAR(26), ws.lasthwscan, 100) as [LastHWScan],
CONVERT(VARCHAR(26), uss.lastscantime, 100) AS 'LastSUScanTime',
CONVERT(nvarchar(26), OS.LastBootUpTime0, 100) AS [Last Reboot],
ui.InfoURL as InformationURL,ui.Title,ui.dateposted [Date Posted]
from V_UpdateComplianceStatus  css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
inner join v_FullCollectionMembership fcm on fcm.ResourceID=css.ResourceID
JOIN dbo.v_R_System AS vrs ON vrs.ResourceID = css.ResourceID
join v_GS_OPERATING_SYSTEM OS on os.ResourceID=css.ResourceID
join v_GS_WORKSTATION_STATUS WS on ws.ResourceID=css.ResourceID
join v_UpdateScanStatus USS on uss.ResourceID=css.ResourceID
JOIN (SELECT     IP1.resourceid AS rsid2, IPAddress = substring
((SELECT     (IP_Addresses0 + ', ')
FROM    v_RA_System_IPAddresses IP2
WHERE     IP2.IP_Addresses0 NOT LIKE '169%' AND IP2.IP_Addresses0 NOT LIKE '0.%' AND IP2.IP_Addresses0 NOT LIKE '%::%' AND
IP_Addresses0 NOT LIKE '192.%' AND IP1.resourceid = IP2.resourceid
ORDER BY resourceid FOR xml path('')), 1, 50000)
FROM    v_RA_System_IPAddresses IP1
GROUP BY resourceid) IP ON IP.rsid2 = fcm.resourceid
WHERE css.Status=2 --for required
and ui.BulletinID='APSB17-32'
--AND ui.Title='2017-08 Security Monthly Quality Rollup for Windows Server 2008 R2 for x64-based Systems (KB4034664)'
--and ui.ArticleID='4034664'
and vrs.operatingSystem0 not like '%2003%'
--and fcm.CollectionID='PS100118'
--and (fcm.name like '%WP%')
and vrs.operatingSystem0 like '%server%'
group by vrs.Name0,vrs.operatingSystem0 ,ui.InfoURL,ui.Title,ui.dateposted,
os.caption0,
ws.lasthwscan,
uss.lastscantime,
IP.IPAddress,
OS.LastBootUpTime0
ORDER BY 1

Hope  it helps!