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)
Hello,k
ReplyDeleteThis will do something very similar, it will look at AD for ActiveDirecoryOrganizational Objects named comp[uters and add them as collections, populate and update the collections on a schedule.
Taken from m : https://www.windows-noob.com/forums/topic/15173-the-script-to-create-device-collections-based-on-ad-ous/
Adjusted by SDIAZ to filter for computers
The purposes of this script:
1. Create device collections in SCCM based on AD. Assign Canonical name of OU to collection
and OU GUID to collection description. I use OU GUID for my further needs, so you can omit this.
In addition, I think that Canonical name is the best variant to use in SCCM but you can pick simple
Name or Distinguished Name - it is up to you
2. Define the Refresh Schedule of collection.
3. Create Query Rule for collection membership
4. Move created collection to custom folder (very handy, never saw this option in other scripts).
5. Updates collection membership at once.
#>
# Importing necessary PS modules
Import-Module ActiveDirectory
Import-Module 'C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1'
# Defining main variables
# SCCM Site
$Site = (Get-PSDRive -PSProvider CMSite).name
<# Folder to move collections into. I've selected the ready one.
You can create new folder right in script with simple "mkdir" in "${Site}:\DeviceCollection\"
#>
$TargetFolder = "${Site}:\DeviceCollection\"
# Relocating to SCCM PSDrive
cd ${Site}:
# Defining refresh interval for collection. I've selected 15 minutes period.
$Refr = New-CMSchedule -RecurCount 4 -RecurInterval Hours -Start "01/01/2017 0:00"
<# Getting Canonical name and GUID from AD OUs.
-SearchScope is Subtree by default, you can use it or use "Base" or "OneLevel".
OUs are listed from the root of AD. To change this i.e. to OU SomeFolder use -SearchBase "OU=SomeFolder,DC=maestro,DC=local"
#>
$ADOUs = Get-ADOrganizationalUnit -filter * -Properties CanonicalName -server imptobnet.com | Select-Object -Property CanonicalName, ObjectGUID | Where-Object -filterscript {$_ -like "*computer*"}
# And at last, let's create some collections!
foreach ($OU in $ADOUs)
{
$O_Name = $OU.CanonicalName
$O_GUID = $OU.ObjectGUID
# Adding collection
New-CMDeviceCollection -LimitingCollectionName 'All Systems' -Name $O_Name -RefreshSchedule $Refr -Comment $O_GUID
# Creating Query Membership rule for collection
Add-CMDeviceCollectionQueryMembershipRule -CollectionName $O_Name -QueryExpression "select * from SMS_R_System where SMS_R_System.SystemOUName = '$O_Name'" -RuleName "OU Membership"
# Getting collection ID
$ColID = (Get-CMDeviceCollection -Name $O_Name).collectionid
# Moving collection to folder
Move-CMObject -FolderPath $TargetFolder -ObjectId "$ColID"
# Updating collection membership at once
Invoke-CMDeviceCollectionUpdate -Name $O_Name
}