Custom Client Health Monitoring, Part 1

Posted on Updated on

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

Advertisements

2 thoughts on “Custom Client Health Monitoring, Part 1

    Custom Client Health Monitoring, Part 2 « t3chn1ck said:
    December 12, 2011 at 10:06 am

    […] 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 […]

    Custom Client Health Monitoring, Part 3 | t3chn1ck said:
    December 21, 2011 at 8:27 pm

    […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s