Mark Iwaszko's Technical Blog - Creating SMS Collections using SQL

When you create dynamic Collections in SMS using query based rules, you have to define the query using WQL. This is fine in most circumstances, but as you have probably already found, WQL is nowhere near as rich in functions as SQL and whereas a query could be created using SQL to get the result set you want, you just can’t get the same from WQL.

Consider the scenario where you want to create a Collection that contains all workstations that have not reported Hardware Inventory for more than 14 days. To perform the query in SQL, you could use the GetDate() function to return the current date and use the DateDiff() function to compare the current date with the LastHWScan date and return all workstations where this value was greater than 14, as demonstrated below.

select sys.ItemKey, sys.DiscArchKey, sys.Name0, sys.SMS_Unique_Identifier0, sys.Resource_Domain_OR_Workgr0, sys.Client0 from System_DISC AS sys JOIN WorkstationStatus_DATA AS wks on sys.ItemKey = wks.MachineID where DATEDIFF(dd,wks.LastHWScan,GetDate()) > 30

When you create a Query Based Rule in the SMS Administrator console, SMS stores the WQL and also translates the WQL into a SQL query. The WQL and SQL are stored in the Collection_Rules_SQL table, which uses the CollectionID and query index as key. When SMS performs Collection Evaluation, it uses the SQL query in the SQL column of this table rather than the WQL. Therefore, if you create a Query Based Rule in the console, but don’t set any criteria, you can then modify the translated SQL to whatever you like, and the Collection membership will then be evaluated based on that query. As long as you don’t change the WQL by editing the Collection in the SMS Admin Console, this provides a useful workaround.

Here’s how you do it:
1. Create the Collection in the SMS Administrator Console. Create a single Query based rule, but don’t define any criteria.
2. Determine the Collection ID (you could run the WQL query ‘select CollectionID from SMS_Collection where name = ‘<collection name>’’)
3. Using SQL Query Analyser, run the following query to update the SQL query rule for the new Collection
Update Collection_Rules_SQL set SQL = ‘select SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, SMS_R_System.SMS_Unique_Identifier0, SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 from System_DISC AS SMS_R_System JOIN WorkstationStatus_DATA AS wks on SMS_R_System.ItemKey = wks.MachineID where DATEDIFF(dd,wks.LastHWScan,GetDate()) > 30’ where CollectionID = <CollectionID>
4. Update the Collection membership and the Collection will now contain all machines that have not reported inventory within the last 14 days. Be sure not to modify the query using the Administrator console, or the SQL will be overwritten!