Feed Detail

October 31, 2018 at 7:00 AM

SCCM - Add Device Collection with RuleQuery

We recently had a problem in the collection population with RuleDirect, which is that to install or reinstall a new operating system, the computer needs to be removed from SCCM. The impact of this on the implementation of computers in collections with RuleDirect is excluded from the SCCM and also from the collection and even after the installation of the new operating system the computer did not return to collection anymore. To solve this issue, we started to implement the inclusion of new computers in the collection based on RuleQuery. Below is an excerpt of the code we use to perform this implementation. param( [Parameter(Mandatory=$true)][string]$SCCMUserName, [Parameter(Mandatory=$true)][string]$SCCMServer, [Parameter(Mandatory=$true)][string]$SCCMSiteCode, [Parameter(Mandatory=$true)][string]$ComputerDevice, [Parameter(Mandatory=$true)][string]$CollectionID ) $SCCMServiceAccountName = Get-APSetting SCCMServiceAccountName $SCCMServiceAccount = Get-ServiceAccount -Name $SCCMServiceAccountName -Scope 0 $SCCMServiceAccountPassword = $SCCMServiceAccount.Password | ConvertTo-SecureString -AsPlainText -Force $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SCCMUserName,$SCCMServiceAccountPassword $ParamConnectSCCM = [ordered]@{ "NameSpace" = "Root\SMS\Site_$SCCMSiteCode" "ComputerName" = $SCCMServer "Credential" = $Credentials "ErrorAction" = 'Stop' } ### ComputerDevice Validation ### try{ if($ComputerDevice.Contains('\')){ $Domain = $ComputerDevice.Split('\')[0] $HostName = $ComputerDevice.Split('\')[1] $ComputerObj = @(Get-WmiObject -Class SMS_R_SYSTEM -Filter "Name = '$HostName' AND ResourceDomainORWorkgroup = '$Domain' AND Client = '1'" @ParamConnectSCCM) } else{ $ComputerObj = @(Get-WmiObject -Class SMS_R_SYSTEM -Filter "Name = '$ComputerDevice' AND Client = '1'" @ParamConnectSCCM) } if($ComputerObj.Count -gt 1){ Write-Error "Found more than one record with the name $ComputerDevice with attributes Client = 1" } if($ComputerObj.Count -eq 1){ Write-Host "The computer: $($ComputerObj.Name) was found with the following ID: $($ComputerObj.ResourceId)" } if($ComputerObj.Count -eq 0){ Write-Error "The computer: $($ComputerDevice) not found in SCCM" } } catch{ Write-Error "Error while browsing the computer $ComputerDevice. Exception: $($_. Exception.Message)" } ### CollectionID Validation ### try{ $CollectionObj = @(Get-WmiObject -Class SMS_Collection -Filter "CollectionID = '$CollectionID' AND CollectionType = 2" @ParamConnectSCCM) if($CollectionObj.Count -eq 1){ $CollectionObj = $CollectionObj[0] Write-Host "The Collection: $($CollectionObj.CollectionID) was found in SCCM with the name: $($CollectionObj.Name)." } if($CollectionObj.Count -eq 0){ Write-Error "The Collection: $($CollectionID) not found in SCCM" } if($CollectionObj.Count -gt 1){ Write-Error "Found more than one record with ID: $($CollectionID)." } } catch{ Write-Error "Error while browsing then collection $($CollectionID). Exception: $($_.Exception.Message)" } ### Include, Create and Update Collection ### $ArrayRules = @() $ArrayComputer = @() if($ComputerObj.Count -eq 1){ foreach($Collection in $CollectionObj){ $Collection.Get() $CollectionRule = $Collection.CollectionRules if($CollectionRule.Count -eq 0){ Write-Host "The collection $($CollectionObj.Name) does not have a RuleQuery created. The initial RuleQuery will be created." $FirstQuery = '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 where SMS_R_System.NetbiosName in ("0",'+'"'+$ComputerObj.Name+'"'+')' try{ $FirstRule = (Get-WmiObject -List -Class SMS_CollectionRuleQuery @ParamConnectSCCM).CreateInstance() $FirstRule.QueryExpression = $FirstQuery $FirstRule.RuleName = $CollectionObj.Name $Collection.Put() | Out- if(($Collection.CollectionRules | Where-Object { ($_.RuleName -like "$($FirstRule.RuleName)") -and ($_.__CLASS -like "SMS_CollectionRuleQuery") } | Measure-Object).Count -eq 1){ Write-Host "QueryRule was successfully created. Name: '$($FirstRule.RuleName)' in collection '$($CollectionObj.Name)" $InvokeUpdate = $Collection.RequestRefresh() if($InvokeUpdate.ReturnValue -eq 0){ Write-Host "Installation | Added $($ComputerObj.Name) in collection $($CollectionObj.Name)." } else{ Write-Error "Failed to update collection $($CollectionObjInstall.Name)." } } } catch{ Write-Error "Failed to create RuleQuery instance. Exception: $($_.Exception.Message)" } } if($CollectionRule.Count -ne 0){ Write-Host "The collection $($CollectionObj.Name) has one or more associated Rule. A single RuleQuery containing all members will be created." foreach($Rule in $Collection.CollectionRules){ if($Rule.__CLASS -eq "SMS_CollectionRuleDirect"){ $ArrayRules += $Rule.RuleName } if($Rule.__CLASS -eq "SMS_CollectionRuleQuery"){ $Query = $Rule.QueryExpression $List = $Query.Split('()')[1].Split(',').Split(' ').Split('"',[System.StringSplitOptions]::RemoveEmptyEntries).Split('"', [System.StringSplitOptions]::RemoveEmptyEntries) $ArrayComputer += $List } } $NewArray = @() $NewArray = $ArrayRules + $ArrayComputer | select -Unique $NewArrayQuery = @() foreach($PC in $NewArray){ if($PC -notin 0){ $NovaArrayQuery += ',"'+$PC+'"' } } $NewArrayQuery += ',"'+$ComputerObj.Name+'"' $NewArrayQuery = $NewArrayQuery | select -Unique $Replace = '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 where SMS_R_System.NetbiosName in ("0"'+$NewArrayQuery+')' Write-Host "New select associate ($Replace)" try{ $NewRule = (Get-WmiObject -List -Class SMS_CollectionRuleQuery @ParamConnectSCCM).CreateInstance() $NewRule.QueryExpression = $Replace $NewRule.RuleName = $CollectionObj.Name $Collection.Put() | Out- if(($Collection.CollectionRules | Where-Object { ($_.RuleName -like "$($NewRule.RuleName)") -and ($_.__CLASS -like "SMS_CollectionRuleQuery") } | Measure-Object).Count -eq 1){ Write-Host = "QueryRule was successfully created. Name: '$($NewRule.RuleName)' in collection '$($CollectionObj.Name)." $InvokeUpdate = $Collection.RequestRefresh() if($InvokeUpdate.ReturnValue -eq 0){ Write-Host "The Collection $($CollectionObj.Name) has been updated with the new RuleQuery, containing the new computer: $($ComputerObj.Name)." Write-Host "Installation | Added $($ComputerObj.Name) in collection $($CollectionObj.Name)." } else{ Write-Error "Failed to update collection $($CollectionObjInstall.Name)." } } } catch{ Write-Error "Failed to create RuleQuery instance. Exception: $($_.Exception.Message)" } } } }

RicardoPereira
7 years ago
I recently worked on an update to the process of including computers in SCCM collection, basically using RuleQuery. Below is the code update. ? <# .SYNOPSIS SCCM Collections Update .DESCRIPTION Function to create rule query, add and or remove devices in specific SCCM collections. The function transforms all rules members into a single rule query, containing all members .PARAMETER SCCMUserName SCCM User, Value passed by AP Service Account .PARAMETER SCCMServer The SCCM server that will be connected and executed the script with WMI queries. .PARAMETER SCCMSiteCode SCCM Site Code Three characters for internal use (PBC) .PARAMETER CollectionID Collection identifier to update .PARAMETER Device Device that will be added to or removed from the collection .PARAMETER Action Action to be performed on collection, 1 to add and 2 to remove .NOTES AUTHOR: Ricardo Rérison LASTEDIT: 2019-11-01 VERSION: 1.0 PREREQUISITE: SCCM server WMI, access and write permission in SCCM, to make changes to collection members. CHANGELOG: 1.0 - Initial Release #> param( [Parameter(Mandatory=$true)][string]$CollectionID, [Parameter(Mandatory=$true)][string]$Device, [Parameter(Mandatory=$true)][string]$Action, [Parameter(Mandatory=$true)][string]$SCCMSiteCode, [Parameter(Mandatory=$true)][string]$SCCMServer, [Parameter(Mandatory=$true)][string]$SCCMUserName ) $SCCMServiceAccountName = Get-APSetting SCCMServiceAccountName $SCCMServiceAccount = Get-ServiceAccount -Name $SCCMServiceAccountName -Scope 0 $SCCMServiceAccountPassword = $SCCMServiceAccount.Password | ConvertTo-SecureString -AsPlainText -Force $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SCCMUserName,$SCCMServiceAccountPassword $ParametrosConnectSCCM = [ordered]@{ "NameSpace" = "Root\SMS\Site_$SCCMSiteCode" "ComputerName" = $SCCMServer "Credential" = $Credentials "ErrorAction" = 'Stop' } function UPDATE_COLLECTION_SCCM{ param( [STRING]$CollectionID, [STRING]$Device, [STRING]$Action ) begin{ $ContenCollectionObj = @() if($Action -eq 1){ $UpdateType = "Add" } elseif($Action -eq 2){ $UpdateType = "Remove" } $CollectionObj = @(Get-WmiObject -Class SMS_Collection -Filter "CollectionID = '$($CollectionID)'" @ParametrosConnectSCCM) if($CollectionObj.Count -eq 1){$ContentObjCollection = $CollectionObj } elseif($CollectionObj.Count -ne 1){Write-Error "Collection $($CollectionID) does not exist in SCCM database."} Write-Host "Collection Update: $($ContentObjCollection.Name) from ID: $CollectionID" Write-Host "Computer to be updated in collection: $Computer" Write-Host "Update Type: $UpdateType" } process{ Write-Host "Starting processing" function CheckDeviceInCollection{ param( $Device, $CollectionID ) $Server = "XXXXX" $DataBase = "XXXX" $User = "XXXX" $Pass = "XXXX" $connection = New-Object System.Data.SqlClient.SqlConnection $connection.ConnectionString = "Server = $Server; Database = $DataBase; User ID = $User; Password = $Pass;" $connection.Open() $command = New-Object System.Data.SQLClient.SQLCommand $command.Connection = $connection $command.CommandText = " SELECT * FROM ( SELECT DISTINCT UPPER(SUBSTRING(REPLACE(REPLACE(REPLACE(VALUE,')',''),'''',''),' ',''),2,10)) AS PC, CollectionID FROM ( SELECT CollectionID, QueryExpression FROM [XXXX].[SCCM_PBC].[DBO].[v_CollectionRuleQuery] ) AS DADOS CROSS APPLY STRING_SPLIT(QueryExpression, ',') WHERE ( UPPER(SUBSTRING(REPLACE(REPLACE(REPLACE(VALUE,')',''),'''',''),' ',''),2,10)) LIKE 'N[0-9]%' OR UPPER(SUBSTRING(REPLACE(REPLACE(REPLACE(VALUE,')',''),'''',''),' ',''),2,10)) LIKE 'W[0-9]%') ) AS Data WHERE CollectionID = '$CollectionID' AND PC = '$Device' " $Reader = $command.ExecuteReader() $DataTable = New-Object System.Data.DataTable $DataTable.Load($Reader) $Table = $DataTable $ReturnValue = $ $ReturnValue = $Table.PC $ReturnValue } function CheckActiveDeployment { param( $CollectionID ) $CheckActive = (Get-WmiObject -ComputerName $SCCMServer -Class SMS_ApplicationAssignment -Namespace root/SMS/site_$SCCMSiteCode -Filter "TargetCollectionID='$($CollectionID)' and OfferTypeID='0'").ApplicationName $CheckActive } $RulesDirect = @() $RuleQuery = @() $Start = 0 $ValInt = $ $CheckDeviceInCollection = CheckDeviceInCollection -Device $Device -CollectionID $CollectionID $ContentObjCollection | % { $_.Get() $Coll = $_ if(($_.CollectionRules).Count -le 0){ write-host "Collection has no Membership rules define, first created." if($Action -eq 1){ $QueryString = $ $QueryReplace = '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 where SMS_R_System.NetbiosName in ("0",'+'"'+$Device+'"'+')' } elseif($Action -eq 2){ $QueryString = $ $QueryReplace = '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 where SMS_R_System.NetbiosName in ("0")' } } elseif(($_.CollectionRules).Count -ge 1){ Write-Host "The collection has one or more Rules. A single RuleQuery will be created." $_.CollectionRules | % { $QueryString = $ if($_.__CLASS -eq "SMS_CollectionRuleDirect"){ $RulesDirect += $_.RuleName } elseif($_.__CLASS -eq "SMS_CollectionRuleQuery"){ $RuleQuery += ($_.QueryExpression).Split('()')[1].Split(',').Split(' ').Split('"',[System.StringSplitOptions]::RemoveEmptyEntries).Split('"', [System.StringSplitOptions]::RemoveEmptyEntries) } if($ArrayUpdate -ne $ -or $ArrayUpdate -eq $){ $ArrayUpdate = @() $ArrayUpdate = $RulesDirect + $RuleQuery | select -Unique } if($Action -eq 1){ $QueryString = $ $RespAction = "Adding" if($NewQueryArray -ne $ -or $NewQueryArray -eq $){ $NewQueryArray = @() $ArrayUpdate | % { if($_ -notin 0){ $NewQueryArray += ',"'+$_+'"' } } } $NewQueryArray += ',"'+$Device+'"' $QueryString = $NewQueryArray | select -Unique } elseif($Action -eq 2){ $QueryString = $ $RespAction = "removing" if($NewQueryArray -ne $ -or $NewQueryArray -eq $){ $NewQueryArray = @() $ArrayUpdate | % { if($_ -notin 0 -and $_ -notin $Device){ $NewQueryArray += ',"'+$_+'"' } } } $QueryString = $NewQueryArray | select -Unique } } $QueryReplace = '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 WHERE SMS_R_System.NetbiosName in ("0"'+$QueryString+')' } } function UpdateMembershipRule { param( $CollectionObj, $RespAction, $Device, $Query ) try{ $InsertNewRule = (Get-WmiObject -List -Class SMS_CollectionRuleQuery @ParametrosConnectSCCM).CreateInstance() $InsertNewRule.QueryExpression = $Query $InsertNewRule.RuleName = $Coll.Name $Coll.CollectionRules = $InsertNewRule $Coll.Put() | Out- if(($Coll.CollectionRules | Where-Object { ($_.RuleName -like "$($InsertNewRule.RuleName)") -and ($_.__CLASS -like "SMS_CollectionRuleQuery") } | Measure-Object).Count -eq 1){ $InvokeUpdate = $ContentObjCollection.RequestRefresh() if($InvokeUpdate.ReturnValue -eq 0){ $MessageReturn = "The Collection $($Coll.Name) from ID: $($Coll.CollectionID) has been updated, $RespAction Computer: $Device" } else{ $MessageReturnWarning = "Failed to update collection $($Coll.Name) from ID: $($Coll.CollectionID)" } } } catch{ Write-Warning "Unable to update collection: $($Coll.CollectionID)" } if($MessageReturnWarning){Write-Warning "$MessageReturnWarning"} if($MessageReturn){Write-Host "$MessageReturn"} } UpdateMembershipRule -CollectionObj $ContentObjCollection -RespAction $RespAction -Device $Device -Query $QueryReplace function Complia
Expand Post