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

Greg

ramseyg@hotmail.com

About Greg Ramsey
Greg Ramsey, Enterprise Mobility MVP, is a Senior Enterprise Architect at Dell Technologies. He has a B.S. in Computer Sciences and Engineering from Ohio State University and has co-authored many books over the years. Greg is an international speaker, a board member of the Northwest System Center User Group, and the Director of Communications for the Midwest Management Summit.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: