SCCM Patch Management Enterprise Compliancy Report —originally available in https://myitforum.com/cs2/blogs/cstauffer/archive/2008/10/17/sccm-patch-management-enterprise-compliancy-report.aspx
As most of you know if you have read any of the reports that I have posted in the past, here at the Commonwealth we use collections to track agencies. This report will show you an overall status and then a breakdown of each agency. This is done by grabbing the parent collection and listing each agency. ********************************************************** Note: You will need to change your ScopeID to match your location and the Collection ID in the last Select statement to your parent Collection. ********************************************************** –AuthListID=ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B
–CollID=SMS00001 declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID='ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B' declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0) from v_ClientCollectionMembers ccm where ccm.CollectionID='SMS00001' Select
CollectionName=vc.Name,
NumberInCollection=@CollCount,
NonClients=@CollCount-@NumClients,
PComputers=convert(numeric(5,2), (@CollCount-@NumClients)*100.00 / isnull(nullif(@CollCount, 0), 1))
from v_Collection vc
where vc.CollectionID='SMS00001' SELECT v_Collection.Name
, sn.StateName AS Status, COUNT(*) AS NumberOfComputers
, CONVERT(numeric(5, 2)
, ISNULL(COUNT(*), 0)* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers
, 'ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B' AS AuthListID FROM v_ClientCollectionMembers AS cm INNER JOIN
v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN
v_Collection ON cm.CollectionID = v_Collection.CollectionID LEFT OUTER JOIN
v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0)
WHERE (cm.CollectionID = 'SMS00001')
GROUP BY sn.StateName, v_Collection.Name
ORDER BY NumberOfComputers DESC SELECT v_Collection.Name, sn.StateName AS Status, COUNT(*) AS NumberOfComputers, CONVERT(numeric(5, 2), ISNULL(COUNT(*), 0)
* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers
FROM v_ClientCollectionMembers AS cm INNER JOIN
v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN
v_Collection ON cm.CollectionID = v_Collection.CollectionID INNER JOIN
v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0) AND cm.CollectionID IN
(SELECT subCollectionID
FROM v_CollectToSubCollect
WHERE (parentCollectionID = 'PA100043'))
GROUP BY sn.StateName, v_Collection.Name
ORDER BY v_Collection.Name DESC
——————-
Thanks,
https://paddymaddy.blogspot.com/
