Automating ConfigMgr Query Creation with Powershell

I'm currently working on implementing a fairly large System Center Configuration Manager (ConfigMgr or SCCM depending on who you ask and the time of day) hierarchy. The hierarchy spans multiple domains on the workstation side of the house and we are targeting and tracking client deployment on a per domain basis.  With 11 domains and 4 queries per domain on 3 site servers I wanted to make my life a little easier so I spent some quality time with the ConfigMgr SDK and the PowerGui Script Editor to hack out a script to do it for me.

The script simply has 2 arrays that it iterates over, one for domains, and the other for the queries to create.  The array for the Domains is a plain vanilla list of strings.  The Array for the Queries is a bit more complex since it required more than a single piece of data per query.  I used an array of hashmaps for this.  There may be a more elegant way to handle this (an array of custom objects perhaps) but my days of perl have made me relatively comfortable with complex nested data structures. 

All of that was simple.  But issues with the Put() method on the WMI class gave me quite a bit of trouble.  Apparently, the put method occasionally fails to function for no apparent reason.  To resolve this I had to trap the exception and re-call the put method.  That worked splendidly.  After figuring out a bit more of the structure of the objects returned I was also able to spit out a nice bit of text if the creation succeeded of error out if it failed. 

One major item to note, the SDK will allow you to create duplicate queries.  So be prepared to delete some stuff if you play around with this script.  And now what you've probably been scrolling past all of this text to find...

$site = 'SMS'
$siteserver = 'SCCMServer01'

[array] $domains = @(
    'DOMA',
    'DOMB',
    'DOMC',
    'DOMD'
)

[array] $queries = @()

$queries += @{
    'Name' = '|DOMAIN| - All Workstations';
    'Expression' = 'select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.ADSiteName, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.Client = 1 and SMS_R_System.ResourceDomainORWorkgroup = "|DOMAIN|" and SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT%Workstation%"';
    'TargetClassName' = 'SMS_R_System';
}
$queries += @{
    'Name' = '|DOMAIN| - All Client Workstations';
    'Expression' = 'select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT%Workstation%" and SMS_R_System.ResourceDomainORWorkgroup = "|DOMAIN|" and (SMS_R_System.Client = 1 or SMS_R_System.Client is not null )';
    'TargetClassName' = 'SMS_R_System';
}
$queries += @{
    'Name' = '|DOMAIN| - All Non-Client Workstations';
    'Expression' = 'select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName, SMS_R_System.ADSiteName from  SMS_R_System where SMS_R_System.OperatingSystemNameandVersion like "Microsoft Windows NT%Workstation%" and SMS_R_System.ResourceDomainORWorkgroup = "|DOMAIN|" and (SMS_R_System.Client = 0 or SMS_R_System.Client is null )';
    'TargetClassName' = 'SMS_R_System';
}
$queries += @{
    'Name' = '|DOMAIN| - All Computers';
    'Expression' = 'select SMS_R_System.Name, SMS_R_System.SMSAssignedSites, SMS_R_System.IPAddresses, SMS_R_System.IPSubnets, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonUserName, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.NetbiosName, SMS_R_System.ADSiteName from  SMS_R_System where SMS_R_System.ResourceDomainORWorkgroup = "|DOMAIN|"';
    'TargetClassName' = 'SMS_R_System';
}

function New-SMSquery {
    Param (
        [string] $SiteServer = '.',
        [string] $Site,
        [string] $Name = $(throw "Missing required parameter (Name)"),
        [string] $Expression = $(throw "Missing required parameter (Expression)"),
        [string] $TargetClassName = 'SMS_R_System'
    )
    Process {
        trap [System.Management.Automation.RuntimeException] {
            if ($_.Exception.ErrorRecord.TargetObject -eq 'put') {
                $newquery.put()
                continue
            } else {
                break
            }
        }
        $eap = $ErrorActionPreference
        $ErrorActionPreference = 'stop'
        $QueryClass = [wmiclass]"\\$Siteserver\root\sms\site_$($Site):SMS_Query"
        $newquery = $QueryClass.CreateInstance()
        $newquery.name = [string] $Name
        $newquery.expression = [string] $Expression
        $newquery.targetclassname = [string] $TargetClassName
        $newquery.put()
        $ErrorActionPreference = $eap
        return $newquery
    }
}

foreach ($d in $domains) {
    foreach ($q in $queries) {
        $newq = new-SMSQuery -SiteServer $SiteServer -Site $Site -Name $($q.Name.Replace('|DOMAIN|',$d)) -Expression $($q.Expression.Replace('|DOMAIN|',$d)) -TargetClassName $($q.TargetClassName)
        $newq | gm
        if ($newq[0].IsInstance) {
            Write-Host "Created query: $($newq.Name) ($($newq.QueryID))"
        } else {
            Write-Error "Failed to create query $($q.Name.Replace('|DOMAIN|',$d))"
            exit
        }
    }
}