Coretech Configuration Manager 2012 R2 PowerShell automation module 0.1

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 zip Coretech Configuration Manager 2012 R2 PowerShell automation module 24.61 kB
Downloaded 422 times


Comments (3):

  1. the hoang says:

    hi,
    when I ran this command I get the error below.
    Export-CMDataTOCSV -InputObject $CollectionData -FilePath ‘C:\Users\thehoang\Desktop\Reports\ViaMonstraCollections.csv’

    Cannot validate argument on parameter ‘InputObject’. The argument is null or empty. Provide an argument that is

    not null or empty, and then try the command again

  2. Thiago says:

    Hi Kaido, After run the command

    Export-CMCollectionsInformationToExcel -ReportsFolder ‘C:\Users\administrator\Desktop\Reports’

    I am getting the following error:

    WARNING: 2015-08-05 11:00:24:
    2015-08-05 11:00:24: ————————————————–
    2015-08-05 11:00:24: — SCRIPT PROCESSING CANCELLED
    2015-08-05 11:00:24: ————————————————–
    2015-08-05 11:00:24:
    2015-08-05 11:00:24: Error in C:\CTConfigMgr2012R2Automation\CTConfigMgr2012R2Automation.psm1
    2015-08-05 11:00:24:
    2015-08-05 11:00:24: ————————————————–
    2015-08-05 11:00:24: — Error Information
    2015-08-05 11:00:24: ————————————————–
    2015-08-05 11:00:24:
    2015-08-05 11:00:24: Line Number: 319
    2015-08-05 11:00:24: Offset: 9
    2015-08-05 11:00:24: Command:
    2015-08-05 11:00:24: Line: $HeaderRange = $WorkSheet.Range($UsedRange[0],$UsedRange[-1])

  3. Open-Mike says:

    Same error:
    WARNING: 2016-02-15 23:51:00:
    2016-02-15 23:51:00: ————————————————–
    2016-02-15 23:51:00: — SCRIPT PROCESSING CANCELLED
    2016-02-15 23:51:00: ————————————————–
    2016-02-15 23:51:00:
    2016-02-15 23:51:00: Error in
    2016-02-15 23:51:00:
    2016-02-15 23:51:00: ————————————————–
    2016-02-15 23:51:00: — Error Information
    2016-02-15 23:51:00: ————————————————–
    2016-02-15 23:51:00:
    2016-02-15 23:51:00: Line Number: 319
    2016-02-15 23:51:00: Offset: 9
    2016-02-15 23:51:00: Command:
    2016-02-15 23:51:00: Line: $HeaderRange = $WorkSheet.Range($UsedRange[0],$UsedRange[-1])

    2016-02-15 23:51:00: Error Details: Exception from HRESULT: 0x800A03EC
    2016-02-15 23:51:00:

Leave a Reply