Show Software Update Compliance State for each System in a Collection
February 24, 2012 Leave a comment
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:
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