How do i export user information from Office 365

Curtsey of http://www.serverlab.se
This article is about how to get a list of licensed users. The list should contain what subscriptions is signed to what user. Article is devided into two logical parts: getting a simple list with licenses and getting detailed list of subscriptions per user.

Note: Speaking of terms, a license is a ”seat” provisioned to a user in you Office 365 tenant and a Subscription is a service you purchased from Microsoft. Example: PowerBI Standalone is a Subscription. Plan E3 is a Subscription. How many liceses for PowerBI or E3 you bought is how many ”seats” you bought.

Part 1. A simple list of licensed users. 
I’m not the first one on consultancy field being asked to provide a list of licensed (unlicensed, licensed only for…, combo of both and so on) users in Office 365. Today was my turn. And maybe, I’m the only one getting errors/warnings in 90% of cases when I’m trying to use tools the way is meant. I doubt it  If you are like me, you definitely stumbled on warning: ”More results are available. Please specify one of All or MaxResults parameters” in Azure Active Directory for Windows PowerShell module.
So you open PowerShell, connect to a tenant and expect that a commandlet you read onMicrosoft knowledge base  (Read it anyway) about

Get-MsolUser | Where-Object { $_.isLicensed -eq ”TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Export-Csv c:\LicensedUsers.csv

would work. Firtsly, you are going to get this:

A classy access denied meaning you will have to create a folder on the C: and export you csv into it.
Secondly, the chanses are you will see that as well:

 
Only a warning you think. Fine, I still get what I want. And that’s true. Because, when you go to c:\themap\ the file ”LicensedUsers.csv” is there. You open it and it’s empty. Nothing. Well, they warned you, aren’t they?
Here’s what you do. The simpliest and fastest is to put -All parameter after Get-MsolUser

The real cmdlet is 

Get-MsolUser -All | Where-Object { $_.isLicensed -eq ”TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Export-Csv c:\LicensedUsers.csv

That’s how I got my csv.
—
Part 2. Detailed list containing subscriptions assigned to users.
But let’s discuss it further.
Say you need to do a detailed report about licensed users, sunbscriptions and who is subscribed to what. There’s no way you can easily get such a document from the tenant. You’ll have to produce it yourself. And PowerShell is your best friend.
But first you need to understand how Microsoft codes its plans and subscriptions and how to decode them. AccountSku is a ”codename” for subscription.
If you enter a short command:

Get-MsolAccountSku

you will see this

As it comes from the screeshot I got Power BI Standalone, E3 plan (ENTERPRISEPACK) and Project Online Standalone. Here is a goot translation tabel that helps to interpret AccountSku into human friendly language.

Office 365 Service Plan SKU’s

This will help you to read the data that comes in a csv-file we are going to produce. When you run this:

$lines = @() foreach($msolUser in (Get-MSOLUser)) { $UserInfo = Get-User -Identity $msolUser.UserPrincipalName foreach($license in $msolUser.Licenses) { $lines += New-Object PsObject -Property @{ ”Username”=”$($UserInfo.DisplayName)”; ”Company”=”$($UserInfo.Company)”; ”AccountSKUID”=”$($license.AccountSKUid)” } } } $lines | Export-CSV C:\output.csv -NoTypeInformation

you will get a file with a list of users and subscrivtions which are signed to the person.

 
The format of data presentation is obvious – you get tenantname:SUBSCRITION for every user as many times as assigned licenses. And that is good, right? Couse we got our data into Excel.