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

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

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.

3 Comments. Leave new

This is great! I was looking for an alternative to a report of boundary members. I have collections for each subnet that I can use this against.

I am currently trying to expand upon this so that I have an export of all collection members within a subfolder of collections in SCCM.

Reply

This worked really well, thanks. Is it possible to include additional fields in this report using PowerShell? Specifically, I would like to include the Primary User(s) and Currently Logged on User when generating a file from a collection.

Reply
Pio Balistoy
March 3, 2022 8:40 pm

That’s great to hear! Yeah you can definitely add more columns. I only selected the columns relevant to my needs using the select-object column. You could definitely select the other columns/properties available.

Reply

Leave a Reply

Your email address will not be published.