Tuesday, May 1, 2012

SCCM 2007 Reboot Report

We unfortunately have some devices inside our hospital that cannot be rebooted after applying updates.  These are not critical patient care devices but they are used sporadically 24/7 and therefore we cannot schedule a clean reboot.  For example, we have a computer that is being used during a sleep study that is not directly used all night long (cannot click postpone reboot) but that nevertheless needs to be managed. Our current solution is to apply updates to a specific group of computers and then have our application team manage rebooting those machines at their convenience.

We needed a report that could be used to show when the last time a computer was rebooted based off of a collection.  This is what I was able to come up with:


SELECT DISTINCT
   sys.netbios_name0 AS [Computer Name],
   [Top Console User] = CASE
when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')
then 'Unknown'
Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0
End,
   CONVERT(VARCHAR(10),os.LastBootUpTime0,101) AS [Bootup Time],
   Datediff(dd, os.LastBootUpTime0, GetDate()) AS [Days Since Last Reboot],
   CONVERT(VARCHAR(10),wss.LastHWScan,101) AS [Last Inventory]
FROM
   dbo.v_R_System_Valid AS sys
   LEFT JOIN dbo.v_GS_Operating_system AS os
      ON sys.resourceID = os.resourceID
   LEFT JOIN dbo.v_GS_Workstation_Status AS wss
      ON sys.resourceID = wss.resourceID
   left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID = sys.ResourceID)
   inner join v_FullCollectionMembership on (v_FullCollectionMembership.ResourceID = sys.ResourceID)
WHERE
v_FullCollectionMembership.CollectionID = @CollectionID
ORDER BY
   [Days Since Last Reboot] DESC