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;
- “$Application”
- " + “"%” + $Application + “%"” + ") (Got this from a DBA friend)
- Doing a split. $Split.Split(“,”) with $Split as the query value
- “%$Application%”
- 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.)