ConfigMgr 2007

14 November 2008

SCCM Patch Management Enterprise Compliancy Report

SCCM Patch Management Enterprise Compliancy Report ---originally available in http://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'

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,
http://paddymaddy.blogspot.com/

SCCM Patch Management Enterprise Compliancy Report

SCCM Patch Management Enterprise Compliancy Report ---originally available in http://myitforum.com/cs2/blogs/cstauffer/archive/2008/10/17/sccm-patch-management-enterprise-compliancy-report.aspx

**********************************************************

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'

-------------------
Thanks,
http://paddymaddy.blogspot.com/

How to Configure the Client Policy Polling Interval for a Specific Collection

for clients to have a more frequent polling interval. To achieve this, configure a collection-specific policy polling interval, which will then apply to members of that collection.

To configure a collection-specific policy polling interval

In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Collections.
In the results pane, right-click the collection you want to modify, and then click Modify Collection Settings.
In the Advanced tab of the Collection Properties dialog box, select Enable collection specific policy polling interval, and then specify the required policy polling interval in minutes.
Click OK to close the Collection Properties dialog box.

-------------------
Thanks,
http://paddymaddy.blogspot.com/

How to Configure the Client Policy Polling Interval for a Specific Collection

for clients to have a more frequent polling interval. To achieve this, configure a collection-specific policy polling interval, which will then apply to members of that collection.

To configure a collection-specific policy polling interval

In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Collections.
In the results pane, right-click the collection you want to modify, and then click Modify Collection Settings.
In the Advanced tab of the Collection Properties dialog box, select Enable collection specific policy polling interval, and then specify the required policy polling interval in minutes.
Click OK to close the Collection Properties dialog box.