Imagine you have 100s of Offices across the globe, and you
need to create a SCCM collection for every office you have.
I have created a PowerShell Script to Read a CSV file and then create SCCM Collections, with a WQL Query based on the OU String.
Prepare your CSV file in this format and Save to C:\temp\Collections.csv
Its important to follow this Structure as this is the expected format by the PowerShell Script.
If your enviroment operates in one country with Multiple Offices and Region isn't required please fill it out anyway.
The Region field is used for the Limiting Collection, so the Collections will created with the Region.
If this isn't required un-comment Line 21 #$LimitingCollection = "All Systems"
Change line 36 from -LimitingCollectionName $item1.region
-LimitingCollectionName $LimitingCollection
OU,Office,Region
E.g.
Production.MyDomain.COM/EUROPE MIDDLE EAST AFRICA/UK IRELAND
NORWAY/UK IRELAND/LONDON/COMPUTERS,LONDON,EMEA
You can
manually populate your CSV, or use T-SQL Query on SCCM DB,
Or PowerShell
Get-ADOrganizationalUnit
Or any
other method you see fit.
select distinct System_OU_Name0
,right(System_OU_Name0,CHARINDEX('/',REVERSE(System_OU_Name0))-1) as Office
,case when System_OU_Name0 like ('%Europe Middle East Africa%')
then 'EMEA'
when System_OU_Name0 like ('%americas%') then 'Americas'
when System_OU_Name0 like ('%Asia Pacific%') then 'APAC'
else 'Unknown Region'
End Region
from
System_System_OU_Name_ARR
where
System_OU_Name0 like '%computers%'
and
System_OU_Name0 like 'PRODUCTION.MYDOMAIN.COM%'
order by Office
My work environment has the following AD OU Structure
With the OU holding, all the computers called Computers,
and the parent OU called “Office”
Production.MyDomain.COM/REGION/Division/Business Unit/Office/Computers
E.G.
Production.MyDomain.COM/EUROPE MIDDLE EAST AFRICA/UK
IRELAND NORWAY/UK IRELAND/LONDON/COMPUTERS
So this SQL line below
pulls out the office name by replacing '/computers' with ‘’ Nothing
And then using the Right command and CharIndex.
,right(replace(System_OU_Name0,'/computers',''),CHARINDEX('/',REVERSE(replace(System_OU_Name0,'/computers','')))-1) as Office
The Script places the Collections into the Root of Device Collections.
You can move in Bulk manually or using this line (If required)
Move-CMObject -FolderPath $FolderPath -InputObject (Get-CMDeviceCollection -Name $Collection.Name)
