Export a list of Office 365 users and their licenses in all customer tenants with delegated administration

Here’s a script that reports on all licensed Office 365 users in all of your customer tenants, and exports their details and their license info to a CSV file.

It uses your Office 365 delegated admin credentials to retrieve all of your customers.Retrieve All Office 365 Customers

Then it pulls user and license info from each customer.Retrieve License Info From Office 365 Customers

It appends this information to a CSV as it runs.Office 365 User Info As CSV

How to export all customers’ Office 365 users and license details to CSV

  1. Copy and paste the code at the bottom of this page into Visual Studio Code.
  2. Save it as a PowerShell (ps1) file. Install the PowerShell extension if prompted.
  3. Press F5 to run the script.
  4. Enter the credentials of an Office 365 Delegated Admin
  5. Wait for it to complete
  6. See a list of all users and their license info at C:\temp\UserLicenseReport.csv.

Complete script to export all Office 365 customers user and license info to a CSV via PowerShell

#Establish a PowerShell session with Office 365. You'll be prompted for your Delegated Admin credentials
Connect-MsolService
$customers = Get-MsolPartnerContract -All
Write-Host "Found $($customers.Count) customers for $((Get-MsolCompanyInformation).displayname)." -ForegroundColor DarkGreen
$CSVpath = "C:\Temp\UserLicenseReport.csv"
 
foreach ($customer in $customers) {
    Write-Host "Retrieving license info for $($customer.name)" -ForegroundColor Green
    $licensedUsers = Get-MsolUser -TenantId $customer.TenantId -All | Where-Object {$_.islicensed}
 
    foreach ($user in $licensedUsers) {
        Write-Host "$($user.displayname)" -ForegroundColor Yellow  
        $licenses = $user.Licenses
        $licenseArray = $licenses | foreach-Object {$_.AccountSkuId}
        $licenseString = $licenseArray -join ", "
        Write-Host "$($user.displayname) has $licenseString" -ForegroundColor Blue
        $licensedSharedMailboxProperties = [pscustomobject][ordered]@{
            CustomerName      = $customer.Name
            DisplayName       = $user.DisplayName
            Licenses          = $licenseString
            TenantId          = $customer.TenantId
            UserPrincipalName = $user.UserPrincipalName
        }
        $licensedSharedMailboxProperties | Export-CSV -Path $CSVpath -Append -NoTypeInformation    
    }
}

Was this article helpful?

Related Articles