I put this presentation together a few years ago and a topic of rebooting came up the other day so I thought I would post this presentation to better explain what happens when management decides “WE CANT REBOOT MACHINES”. I hope this helps others explain to management what happens when we patch and don’t reboot as needed.
Thanks,
Chris Stauffer <><
I’m finally getting back into ConfigMgr 2007 and Patch Management again so I wanted to update some of my existing reports and try to come up with some better ways to control my patches. Here is the fist updated report. Thanks Matt Broadstock for assisting with the code where I got stuck 
Each report will have to be hard coded so I created several of them but basically you enter the ScopeID and the Collection for the machines you want a status on.
In this example I want to see all XP workstations and I want to compare it against the Patch list I created for XP Security patches.
This report is broken into 4 parts:
Part 1 Show the title of the Scope ID
Part 2 Show the Collection ID for an OS and give me the headcounts
Part 3 Show the total Compliancy state for the machines in part 2
Part 4 Show me the total compliancy state for each machines in each of the sub collections I want to know more about
Presently I have to report on multiple office groups and each office group has its own IT staff, so I needed a report that would show management what things look like as a whole but at the same time tell us were issues are so we can address them separately. So I created a master collection and sub collections for each office group that contain only the machines from that office groups OU in AD.
This could probably be automated further or even be setup with variables but it has been 18+ months since i worked on SQL like this so i need to get my head wet again :-)
Not as fancy as the new SRS but my Boss is just as happy with the ASP reports still :-P
##################################################################
--The first two lines are just for quick reference so I know what Scopeid and collection I am using in the report
-- AuthListID=ScopeId_07303A0F-140E-4EB7-9D23-A333E0D085FC/AuthList_1BAE5B91-C218-4817-8CEC-13019EA83518
--CollID=SMS000ES
declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems
where CIType_ID=9 and CI_UniqueID='ScopeId_07303A0F-140E-4EB7-9D23-A333E0D085FC/AuthList_1BAE5B91-C218-4817-8CEC-13019EA83518'
declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0)
from v_ClientCollectionMembers ccm
where ccm.CollectionID='SMS000ES'
-- Part 1
select distinct Title as Title
--, CI_UniqueID as AuthListID
from v_AuthListInfo
where CI_UniqueID ='ScopeId_07303A0F-140E-4EB7-9D23-A333E0D085FC/AuthList_1BAE5B91-C218-4817-8CEC-13019EA83518'
-- Part 2
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='SMS000ES'
-- Part 3
SELECT v_Collection.Name
, sn.StateName AS Status, COUNT(*) AS "Number Of Computers"
, CONVERT(numeric(5, 2)
, ISNULL(COUNT(*), 0)* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS "Percentage of Computers"
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 = 'SMS000ES')
GROUP BY sn.StateName, v_Collection.Name
ORDER BY "Number Of Computers" DESC
-- Part 4
SELECT v_Collection.Name
, sn.StateName AS Status
, COUNT(*) AS "Number Of Computers"
, CONVERT(numeric(5, 2)
, ISNULL(COUNT(*), 0)* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS "Percentage of Computers"
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 = 00100030') )
WHERE cm.ResourceID in
(select ResourceID from v_ClientCollectionMembers where CollectionID = 'SMS000ES')
GROUP BY sn.StateName, v_Collection.Name
ORDER BY v_Collection.Name Asc, Status Desc
########################################################################
More to come as I get more into reports again.