Author Posts

March 24, 2018 at 3:07 pm

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%"
4) 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.)

April 2, 2018 at 1:15 pm

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%'"

April 2, 2018 at 1:17 pm

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 🙂