This is part 3 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. Part 2, a drill down 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 in the collection. This report can be particularly helpful when trying to identify a set of computers (such as all at a particular location) that are being impacted.
select Count(FCM.Name) as 'Total number of computers in collection', COL.Name, COL.Comment, COL.CollectionID from v_FullCollectionMembership FCM, v_Collection COL where ((COL.CollectionID='CollectionID1' and FCM.CollectionID='CollectionID1') or (COL.CollectionID='CollectionID2' and FCM.CollectionID='CollectionID2') or (COL.CollectionID='CollectionID3' and FCM.CollectionID='CollectionID3') or (COL.CollectionID='CollectionIDetc' and FCM.CollectionID='CollectionIDetc') ) group by COL.Name, COL.CollectionID, COL.Comment order by 'Total number of computers in collection' DESC
Now, link this report for further drill down into the default SCCM report for computer information by
- Edit the report and select the “Links” tab
- Change the “Link type” to “Link to another report”
- Select report “Members of a selected collection”
- Create a new Target Report Prompt where Name=variable, Prompt text=Collection ID, and Column=4
- Click OK and enjoy drill down ;-)
This now completes a trio of custom client health monitoring/reporting collections!