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