Extract Office 365 License information for all users using Powershell

 

This script gathers O365 license information for all active users.  It will populate fields within a csv for import into Excel which includes UPN, Licenses, whether it is a shared mailbox or not and whether or not the user is blocked.   A separate row will be created for each license, with the user’s UPN detailed on each row.  Once the export is complete, you can filter this list in excel to get whichever granular information you need.

$LogFilePath = $env:LOCALAPPDATA + "\Cloudwyse\Logs\user_license_report_" + $(get-date -Format ddMMyy_HHmmss) + ".log" Start-Transcript -Path $LogFilePath -NoClobber $365Pass = cat C:\cloudwyse\securestring365.txt | convertto-securestring $365Cred = new-object -typename System.Management.Automation.PSCredential -argumentlist "admin@contoso.onmicrosoft.com",$365Pass $DateTime = (Get-Date -Format "ddMMyyyy-HHmmss")   $365Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $365cred -Authentication Basic -AllowRedirection Import-PSSession $365Session Connect-Msolservice -Credential $365Cred     Write-Host  -ForegroundColor Magenta "Pulling mailbox information for all users, please be patient..." $JobStart = Get-Date $getmailbox = get-Mailbox $JobEnd = Get-Date $JobSecondsTaken =($JobEnd - $JobStart) Write-Host  -ForegroundColor Yellow "Extract complete.  The Job took" $JobSecondsTaken.Seconds "seconds." $total = $null $Job2Start = Get-Date $userList = @() foreach ($user in $getmailbox) {     $lookup = get-msoluser -userprincipalname $user.userprincipalname     Write-Host  -ForegroundColor Magenta "Current user is" $user.userprincipalname     $licenses = $lookup.licenses     foreach ($license in $licenses) {         $us = New-Object PSObject         $us | Add-Member -type NoteProperty -Name 'UPN' -Value $lookup.userprincipalname         $us | Add-Member -type NoteProperty -Name 'License' -Value $license.accountskuid         $us | Add-Member -type NoteProperty -Name 'IsShared' -Value $user.IsShared         $us | Add-Member -type NoteProperty -Name 'Blocked' -Value $lookup.BlockCredential         Write-Host  -ForegroundColor Cyan $license.accountskuid "was added to the list for" $lookup.userprincipalname         $userList += $us         }     $total = $total +1         } $Job2End = Get-Date $Job2SecondsTaken =($Job2End - $Job2Start) Write-Host -ForegroundColor Yellow "$total users processed in" $Job2SecondsTaken.Minutes "minute(s) and" $Job2SecondsTaken.Seconds "second(s)." Remove-PSSession $365Session $userlist | export-csv C:\Cloudwyse\user_license_report$datetime.csv Write-Host -ForegroundColor Yellow "Report exported to C:\Cloudwyse\user_license_report$datetime.csv"     Stop-Transcript

I’m happy to post the script as a text file if that will help anyone.