Custom Client Health Monitoring, Part 2

Posted on Updated on

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

Advertisements

2 thoughts on “Custom Client Health Monitoring, Part 2

    Custom Client Health Monitoring, Part 3 « t3chn1ck said:
    December 19, 2011 at 8:01 am

    […] of the first report, will then show exactly which collections that computer is a member of, called “Collection memberships for a specific computer“.  Drilling down into that report then shows a report that will show all computer records that are […]

    Custom Client Health Monitoring, Part 1 « t3chn1ck said:
    January 12, 2012 at 8:23 am

    […] HomeAbout Nick « Fix for SMS Trace missing columns Custom Client Health Monitoring, Part 2 […]

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