Show Software Update Compliance State for each System in a Collection

We had an internal request to “Show me software update compliance state for each system in a collection, based on an Update List”. There are a couple canned web reports available that got me close, but not exactly what I need.  So here it is in case anyone else is interested.

Here’s a shot of the report:

Here’s the SQL:

declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID=@AuthListID 

SELECT rs.Name0 AS MachineName, rs.User_Name0 AS LastLoggedOnUser, asite.SMS_Assigned_Sites0 AS AssignedSite, rs.Client_Version0 AS ClientVersion, 
(CASE cs.Status WHEN 0 THEN 'Compiance State Unknown' WHEN 1 THEN 'Compliant' WHEN 2 THEN 'Non-Compliant' WHEN 3 THEN 'Conflict Detected' ELSE 'Null' END) 
AS State, cs.StatusTime 
FROM v_ClientCollectionMembers AS ccm INNER JOIN 
v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = ccm.ResourceID INNER JOIN 
v_R_System AS rs ON rs.ResourceID = ccm.ResourceID LEFT OUTER JOIN 
v_RA_System_SMSAssignedSites AS asite ON asite.ResourceID = ccm.ResourceID 
WHERE (ccm.CollectionID = @CollID) 
order by rs.Name0


Download the report


About Greg Ramsey
Greg Ramsey is a systems engineer specializing in global systems management for Dell IT. He has a B.S. in Computer Sciences and Engineering from the Ohio State University and is a Microsoft Most Valuable Professional (MVP) for Microsoft System Center Configuration Manager. Greg coauthored SMS 2003 Recipes: A Problem-Solution Approach (Apress, 2006), Microsoft System Center Configuration Manager Unleashed (Sams, 2009), Microsoft System Center 2012 Configuration Manager Unleashed (Sams, 2012), and Microsoft System Center 2012 Configuration Manager: Administration Cookbook (Packt Publishing, 2012), and Microsoft System Center 2012 Configuration Manager R2 Unleashed (Sams, 2012). Greg is cofounder of the Ohio SMS Users Group, and the Central Texas Systems Management User Group.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: