Add Devices to Different Collections Using CSV Source

[BzowK]

Hey Guys / Niall - 

I'm trying to script something which I've done parts of in the past, but not together at once and need some assistance, please...

Overall, I'm trying to add hostnames as direct members to a different collection referenced on the same row of a CSV source.  Below is an example of the CSV source which states the hostname in the first column and its domain in a 2nd column.  A collection already exists per domain which is "Prod Servers: domainfqdn".  For example, below is a similar CSV with headers:

• svr_name,svc_domainfqdn
• SYSTEM1,DOMAIN.5.com
• SYSTEM2,DOMAIN.6.com
• SYSTEM3,DOMAIN.4.org
• SYSTEM8,DOMAIN.6.local

The desired result is that once the script executes, the following occurs:

• The hostname "SYSTEM1" is added via direct rule to the collection "Prod Servers: DOMAIN.5.com"
• The hostname "SYSTEM2" is added via direct rule to the collection "Prod Servers: DOMAIN.6.com"
• The hostname "SYSTEM3" is added via direct rule to the collection "Prod Servers: DOMAIN.4.org"
• The hostname "SYSTEM8" is added via direct rule to the collection "Prod Servers: DOMAIN.6.local"

I've added systems to a single collection many times before using powerShell using something similar to the below, but am at a loss for how to add to multiple collections all within the same source.

Quote

$Computers = Import-Csv "C:\source.csv"
 Foreach ($Computer in $Computers) {
    $ResourceID = (Get-CMDevice -name $($Computer.Name)).ResourceID
    add-cmdevicecollectiondirectmembershiprule -CollectionId $CollectionID -resourceid $ResourceID -Verbose 

 I've already created the collections for each domain, so no need to have the script check for them or do so.

Any suggestions?  Thanks!

 

 

[Peter33]

Try this

import-Module -Name "$(split-path $Env:SMS_ADMIN_UI_PATH)\ConfigurationManager.psd1" -ErrorAction Stop
$SiteCode = Get-PSDrive -PSProvider CMSITE -ErrorAction Stop
Set-Location "$($SiteCode.Name):" -ErrorAction Stop

$Computers = Import-Csv "C:\source.csv" -Delimiter ","
 
 Foreach ($Computer in $Computers) {
    $devname = $Computer.svr_name
    $colname = $Computer.svc_domainfqdn
    $ResourceID = (Get-CMDevice -Name $devname -ErrorAction SilentlyContinue).ResourceID
    Add-CMDeviceCollectionDirectMembershipRule -CollectionName $colname -ResourceId $ResourceID -Verbose -ErrorAction SilentlyContinue
 }

 

[BzowK]

Thanks!

I'll give it a shot and reply back soon...

[BzowK]

OK - So I just tried it out and having an issue...

I had to make a couple of changes to values to add in $newcolname as the names of the collections is "Prod Servers: domainfqdn" plus match headers in my source file therefore the script I tested is below as well as the source CSV and finally the resulting echo when testing the script.  I'm going to play with it a bit more, but if you have any suggestions or thoughts, I'd appreciate it.  Thanks!

Modified Script

Quote

 

import-Module -Name "$(split-path $Env:SMS_ADMIN_UI_PATH)\ConfigurationManager.psd1" -ErrorAction Stop
$SiteCode = Get-PSDrive -PSProvider CMSITE -ErrorAction Stop
Set-Location "$($SiteCode.Name):" -ErrorAction Stop

$Computers = Import-Csv "C:\source1.csv" -Delimiter ","
 
 Foreach ($Computer in $Computers) {
    $devname = $Computer.svr_name
    $colname = $Computer.svc_domainfqdn
    $newcolname = “Prod Servers: $colname"
    $ResourceID = (Get-CMDevice -Name $devname -ErrorAction SilentlyContinue).ResourceID
    Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newcolname -ResourceId $ResourceID -Verbose -ErrorAction SilentlyContinue
 }

 

Source CSV

Quote

svr_name,svc_domainfqdn
LABAPPV,Prod Servers: faext.lab.net
LABDC,Prod Servers: wings.labne.com
LABEXCHANGE,Prod Servers: wings.labne.com
LABSCCM,Prod Servers: faext.lab.net
LABSVR,Prod Servers: PROD.ad

Resulting Echo

Quote

 

PS C:\> C:\test2.ps1
VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: faext.lab.net'
VERBOSE: Finish: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: faext.lab.net'. Processed 0 results in 00:00:00.
Add-CMDeviceCollectionDirectMembershipRule : No object corresponds to the specified parameters.
At C:\test2.ps1:14 char:5
+     Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newco ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...tMembershipRule], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionDirectMembershipRuleCommand
 
VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: wings.labne.com'
VERBOSE: Finish: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: wings.labne.com'. Processed 0 results in 00:00:00.
Add-CMDeviceCollectionDirectMembershipRule : No object corresponds to the specified parameters.
At C:\test2.ps1:14 char:5
+     Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newco ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...tMembershipRule], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionDirectMembershipRuleCommand
 
VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: wings.labne.com'
VERBOSE: Finish: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: wings.labne.com'. Processed 0 results in 00:00:00.
Add-CMDeviceCollectionDirectMembershipRule : No object corresponds to the specified parameters.
At C:\test2.ps1:14 char:5
+     Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newco ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...tMembershipRule], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionDirectMembershipRuleCommand
 
VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: faext.lab.net'
VERBOSE: Finish: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: faext.lab.net'. Processed 0 results in 00:00:00.
Add-CMDeviceCollectionDirectMembershipRule : No object corresponds to the specified parameters.
At C:\test2.ps1:14 char:5
+     Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newco ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...tMembershipRule], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionDirectMembershipRuleCommand
 
VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: PROD.ad'
VERBOSE: Finish: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: PROD.ad'. Processed 0 results in 00:00:00.
Add-CMDeviceCollectionDirectMembershipRule : No object corresponds to the specified parameters.
At C:\test2.ps1:14 char:5
+     Add-CMDeviceCollectionDirectMembershipRule -CollectionName $newco ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Microsoft.Confi...shipRuleCommand:AddDeviceCollec...shipRuleCommand) [Add-CMDeviceCol...tMembershipRule], ItemNotFoundException
    + FullyQualifiedErrorId : ItemNotFound,Microsoft.ConfigurationManagement.Cmdlets.Collections.Commands.AddDeviceCollectionDirectMembershipRuleCommand
 

 

To note, yes, the collections exist and after refreshing them each are still empty.  Thanks!

[Peter33]

Not sure why you added this line "$newcolname = “Prod Servers: $colname", since you added the Prod Servers part to your csv file already.

This way you are duplicating the string which causes the error.

Quote

VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: wings.labne.com'

 Just get rid of this line and change back the variable used in the last line.

[BzowK]

Good Morning - 

The CSV doesn't include the name of the collection - just the hostname and domain FQDN.  The collection names are "Prod Servers: valuein2ndcolumnofcsv" with that value being the domain FQDN.  That's why I added the line you mentioned above plus changed the variable used in the next line that adds the rule to use the new variable.  Hope that makes sense and apologies if I explained it incorrectly earlier.

Thanks

[Peter33]

Quote

The CSV doesn't include the name of the collection - just the hostname and domain FQDN.  The collection names are "Prod Servers: valuein2ndcolumnofcsv" with that value being the domain FQDN

Quote

svr_name,svc_domainfqdn
LABAPPV,Prod Servers: faext.lab.net
LABDC,Prod Servers: wings.labne.com
LABEXCHANGE,Prod Servers: wings.labne.com
LABSCCM,Prod Servers: faext.lab.net
LABSVR,Prod Servers: PROD.ad

Sounds contradicting to me, you clearly got "Prod Servers" as part of value2 in your csv, which is confirmed by the executed query.

Quote

VERBOSE: Start: Execution of WQL query: SELECT * FROM SMS_Collection WHERE CollectionType = 2 AND IsBuiltIn = 0 AND Name = 'Prod Servers: Prod Servers: faext.lab.net'

Anyways, either remove "Prod Servers" from your csv and your script will work, or leave it there and my script will work. ?