Let’s say you want to have a list of all licensed users in your Office 365 environment. You could look at the Office 365 Dashboard under Users and look at the list there, but quite often you may want to have that information in a spreadsheet for further processing. How to?, you ask? PowerShell to the rescue!
[info_box]More information on available PowerShell cmdlets for Office 365 can be found on the following site: http://onlinehelp.microsoft.com/office365-enterprises/hh125002 [/info_box]
Open PowerShell, connect to Office 365 (Connect-MsolService), and test the following cmdlets:
Get-MsolUser
“The Get-MsolUser cmdlet can be used to retrieve an individual user, or list of users. An individual user will be retrieved if the ObjectId or UserPrincipalName parameter is used.”
So, we’ve got a list of all users right now. Let’s try to export it to a CSV file that we can then open with Excel
Get-MsolUser | Export-Csv c:\allUsers.csv
If we open the file allUsers.csv now, we get to see all users in our Office 365 environment, together with lots of additional attributes
We’re just interested in the ones with licenses, so let’s filter a bit:
Get-MsolUser | Where-Object {$_.isLicensed -eq "TRUE"}
Great! We’ve now reduced the list to only those users for which the attribute isLicensed is set to True. Meeting Rooms don’t need a license, thus they don’t show up here.
Next step: exporting this list (in my case only 1 user) to Excel:
Get-MsolUser | Where-Object {$_.isLicensed -eq "TRUE"} | Export-Csv c:\licensedUsers.csv
A combination of the two code snippets above, this one here grabs all users, filters them, and exports the result to a CSV file. Opening that file, we can see the list of all users, however also with all attributes
As a last steps, let’s reduce the results further by only displaying the UserPrincipalName attribute/column/property in our CSV file:
Get-MsolUser | Where-Object {$_.isLicensed -eq "TRUE"} | Select-Object -property "UserPrincipalName" | Export-Csv c:\licensedUsers.csv
(After filtering, we then proceed to select only the UserPrincipalName property of all our users before exporting them.
References:
Using the Where-Object Cmdlet
Using the Select-Object Cmdlet
Using the Export-Csv Cmdlet
Windows PowerShell cmdlets for Office 365
Thanks for this, it’s very useful!
Glad I could help!
From the archive: Getting all licensed Office 365 users with PowerShell http://t.co/8Gzh6onzn9 #office365
RT @modery: From the archive: Getting all licensed Office 365 users with PowerShell http://t.co/8Gzh6onzn9 #office365
Getting all licensed Office 365 users with PowerShell | Office 365 MVP Rene Modery http://t.co/H8vejpl1uz