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.)
