Export Large Number of Users from ConfigMgr to CSV with PowerShell

Had a scenario where I required export of 50k+ users from ConfigMgr to do some analysis.

However PowerShell cmdlet Get-CMUser just hung for an hour or more. Maybe it would eventually come back with a result, I don’t know…

So I tried using WMI query:

$SiteName="<site code>"
$SCCMServer="<configmgr server>"
$result = Get-WmiObject -namespace $SCCMNameSpace -computer $SCCMServer -query "select * from sms_r_user"
$result | Export-Csv UserIDs.csv -NoTypeInformation

However this also hung for quite some time until eventually failing:

Get-WmiObject : Shutting down
At line:4 char:11
+ $result = Get-WmiObject -namespace $SCCMNameSpace -computer $SCCMServer -query ” …
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], ManagementException
    + FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand


To work around this, did a search filtering by first character of username. In this case I just looked for usernames starting with a-z or 0-9:

$SiteName="<site code>"
$SCCMServer="<configmgr server>"

$result = @()
ForEach ($letter in "a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z","0","1","2","3","4","5","6","7","8","9")
    Write-Host "Searching for usernames starting with $letter"
    $result += Get-WmiObject -namespace $SCCMNameSpace -computer $SCCMServer -query "select * from sms_r_user WHERE UserName LIKE '$letter%'"

$result | Export-Csv UserIDs.csv -NoTypeInformation

This worked perfectly and extracted all the users into CSV format within a few minutes.

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in ConfigMgr and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s