Create CM Collection from csv

#

# Press 'F5' to run this script. Running this script will load the ConfigurationManager

# module for Windows PowerShell and will connect to the site.

#

# This script was auto-generated at '2021-02-19 16:27:13'.

# Uncomment the line below if running in an environment where script signing is

# required.

#Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process

# Site configuration

# Site code

$SiteCode = "%SiteCode%"

# SMS Provider machine name

$ProviderMachineName = "%CM_ServerFQDN%"

# Customizations

$initParams = @{}

#$initParams.Add("Verbose", $true) # Uncomment this line to enable verbose logging

#$initParams.Add("ErrorAction", "Stop") # Uncomment this line to stop the script on any errors

# Do not change anything below this line

# Import the ConfigurationManager.psd1 module

if((Get-Module ConfigurationManager) -eq $null) {

Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1" @initParams

}

# Connect to the site's drive if it is not already present

if((Get-PSDrive -Name $SiteCode -PSProvider CMSite -ErrorAction SilentlyContinue) -eq $null) {

New-PSDrive -Name $SiteCode -PSProvider CMSite -Root $ProviderMachineName @initParams

}

# Set the current location to be the site code.

Set-Location "$($SiteCode):\" @initParams

#Script to create user & device collection from a CSV.

#CSV can include multiple entries, when creating user & device collections in same CSV error text is to be expected when no value is added in the user/device column.

$csv = Import-Csv -Path "%Path%\Collections.csv"

ForEach ($item In $csv)

{

$create_devicecollection = New-CMDeviceCollection -Name $item.DeviceCollection -LimitingCollectionName $item.LimitingCollection -RefreshType $item.RefreshType -Comment $item.Comment

Write-Host -ForegroundColor Green "Device Collection $($item.DeviceCollection) created!"

}

ForEach ($item In $csv)

{

$create_usercollection = New-CMUserCollection -Name $item.UserCollection -LimitingCollectionName $item.LimitingCollection -RefreshType $item.RefreshType -Comment $item.Comment

Write-Host -ForegroundColor Green "User Collection $($item.UserCollection) created!"

}

ForEach ($item In $csv)

{

$create_devicequery = Add-CMDeviceCollectionQueryMembershipRule -CollectionName $item.DeviceCollection -QueryExpression $item.QueryExpression -RuleName $item.RuleName

Write-Host -ForegroundColor Green "Device Query $($item.DeviceCollection) created!"

}

ForEach ($item In $csv)

{

$create_userquery = Add-CMUserCollectionQueryMembershipRule -CollectionName $item.UserCollection -QueryExpression $item.QueryExpression -RuleName $item.RuleName

Write-Host -ForegroundColor Green "User Query $($item.UserCollection) created!"

}

ForEach ($item In $csv)

{

$create_UserExclusion = Add-CMUserCollectionExcludeMembershipRule -CollectionName $item.UserCollection -ExcludeCollectionName $item.Exclude

Write-Host -ForegroundColor Green "Exclution $($item.Exclude) created!"

}

ForEach ($item In $csv)

{

$create_DeviceExclusion = Add-CMDeviceCollectionExcludeMembershipRule -CollectionName $item.DeviceCollection -ExcludeCollectionName $item.Exclude

Write-Host -ForegroundColor Green "Exclusion $($item.Exclude) created!"

}

Collections.csv

DeviceCollection,UserCollection,LimitingCollection,RefreshType,Comment,QueryExpression,RuleName,Exclude

"%DeviceCollectionName%",,"All Workstations",6,"AutoCreated","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.SystemGroupName = '%AD-GroupName%'","%QueryRuleName%",%NameOfExcludedDeviceCollection%

,"%UserCollectionName%","All Users",6,"AutoCreated",select * from SMS_R_User where SMS_R_User.UserGroupName = "%AD-GroupName","%QueryRuleName%",%NameOfExcludedUserCollection%