Abstract
I have written a tool to check the embedded references in ConfigMgr query-based collection rules.
Background
A very common practice in designing query rules for ConfigMgr collections is to use the SMS_FullCollectionMembership WMI class to effect the inclusion or exclusion of members of other collections into the collection for which the query rule is being written. There are numerous examples and discussions about this practice on other blogs, but briefly, here's an involved scenario with the example query that meets the need:
- You have some generic collections:
- You have a collection that you design that uses the software inventory to include all systems on which .NET Framework 2.0 is installed. Let's say this is collection ID LAB00001.
- You have another collection that does something similar for MSXML 2.0, collection ID LAB00002.
- You have a collection that contains any system with less than 1G of memory installed, collection ID LAB00003.
- You have a collection that contains any system that is under the control of the EMEA desktop group, collection ID LAB00004.
- You need to create a collection for a software roll-out that the EMEA desktop group can manage, but that will not allow them to advertise to anyone that does not meet the requirements. You might have guessed it: .NET 2.0, MSXML 2.0, at least 1G of memory, and within their locus of control.
- Instead of duplicating the effort in each collection, you want to re-use the existing collections.
- This also is a best practice because if you decide to refine the queries that drive the membership of any of those collections, you would only need to update it in one place.
- You consider using the "Limit Collection Membership" feature, but realize that (a) this can only link the new collections' membership to 1 other collection where here we need 4, and (b) you cannot use this to effect one of the requirements: excluding the membership of another collection.
- To solve this issue, you create two collections. To one collection (e.g. LAB00005) you grant the EMEA team rights. To the other collection, you advertise the software and use the following query rule that uses the SMS_FullCollectionMembership WMI class:
SELECT * FROM SMS_R_System
WHERE
AND ResourceID IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID="LAB00001")
AND ResourceID IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID="LAB00002")
AND ResourceID NOT IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID="LAB00003")
AND ResourceID IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID="LAB00004")
AND ResourceID IN (SELECT ResourceID FROM SMS_FullCollectionMembership WHERE CollectionID="LAB00005")
- This produces a collection whose members have .NET 2.0, MSXML 2.0, are not in the <1G memory collection, are in the collection of systems under EMEA control, and are in the collection EMEA has been provided to control the distribution of this software release.
- Note in all of these cases, you could have used the WMI class that is created specifically for each collection. I don't think this is the common practice, but it achieves the same result. This is important to point out, as the point comes up later in this post.
- (i.e. the first sub-select could have been SELECT ResourceID FROM SMS_CM_RES_COLL_LAB00001).
The Problem
While this is a powerful technique and essentially something you can't do without in any active ConfigMgr environment, it is difficult to manage. With a large number of collections that utilize this and the certain change in what is required for the types of collections I mentioned above as "generic," keeping track of these references can be difficult. The purpose of a collection may change (e.g. .NET 2.0 becomes .NET 2.0 or higher, where something that required the collection when it was only .NET 2.0 is not compatible with >2.0) The name may change with it. A collection may be deleted and re-created for any number of reasons, which will not cause any alarm as embedded references are not tracked by the ConfigMgr Admin UI.
In spite of these issues, there is no facility for obtaining an overview of query rules with these references or, even more pressing, to decode the references to check that they still point to an existing, appropriate collection.
The Tool
I have written a tool that provides for both of these requirements: an overview and a reference checker. The tool uses integrated SQL security to access the database views directly. It is a console application that is invoked with two command line parameters: the server name and the SMS_??? database name.
The tool enumerates all existing site codes, collections, and query rules on that server. It then uses regular expressions to detect probable collection references (i.e. any site code followed by 5 hex digits). There is also a special case for the built-in collections, which do not correspond to a site code (starting instead with SMS) and do not conform to the strict standard for user-created collections (e.g. SMSDM001, SMS000GS, etc.) Finally, it cross-references those collection IDs to the actual list of collections and includes them by ID and name for review. Any collection ID that is found in a rule but not in the collections enumerated from the server is reported as "**** Unknown ****". You can search the output for this to identify bad references.
Here's an example run:
...and the output file...
I think the tool and the output should be fairly self-explanatory from there.
Notes on the limitations of this tool:
- We have a fairly large number of collections that utilize references at many client sites. I've used this tool against all of them with no problems. That having been said, there is still a possibility that some of you may have rules that fool the regular expressions I am using. That highlights the limitation of pattern matching versus parsing, only the former of which this tool does. I would be interested to see any instances of this, so please let me know. The most likely candidate would be if your system or user naming convention is close (or identical) to the collection ID patterns in ConfigMgr. If that were the case, collection query rules that reference such names could be mistaken for collection references.
- This will not catch more generic references, like WHERE CollectionID LIKE "LAB0001%". I'm guessing that if anyone is using these, it is a rare case.
- It also will not catch references that are intrinsically broken, like an invalid Collection ID (e.g. LAB000X0 or LAB00000121).
- It will not catch references to any Collection ID that does not correspond to any known site code. Besides obvious typos, I can envision a valid scenario where this would be needed, but it would be very rare:
- You have a child primary site 999.
- You create a collection at that site (999?????).
- You use that collection ID in a reference in a query rule of a collection built at a parent site. This reference would return no ResourceIDs except when run on that particular child primary and any of its children. This is where I say it's probably a very, very rare case.
- You then delete site 999.
- Any references to 999 collections are now broken, but since there's no longer a reference to site 999 in the database, the tool will not catch these.
- It will not catch references to collections that are specified using the SMS_CM_RES_COLL_<CollectionID> classes. I actually had this in the tool, but the underlying query that ConfigMgr generates uses these classes to effect the "Limit Collection Membership To" functionality. This would have meant that the tool would not only identify query rules that had embedded references but also query rules for collections with the "Limit To" collection ID specified. Since the latter is much more common and also double-checked by the Admin UI, I left their detection out of the tool. As I mentioned above, I believe the most common practice is to use the SMS_FullCollectionMembership class, so this shouldn't pose much of an issue.
- It will not catch references to other named objects such as packages, advertisements, etc. While technically possible to use these in a WQL query, I'm guessing these, too, are rare.
All of these are probably rare. If you have an appreciable number of instances of any of these exceptions, please let me know. I'd be interested in working to evolve the tool to handle them accordingly. Overall, though, I think you will find this to be a very useful tool.
You can download build 15 of CheckCollRef here.
VJD/<><