SCCM Script to call a variable in a query

Welcome Forums General PowerShell Q&A SCCM Script to call a variable in a query

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
7 months, 2 weeks ago.

  • Author
    Posts
  • #97010

    Participant
    Points: 0
    Rank: Member

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

  • #97677

    Participant
    Points: 23
    Rank: Member

    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%'"
    
  • #97679

    Participant
    Points: 0
    Rank: Member

    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 🙂

The topic ‘SCCM Script to call a variable in a query’ is closed to new replies.