How To Export Collections in SCCM to CSV Using Powershell

Posted in: Site Reliability Engineering, Technical Track

Your mission: Gather a list of servers in a specific collection on System Center Configuration Manager (SCCM) so you or your client can verify the servers in the collection.

How will you do it?

First, a disclaimer. I’m a data platform professional and not an SCCM administrator. However, lately I’ve been performing some administration-related tasks on SCCM. In fact, reports are actually “built-in” on SCCM.

SCCM GUI

  1. Navigate to Monitoring > Overview > Reporting > Reports > Asset Intelligence. This lists the various reports available.
  2. Click Hardware 01A – Summary of computers in a specific collection.
  3. Select the collection for which you want to generate the report.
  4. Click Run.

This generates the report for the selected collection. This works flawlessly if you install the correct report builder on the client from which you are running reports. The version for the report builder should match the SQL server version for the SCCM. If it’s not the same version or not installed on the system from which you’re running the console, it won’t work.

Enter Powershell

Powershell is a nifty tool, not just for SQL Server DBAs, but for anyone working in a Windows environment. You can quickly generate this report using Powershell. Here’s how I did it:

[String]$CollectionID = "CollectionID" #replace with the CollectionID that you want to generate the report for
[String]$SiteCode = "SiteCode" #This is the site code for the SCCM
[String]$Outfile= "C:\Pythian\Servers-Running SQL-Servers.csv" #Location where you want to drop the file.
$delimiter = ',' 

Import-Module "$($ENV:SMS_ADMIN_UI_PATH)\..\ConfigurationManager.psd1"

Set-Location "$SiteCode`:"

$Servers = Get-CMCollectionMember -CollectionId $CollectionID  | Select-Object Name,Status,DeviceOS,DeviceOSBuild,IsVirtualMachine,IsActive,LastActiveTime,IsDecommissioned,SerialNumber,MACAddress,CNLastOnlineTime,CoManaged

$Servers | ConvertTo-Csv -Delimiter $delimiter -NoTypeInformation | out-file $Outfile -encoding ascii

This gets the job done. You can change the columns in Select-Object to gather the information you require. If you’re going to do this more than once, you can improve the script and make it a parameterized function. You can also select a different output format using the various ConverTo commandlets. Improve and modify the script as required.

email

Authors

Interested in working with Pio? Schedule a tech call.

About the Author

Lead Microsoft Data Platform Consultant
Pio Balistoy is a Microsoft MVP for Data Platform from Singapore. He has been a Database professional for more than 17 years. He brings his passion for SQL to the community by being one of the Community leads for both Philippine Data Platform Forums (formerly Philippine SQL Server User Group) and Singapore SQL PASS.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *