Custom Client Health Monitoring, Part 2

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

About these ads