Scripting SMS: Making a Better Connection

' Create the object to make the WMI connection
Set locator = CreateObject("WbemScripting.SWbemLocator")

' Connect to server SERVER hosting SMS site XXX
Set sms = locator.ConnectServer("SERVER", "root\sms\site_XXX")

you will notice that the site code ("XXX") is specified when making the connection via WMI. So how does the SMS Administrator get by without asking? Simple: it asks WMI. One level up in the WMI hierarchy, e.g. "root\sms", there is an "SMS_ProviderLocation" class that contains one instance for each site hosted by the current server. The primary site is indicated by setting the "ProviderForLocalSite" property to "true" - only one site on a machine will be marked as such (any other sites are secondaries).

Want to see this for yourself? Using the handy WBEMTEST utility, connect to "\\SERVER\root\sms", where "SERVER" is your SMS server name. Then click "Enum Classes" and "Recursive". Scroll to the bottom of the class list and double-click on the "SMS_ProviderLocation" class to bring up the class details (all of the properties associated with each instance of the class). Click the "Instances" button to see the sites hosted by this machine. Double-click on each one looking for one (and only one) marked as the "ProviderForLocalSite".

Now that you've seen this for yourself, it is time to script it. The basic steps are:

1. Connect to "\\SERVER\root\sms".
2. Query for the local primary site code ("ProviderForLocalSite = true").
3. Reconnect to "\\SERVER\root\sms\site_XXX", where "XXX" is the result from step #2.

Before scripting this, you will need to be familiar with a few additional items:

1. WMI Query Language, or WQL. A subset of standard SQL, this is documented (where else) in the Platform SDK.
2. The SMS Extended WMI Query Language. SMS takes the standard WQL features and extends them, creating something very close to the ANSI-92 SQL standard.
3. The SWbemServices.ExecQuery method. The ExecQuery method is used to execute a specified WQL string, returning the results in an SWbemObjectSet collection.
4. The SWbemObjectSet object class. If you are familiar with Visual Basic collections, these behave similarly. Typically the members of a collection are processed with a "FOR EACH" statement.
5. The SWbemObject object class. Each member of the SWbemObjectSet returned by the ExecQuery method is an SWbemObject instance. You normally do not need to be too concerned with this class, as you are interested the specifics of the returned class instances.

As usual, the documentation makes this look much more complicated than it really is. Let's take this step by step, using the list of steps from above. First, you must connect to the SMS provider to look up the site code:

' Create the object to make the WMI connection
Set locator = CreateObject("WbemScripting.SWbemLocator")

' Connect to server SERVER hosting SMS site XXX
Set sms = locator.ConnectServer("SERVER", "root\sms")

With the connection established, you can now write a query such as "SELECT * FROM SMS_ProviderLocation" to select all site codes residing on this server. But in this specific case, we are looking for only the primary site, so the statement can be qualified and executed:

' Create the object to make the WMI connection
Set results = sms.ExecQuery("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")

Even though this query will only return one instance (record), it is still in a collection. This collection can be processed like so:

' Process the results
For each r in results
    siteCode = r.SiteCode
Next

Want to test out a query before putting it in a script? Again, WBEMTEST is very useful. After connecting to the SMS server (e.g. "\\SERVER\root\sms"), click the "Query" button, enter the WQL text, and click "Apply". Double-click on each of the instances returned to look at all of its properties.

Now that you know the site code, you can reconnect to the proper location in WMI. To do this, disconnect and reconnect to the SMS provider:

' Disconnect from "root\sms"
Set sms = Nothing

' Connect to the proper location
Set sms = locator.ConnectServer("SERVER", "root\sms\site_" & siteCode)

The SMS Administrator can actually connect to the proper location without disconnecting first, using the "OpenNamespace" method of the "IWbemService" interface, but that requires using C++. So we'll settle for the slower method.

That's all there is to it. But we can't stop there - what if you want to use a script with more than one server? Let's make the script more flexible. And, since every script needs to establish a connection, we'll turn this into a function that we can reuse in each script.

So how do you create a function? First, you have to declare it, like so:

Function Connect(server, site, user, password)

Notice that I have included four parameters. All of these must be specified when calling the function, one of the disadvantages of VBScript: it does not support default values for parameters not specified. So, to call this function you would have to specify something like:

Set sms = Connect("SERVER","SITE","USER","PW")

or maybe even something like:

Set sms = Connect("","","","")

So what does that mean? In this case, it can be interpreted to mean the current machine, primary instance, using the logged-on user's credentials. How can this one routine handle any combination of specified parameters? Really, it is fairly simple: specifying an empty string to WMI means that the default value should be used. So, the function can be defined like so:

Function Connect(server, site, user, password)
    ' Create the object to make the WMI connection
    Set locator = CreateObject("WbemScripting.SWbemLocator")

    ' Is a site code specified?  If not, find one
    If site = "" then
        ' Connect to the specified (or default) server
        Set sms = locator.ConnectServer(server, "root\sms")

        ' Select the primary site
        Set results = sms.ExecQuery("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")

        ' Process the results
        For each r in results
            site = r.SiteCode  ' Get the 3-character site code
        Next
        
        ' Disconnect
        Set sms = Nothing
    End if
    
    ' Now, connect to the specified site, returning the SWbemServices object to the caller
    Set Connect = locator.ConnectServer(server, "root\sms\site_" & site, user, password)

End Function

Easy enough? Now, this function can be included in each script, without worrying about the specifics. It can be called with whatever parameters are appropriate for your situation:

• Running the script on an SMS primary site server as a user with the necessary SMS rights? Use all empty strings:
  

  Set sms = Connect("","","","")

• Running from a different workstation, again as a user with the necessary SMS rights? Specify the SMS server name:
  

  Set sms = Connect("SERVER","","","")

• Running from a different workstation, wanting to connect to a secondary site (or maybe you just want to connect faster, avoiding the query and reconnection)? Specify the server and site code:
  

  Set sms = Connect("SERVER","XXX","","")

• Needing to specify a different user ID? Specify whatever other parameters are appropriate:
  

      Set sms = Connect("SERVER","XXX","user","password")
      Set sms = Connect("SERVER","","user","password")
      Set sms = Connect("","","user","password")
      

Want a complete copy of this code? Download it here.

Next up: converting this sample into Visual Basic, an Excel spreadsheet, and an Active Server Page. Stay tuned...