Microsoft SQL Server Database

How To Export Collections in SCCM to CSV Using Powershell

1 min read

Apr 30, 2020 12:00:00 AM

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

Navigate to Monitoring > Overview > Reporting > Reports > Asset Intelligence. This lists the various reports available.
Click Hardware 01A - Summary of computers in a specific collection.
Select the collection for which you want to generate the report.
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.