- 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.
Now let’s break it down…
These are fairly normal, we’re looking for the system name and collection name.
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.
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.
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:
- 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.
- 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
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.