During the TechEd Kent showed one of our solution that allows you to save all the Collections to an Excel file or to create Collections based on Excel template. We have received a lot of emails and twitter tweets that when we are going to publish it and good news is that we will publish it now 🙂 . If you haven’t seen the Kent TechEd video, then I recommend to watch it before you use this module. This is not the latest version and it is work in progress release. We will continue to improve this PowerShell module.

Here are commands. Run these commands step by step

#Import the Module
Import-Module -Name CTConfigMgr2012R2Automation

#Connect to SQL Server
New-SQLServerConnection -SQLServer . -Database CM_PS1

#Connect to ConfigMgr Server
New-ConfigMgrConnection

#Test the SQL Query
New-SQLQuery | Out-GridView

#Test the Collection Rules Query
Get-CTCMCollectionRules | Out-GridView

#Query all the Collections Information
$CollectionData = New-SQLQuery

#Query all the Collections Query Rules
$QueryData = Get-CTCMCollectionRules

#Dump the information to a CSV file because we dont have Excel Application on Primary Site Servers
Export-CMDataTOCSV -InputObject $CollectionData -FilePath 'C:\Users\kaj\Desktop\Reports\ViaMonstraCollections.csv'
Export-CMDataTOCSV -InputObject $QueryData -FilePath 'C:\Users\kaj\Desktop\Reports\ViaMonstraCollectionQueries.csv'

#Run this command on your admin PC to generate the Excel Report
Export-CMCollectionsInformationToExcel -ReportsFolder 'C:\Users\administrator\Desktop\Reports'

# Job Done and we can go home
# Have a nice day
# Kaido
Remove-Module -Name CTConfigMgr2012R2Automation


##################        PART 2        #########################################################

# We need Configuration Manager Excel Template for this part

#Import the Module
Import-Module -Name CTConfigMgr2012R2Automation

#Connect to Site Server
New-ConfigMgrConnection

#Create the Collections remotely from Admin PC
Import-CMCollectionsFromExcel -TemplateFilePath 'C:\Users\administrator\Desktop\Reports\CTCollectionsTemplate.xlsx' -Remote

#Create the Collections on Primary Site Server

#Run this command on Admin PC to convert the Excel to CSV files
Convert-CMExcelTemplateToCSV -TemplateFilePath 'C:\Users\administrator\Desktop\Reports\CTCollectionsTemplate.xlsx' `
-CollectionsFilePath 'C:\Users\administrator\Desktop\Reports\Collections.csv' `
-QueriesFilePath "C:\Users\administrator\Desktop\Reports\Queries.csv"

#Run this command on Primary Site Server
Import-CMCollectionsFromExcel -CollectionsFile 'C:\Users\administrator\Desktop\Reports\Collections.csv' `
-QueriesFile 'C:\Users\administrator\Desktop\Reports\Queries.csv'

Download link includes also the Excel template and copy the PowerShell module to your modules folder C:\Users\YOURUSERNAME\Documents\WindowsPowerShell\Modules.

Currently this module is only tested on Windows Server 2012 R2 with Configuration Manager 2012 R2 with CU´s

You can download the module from here [download id=”212″]