Get Office 365 license type from a csv of users

I would like to export a list of users and their respective office 365 license type.
The users would come from a CSV.
LVL 9
K BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JonathanSpitfireSenior Solutions EngineerCommented:
Hi KB - have you seen this Microsoft knowledge base article?

MS KB 2777380 - Getting all Licensed Office 365 users with PowerShell

I have not tried it yet myself, but I'm gonna give it a shot after I get back from dinner.

Hope this helps,

Jonathan
K BAuthor Commented:
Thanks for your reply. I actually have a list of users and just need that list of users.
There are almost a half million mailboxes so the small subset is the "ask" from the company.
JonathanSpitfireSenior Solutions EngineerCommented:
Holy cow! You've got half a million users in O365???

Ok, give this a shot. You'll need a list of users in a csv that contains the UPN for each user.

eg:

UserPrincipalName
joe@contoso.com
sally@contoso.com
jimbob@contoso.com

Connect to O365 with PowerShell (I assume you know how, but for the benefit of anyone reading this who doesn't know how:

How to connect and remotely manage Office 365 with PowerShell

Then run this command:

$DataPath = "C:\temp\output.csv"
$Results = @()
$accounts = Import-csv c:\temp\users.csv
 ForEach ($account in $accounts)

{
$UPN = $account.UserPrincipalName
$License = Get-MsolUser -userprincipalname $UPN
$Mbx = Get-Mailbox $UPN

      $Properties = @{
      Name = $Mbx.name
      UPN = $UPN
      License = $License.Licenses[0].AccountSkuId
      }

$Results += New-Object psobject -Property $properties

}

$Results | Select-Object Name,UPN,License | Export-Csv -notypeinformation -Path $DataPath

-----------------------------------------------
The output file will look like this:

"Name","UPN","License"
"Joe Smith","joe@contoso.com","contoso:ENTERPRISEPACK"
"Sally Spencer","sally@contoso.com","contoso:ENTERPRISEPACK"
"Jim Bob Carpenter","jimbob@contoso.com","contoso:STANDARDPACK"


There's obviously more you can do....I adapted this from here:

How to Combine PowerShell Cmdlet Output in a Single CSV File

Hope this helps,

Jonathan

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K BAuthor Commented:
Jonathan,

Thank you for that.  

The Get-Mailbox doesn't want to accept UPN.  I changed it to Get-MsolUser but name is missing from the Output.

Any Ideas?

$DataPath = "C:\scripts\Spitoutput.csv"
$Results = @()
$accounts = Import-csv c:\scripts\300.csv 
 ForEach ($account in $accounts)

{
$UPN = $account.UserPrincipalName
$License = Get-MsolUser -userprincipalname $UPN
$Mbx = Get-MsolUser -userprincipalname $UPN

      $Properties = @{
      Name = $Mbx.name
      UPN = $UPN
      License = $License.Licenses[0].AccountSkuId
      }

$Results += New-Object psobject -Property $properties

}

$Results | Select-Object Name,UPN,License | Export-Csv -notypeinformation -Path $DataPath

Open in new window



If I also may, do you know how to swap the current output...

License
Contoso:ENTERPRISEPACK
Contoso:STANDARDPACK
Contoso:STANDARDWOFFPACK
Contoso:STANDARDPACK
Contoso:STANDARDPACK
Contoso:ENTERPRISEPACK
Contoso:ENTERPRISEPACK
Contoso:ENTERPRISEPACK
Contoso:STANDARDWOFFPACK

...(from the license column) with a table like this (below)?

$Sku = @{ 
    "DESKLESSPACK" = "Office 365 (Plan K1)"
    "EXCHANGEDESKLESS" = "Exchange Online Kiosk" 
    "DESKLESSWOFFPACK" = "Office 365 (Plan K2)" 
    "LITEPACK" = "Office 365 (Plan P1)" 
    "EXCHANGESTANDARD" = "Office 365 Exchange Online Only" 
    "STANDARDWOFFPACK_IW_FACULTY" = "O365 Education for Faculty"
    "POWER_BI_STANDARD" = "Microsoft Power BI"
    "IT_ACADEMY_AD" = "IT_ACADEMY_AD"
    "EXCHANGESTANDARD_STUDENT" = "Exchange Online (Plan 1) for Students"
    "OFFICESUBSCRIPTION_STUDENT" = "Office ProPlus Student Benefit"
    "OFFICESUBSCRIPTION_FACULTY" = "O365 ProPlus for Faculty" 
    } 

Open in new window

JonathanSpitfireSenior Solutions EngineerCommented:
In our org, the UPN is also the default email address....so in my case it works like a champ.

The Get-Mailbox is looking for a mailbox email address if I recall correctly.

Are your UPNs also your default SMTP addresses, or are they different? if they are different, then that is likely why.

I won't be able to look at the table until Monday.

Hope this helps,

Jonathan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office 365

From novice to tech pro — start learning today.