SMS 2003: Getting a list of all of the queries for each of your collections

  • Article

Have you ever wondered how you can get a list of all of the queries for each of your collections? You may have noticed that when you try to export queries, you can select multiple queries to export, but when you export collections you have to do them one at a time. Of course if you have a large number of collections that is probably not an option.  Because of this, I created the script below that will give you a list of collections and their associated queries.  For the record, I tested this on both SMS 2003 and System Center Config Manager 2007 and it runs fine on both.

======
'miory 10/29/2007
'Lists the query for each collection in a site

SiteCode = inputbox("Enter your 3 letter site code", "Site Code")
Set fileSys = CreateObject("Scripting.FileSystemObject")
If NOT(fileSys.FileExists("C:\CollectionQueries.txt")) Then
Set oCreateFile = fileSys.CreateTextFile("C:\CollectionQueries.txt")
Set oCreateFile = Nothing
End If

Set lLocator = CreateObject("WbemScripting.SWbemLocator")

Set gService = lLocator.ConnectServer( , "root\sms\site_" &Sitecode)
Set Collections = gService.ExecQuery("Select * From SMS_Collection")
For Each Collection In Collections
Set Collection=gService.Get("SMS_Collection='" & Collection.CollectionID & "'" )
Set clsQueryRule = gService.Get("SMS_CollectionRuleQuery")
If Not (IsNull(Collection.CollectionRules)) Then
Rules = Collection.CollectionRules

For Each Rule in Rules
If Rule.Path_.Class = "SMS_CollectionRuleQuery" Then
set oFile = fileSys.OpenTextFile("C:\CollectionQueries.txt", 8, True)
oFile.writeline("Rule Name: " &Rule.Rulename &vbcrlf & "CollectionID:" & _ &Collection.CollectionID &vbcrlf & "LimitToCollectionID: " & _
&Rule.LimitToCollectionID &vbcrlf & "Query: " & Rule.QueryExpression &vbcrlf &vbcrlf )
oFile.close
Else
set oFile = fileSys.OpenTextFile("C:\CollectionQueries.txt", 8, True)
oFile.writeline("Direct Membership Rule pointing to: " &Rule.Rulename & _
&vbcrlf & "CollectionID: " &Collection.CollectionID &vbcrlf &vbcrlf)
oFile.close
End If
Next
End If

Next
wscript.echo "Finished"
======

Hope this helps!

Mike Ory