Create SCCM User collections from a CSV File

This PowerShell code will create SCCM user collections from a CSV file.


The CSV file looks like this:

ADGroup	NickName	Code	City
Location-3254	LOC-A	3254	Austin
Location-1499	LOC-B	1499	Baltimore
Location-3269	LOC-I	3269	Indianapolis
Location-1395	LOC-K	1395	Knoxville
Location-1999	LOC-M	1999	Madison
Location-1998	LOC-W	1998	Waco

#Import AD Groups from CSV
$CSV = Import-Csv c:\temp\CSC.csv

foreach($item in $csv)
    {
    "ADGroup = $($item.ADGroup) and NickName = $($item.NickName) and Code = $($item.Code) and City = $($item.City)"
    $ADGroups += $item
    }

foreach($ADGroup in $ADGroups){
#Get a random time and day of the week. This is so we don't create a ton of groups that update at the time and crash SCCM
$RandomDay = Get-Random -InputObject Monday, Tuesday, Wednesday, Thrusday, Friday, Saturday, Sunday

#Had to do a random hour like this join didn't like intergers
$RandomHour = Get-Random -InputObject 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

#Variables we later use to format the date and time in a format that SCCM likes
$TimeSuffix = ":00"
$RandomAMPM = Get-Random -InputObject AM, PM
$Today = get-date -UFormat "%d/%m/%Y"
$space = " "

#join all  of the random stuff together
$TimeAndDate = -join($Today, $space, $RandomHour, $TimeSuffix, $space, $RandomAMPM)
#Formats the date and time like this 03/12/2018 6:00 AM

#Creates a schedule for the collection to update
$Schedule = New-CMSchedule -Start "$TimeAndDate" -DayOfWeek $RandomDay -RecurCount 1

#Creates the User collection
New-CMUserCollection -Name "Users in AD Group $ADGroup.ADGroup - $ADGroup.Nickname" -LimitingCollectionName "All Users and User Groups" -RefreshSchedule $Schedule -RefreshType Periodic

#Adds the query rule to the collection
Add-CMUserCollectionQueryMembershipRule -CollectionName "Users in AD Group $ADGroup.ADGroup" -QueryExpression "select *  from  SMS_R_User where SMS_R_User.UserGroupName = 'LOWES\\$ADGroup.ADGroup'" -RuleName "Users in $ADGroup.ADGroup"

}