All posts tagged powershell

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