Finding membership of multiple SCOM 2007 Collections and subselects

So, we have collections for sites, and collections for advertisements. Someone asked me to find the following:
  • all computers at a particular site (CollectionID ANN00086)
  • which had particular software packages

SCOM 2007 reporting seemed to be the way to go, so after a few iterations I came up with this friendly behemoth.

SELECT v_FullCollectionMembership.Name as SysName, v_Collection.Name as Package_Name
FROM v_FullCollectionMembership
LEFT JOIN v_Collection
ON v_Collection.CollectionID=v_FullCollectionMemberShip.CollectionID
WHERE v_FullCollectionMembership.name in
( SELECT name FROM v_FullCollectionMembership
WHERE v_FullCollectionMembership.CollectionID='ANN00086' )
AND v_FullCollectionMembership.CollectionID in
( SELECT CollectionID from v_Collection
WHERE (name like 'Training%' or name like 'Support%') and name not like '%FIX%' )
ORDER BY v_FullCollectionMembership.Name, v_Collection.Name

Now let’s break it down…

SELECT v_FullCollectionMembership.Name as SysName, v_Collection.Name as Package_Name
FROM v_FullCollectionMembership

These are fairly normal, we’re looking for the system name and collection name.

LEFT JOIN v_Collection
ON v_Collection.CollectionID=v_FullCollectionMemberShip.CollectionID

Now this matches names of collections to their CollectionID so that instead of only being able to show CollectionID’s we can show something more readable.

WHERE v_FullCollectionMembership.name in
( SELECT name FROM v_FullCollectionMembership
WHERE v_FullCollectionMembership.CollectionID='ANN00086' )

Here comes the first sub-query select. In the case of our system, we’re dealing with relatively small numbers of computers and groups, so it doesn’t matter in which order I do my queries. Originally I had this sub-select second, but for the sake of good design, I changed it. The idea is to limit your results to the smallest group as quickly as you can, so if you can limit your results to only machines in a specific CollectionID, that’ll probably cut it down quicker.

In this case it’s making the parser find machines (name) which are in a list of names selected from CollectionID ANN00086.

AND v_FullCollectionMembership.CollectionID in
( SELECT CollectionID from v_Collection
WHERE (name like 'Training%' or name like 'Support%') and name not like '%FIX%' )

This is our second sub-query select statement. It makes sure that the CollectionID you are returning is in a list of ID’s you set up. In this case, it’s a list of CollectionID’s for any collection which has a name which starts with “Training” or “Support” but doesn’t have FIX anywhere in it.

There’s two things to remember about ‘like’ statements:

  1. With “like” matches it’s important to remember the placement of the %’s makes a difference on results. One at the end of a term means anything starting with that term - or one at each end of a term means anything containing that term.
  2. LIKE matches with wildcards can be very intensive to process, so be careful if you’re using a couple of wildcards or potentially matching against a huge list of results
ORDER BY v_FullCollectionMembership.Name, v_Collection.Name

This last one’s fairly self-explanatory, order it by the name of the PC, and as a secondary ordering field, order it by the software package.



#Center #SCOM 2007 #SQL #System Centre Operations Manager #windows #WQL