Wednesday, 16 January 2019

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 "",$365Pass

$DateTime = (Get-Date -Format "ddMMyyyy-HHmmss")


$365Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -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"







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




No comments:

Post a Comment