Custom Client Health Monitoring, Part 2

Posted on December 12, 2011 Updated on February 25, 2013

This is part 2 of 3 on my custom client health monitoring reports, which I created way back in SMS 2003 and I still use today.  Part 1 outlines a custom report that I call “Computers that belong to X number of monitored collections“, which shows how many collections in which a computer resides.  Drilling down into that report will then show exactly which collections that computer is a member of.  This report I call “Collection memberships for a specific computer“.  The code for this report is as follows:

select distinct SYS.Name0 as 'Computer',
  SYS.User_Name0 as 'User Name',
  col.Name, col.CollectionID
from v_R_System SYS
LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
LEFT join v_Collection col on col.CollectionID=fcm.CollectionID
where SYS.Name0=@var1 AND (
  FCM.CollectionID='CollectionID1' or
  FCM.CollectionID='CollectionID2' or
  FCM.CollectionID='CollectionID3' or
  FCM.CollectionID='CollectionIDetc' )
GROUP BY SYS.Name0, SYS.User_Name0, col.name, col.collectionID
ORDER BY SYS.Name0 

Now, link this report for further drill down into the default SCCM report for computer information by

1. Edit the report and select the “Links” tab
2. Change the “Link type” to “Link to another report”
3. Select report “Hardware – General – Computer information for a specific computer”
4. Create a new Target Report Prompt where Name=variable, Prompt text=Computer Name, and Column=1
5. Click OK and enjoy drill down ;-)

Next up: Part 3 of 3 – Monitored Collections Summary

Custom Client Health Monitoring, Part 1

Posted on December 9, 2011 Updated on February 25, 2013

Something that I created way back with SMS 2003 is created a set of reports as my own homegrown monitoring for client health (note: I still use these reports today with SCCM 2007) .  This is part 1 of 3.

BACKGROUND: In a typical environment, a computer can reside in many collections with many different advertisements assigned to it.  Often these collections are designed with queries that will return a subselect set of systems that “do not have something installed”.  For example, the query may be designed to list a set of systems that do not have Adobe Reader, so as the software installs and the PCs submit the updated inventory, those computers will then get removed from the collection upon the next scheduled update cycle.

The following report, Computers that belong to X number of monitored collections, will list PCs in the most collections.  However, not all collections are subselect collections that contain computers missing software.  For example, I have recurring scripts assigned to various PCs to perform various tasks that I do not want as part of the results.  This code only reports on the collections that I care about.  To use this, simply change the “CollectionID” with the collection that you want to report upon.

select distinct
   SYS.Name0 as 'Computer',
   SYS.User_Name0 as 'User Name',
   Count (*) as 'Number of missing installs',
   HWSCAN.LastHWScan,
   SWSCAN.LastScanDate
 from v_R_System SYS
 LEFT join v_FullCollectionMembership fcm on fcm.ResourceID=sys.ResourceID
 LEFT JOIN v_GS_LastSoftwareScan SWSCAN on SYS.ResourceID = SWSCAN.ResourceID
 LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on SYS.ResourceID = HWSCAN.ResourceID
 LEFT JOIN v_UpdateScanStatus UPSCAN on SYS.ResourceID=UPSCAN.ResourceID
 where SYS.Name0=FCM.Name AND (
   FCM.CollectionID='CollectionID1' or
   FCM.CollectionID='CollectionID2' or
   FCM.CollectionID='CollectionID3' or  
   FCM.CollectionID='CollectionIDetc' )
 GROUP BY SYS.Name0,
   SYS.User_Name0,
   HWSCAN.LastHWScan,
   SWSCAN.LastScanDate
 ORDER BY 'Number of missing installs' desc, SYS.Name0

Results can look like this:

Next up: drilling down into this report to reveal Collection memberships for a specific computer