SCCM Script to call a variable in a query

Hi All,

Starting to automate some tasks in PowerShell for SCCM and ran into a snag. I typically use this query to query applications from computers. For example, let’s say I want to find all computers that don’t have Chrome installed. I would do the following;

"select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_COMPUTER_SYSTEM.Name not in  (select distinct SMS_G_System_COMPUTER_SYSTEM.Name  from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%Chrome%""

Now this works as it should when I do it via the GUI and with a hard-coded value for the application. However, I want to automate having to do this, and I want to use params so I don’t have to hard-code anything in. This is my current script;

Function Get-SCCMMissingApp {
    [cmdletbinding(DefaultParameterSetName = 'SCCMQueryMissingApp', SupportsShouldProcess = $true, ConfirmImpact = 'Medium')]
    Param (
        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            HelpMessage = 'Please enter a collection name for your new SCCM collection',
            ParameterSetName = 'SCCMQueryMissingApp')]
        [ValidateNotNullOrEmpty()]
        [Alias('Name', 'Collection')]
        [psobject]$CollectionName,

        [Parameter(Mandatory = $true,
            ValueFromPipeline = $true,
            ValueFromPipelineByPropertyName = $true,
            HelpMessage = 'Please enter an application name for your new SCCM collection',
            ParameterSetName = 'SCCMQueryMissingApp')]
        [ValidateNotNullOrEmpty()]
        [Alias('LimitingCollection')]
        [psobject]$LimitingCollectionName,

        [Parameter(HelpMessage = 'Please type in a comment/description for your SCCM collection',
        ParameterSetName = 'SCCMQueryMissingApp')]
        [string]$Comment,

        [Parameter( 
        ValueFromPipeline=$true,
        ValueFromPipelineByPropertyName=$true,
        HelpMessage='Please type in the appropriate application name that you want to query')]
        [string]$Application = 'UltraVNC',

        [Parameter(HelpMessage = 'The error log is for any errors that occur. They will be stored in the specified location',
            ParameterSetName = 'SCCMQueryMissingApp')]
        [string]$ErrorLog = (Read-Host 'Please enter a UNC path for an error log if an error occurs in your script')
    )
    Begin {   
        Write-Warning 'Please ensure you have started a PowerShell Window connecting to your appropriate SCCM environment.'
    }

    Process {
        Try {
            Write-Verbose 'Collecting New-CMDeviceCollection parameters'
            $NewCMDeviceCollectionSPLAT = @{
                'Name'               = $CollectionName
                'LimitingCollectionName' = $LimitingCollectionName
                'RefreshType'        = 'Manual'
                'Comment'            = $Comment
                'Confirm'            = $true
            }
            Write-Verbose 'Creating new device collection'
            $NEWCMDeviceCollection = New-CMDeviceCollection @NewCMDeviceCollectionSPLAT

            Write-Verbose 'Creating new custom object'
            $NEWCMDeviceCollectionPSOBJECT = [pscustomobject] @{
                'CollectionID'         = $NEWCMDeviceCollection.CollectionID
                'SmsProviderObectPath' = $NEWCMDeviceCollection.SmsProviderObectPath
                'LastChangeTime'       = $NEWCMDeviceCollection.LastChangeTime
                'LimitingCollectionTo' = $NEWCMDeviceCollection.LimitingCollectionName
                'CollectionName'       = $NEWCMDeviceCollection.Name
            }

            Write-Verbose 'Outputting custom object based on New-CMDeviceCollection output'
            $NEWCMDeviceCollectionPSOBJECT

            IF ($NEWCMDeviceCollection.Name -match "\w") {

                Write-Output "The collection $CollectionName has been created. Querying the missing application will now begin"
                $RuleName = $CollectionName += 'MissingApp'
                #Query missing application from device collection
               $AddDeviceCollectionQuerySPLAT = @{
                    'CollectionName'  = $CollectionName
                    'CollectionID'    = $NEWCMDeviceCollection.CollectionID
                    'RuleName'        = $RuleName
                    'QueryExpression' = "select SMS_R_SYSTEM.ResourceID,
                                    SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,
                                    SMS_R_SYSTEM.SMSUniqueIdentifier,
                                    SMS_R_SYSTEM.ResourceDomainORWorkgroup,
                                    SMS_R_SYSTEM.Client from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_COMPUTER_SYSTEM.Name not in  (select distinct SMS_G_System_COMPUTER_SYSTEM.Name  from SMS_R_System  inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId  inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId  where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "*$Application*")"
                }
                $AddDeviceCollectionQuery = Add-CMDeviceCollectionQueryMembershipRule @AddDeviceCollectionQuerySPLAT
    
                Write-Verbose 'Starting query'
                $AddDeviceCollectionQuery
            }#IF

            else {
                Write-Output 'No device collection was created. Please try re-running the script'
            }#ELSE
        }#TRY
        Catch {
            Write-Warning 'An error has occured. Please review the error logs'
            $_ | Out-File -Encoding utf8 -FilePath ('FileSystem::' + $ErrorLog)
            #Throw error to host
            Throw
        }
    }#Process 
    End {Write-Verbose 'The function has completed'}
}#Function

At the end of the query, you’ll see the $Application variable. I have tried the following for this to work;

  1. $Application
  2. " + “”%" + $Application + “%”" + ") (Got this from a DBA friend)
  3. Doing a split. $Split.Split(",") with $Split as the query value
  4. “%$Application%”
  5. Tried splitting commas with ASCII. $([char]0X002C)

However, I am still stuck. I keep getting errors about converting to an integer. I did state a [string] value however. Everything else works in the script except the query. Any ideas? (I’m not a DBA by any means, so I’m not the best at SQL queries.)

In SQL you need to use single quotes for strings and the percent sign is the wildcard.

Try this

"SELECT columns FROM table WHERE column LIKE '%$Application%'"

Ah yeah, sorry Jeremy. I forgot to respond make a comment on this ticket that I found out the issue. I did pretty much the same thing. Thank you :slight_smile: