Link to home
Create AccountLog in
Avatar of K B
K BFlag for United States of America

asked on

Office 365 Granular License report - Export to CSV

I am looking for a script that shows (for each user) their license type and each of the services that they have been assigned.
Such as
Firstname Lastname, Office 365 Education E1 for Students, Lync Online ‎(Plan 2)‎, SharePoint Plan 1 for EDU
ASKER CERTIFIED SOLUTION
Avatar of Vasil Michev (MVP)
Vasil Michev (MVP)
Flag of Bulgaria image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of K B

ASKER

Vasil,

As always thank you so much!

I also used this script from Alan Byrne at https://gallery.technet.microsoft.com/scriptcenter/Export-a-Licence-b200ca2a#content 
I modified it with the suggestions from Nuno Alexandre at https://gallery.technet.microsoft.com/scriptcenter/Export-a-Licence-b200ca2a/view/Discussions#content
I also added more license SKUs and and Service Plans that fall under those License SKUs.

# Define Hashtables for lookup 
$Sku = @{ 
    "DESKLESSPACK" = "Office 365 (Plan K1)" 
    "DESKLESSWOFFPACK" = "Office 365 (Plan K2)" 
    "LITEPACK" = "Office 365 (Plan P1)" 
    "EXCHANGESTANDARD" = "Office 365 Exchange Online Only" 
    "STANDARDPACK" = "Office 365 (Plan E1)" 
    "STANDARDWOFFPACK" = "Office 365 (Plan E2)" 
    "ENTERPRISEPACK" = "Office 365 (Plan E3)" 
    "ENTERPRISEPACKLRG" = "Office 365 (Plan E3)" 
    "ENTERPRISEWITHSCAL" = "Office 365 (Plan E4)" 
    "STANDARDPACK_STUDENT" = "Office 365 (Plan A1) for Students" 
    "STANDARDWOFFPACKPACK_STUDENT" = "Office 365 (Plan A2) for Students" 
    "ENTERPRISEPACK_STUDENT" = "Office 365 (Plan A3) for Students" 
    "ENTERPRISEWITHSCAL_STUDENT" = "Office 365 (Plan A4) for Students" 
    "STANDARDPACK_FACULTY" = "STANDARDPACK_FACULTY" 
    "STANDARDWOFFPACKPACK_FACULTY" = "STANDARDWOFFPACKPACK_FACULTY" 
    "ENTERPRISEPACK_FACULTY" = "ENTERPRISEPACK_FACULTY" 
    "ENTERPRISEWITHSCAL_FACULTY" = "ENTERPRISEWITHSCAL_FACULTY" 
    "ENTERPRISEPACK_B_PILOT" = "ENTERPRISEPACK_B_PILOT" 
    "STANDARD_B_PILOT" = "STANDARD_B_PILOT"
    "STANDARDWOFFPACK_STUDENT" = "STANDARDWOFFPACK_STUDENT"
    "STANDARDWOFFPACK_IW_STUDENT" = "STANDARDWOFFPACK_IW_STUDENT"
    "EXCHANGEENTERPRISE_FACULTY" = "EXCHANGEENTERPRISE_FACULTY"
    "STANDARDWOFFPACK_IW_FACULTY" = "STANDARDWOFFPACK_IW_FACULTY"
    "POWER_BI_STANDARD" = "POWER_BI_STANDARD"
    "IT_ACADEMY_AD" = "IT_ACADEMY_AD"
    "OFFICESUBSCRIPTION_FACULTY" = "OFFICESUBSCRIPTION_FACULTY" 
    } 
         
# The Output will be written to this file in the current working directory 
$LogFile = ($(get-date -Format yyyy-MM-dd_HH-mm-ss) + "-licenses.csv")
 

# Get a list of all licences that exist within the tenant 
$licensetype = Get-MsolAccountSku | Where {$_.ConsumedUnits -ge 1} 
 
# Loop through all licence types found in the tenant 
foreach ($license in $licensetype)  
{     
    # Build and write the Header for the CSV file 
    $headerstring = "DisplayName,UserPrincipalName,AccountSku" 
     
    foreach ($row in $($license.ServiceStatus))  
    { 
         
        # Build header string 
        switch -wildcard ($($row.ServicePlan.servicename)) 
        { 
            "EXC*" { $thisLicence = "Exchange Online" } 
            "MCO*" { $thisLicence = "Lync Online" } 
            "LYN*" { $thisLicence = "Lync Online" } 
            "OFF*" { $thisLicence = "Office Profesional Plus" } 
            "SHA*" { $thisLicence = "Sharepoint Online" } 
            "*WAC*" { $thisLicence = "Office Web Apps" } 
            "WAC*" { $thisLicence = "Office Web Apps" }
            "YAM*" { $thisLicence = "Yammer" }
            "RMS*" { $thisLicence = "Azure AD Rights Mgmt" }
            default { $thisLicence = $row.ServicePlan.servicename } 
        } 
         
        $headerstring = ($headerstring + "," + $thisLicence) 
    } 
     
    Out-File -FilePath $LogFile -InputObject $headerstring -Encoding UTF8 -append 
     
    write-host ("Gathering users with the following subscription: " + $license.accountskuid) 
 
    $users = Get-MsolUser -all | where {$_.isLicensed -eq "True"}

    $skuid = $license.accountskuid

	foreach ($user in $users) {
		
		$userLicences = $user.Licenses

        for ($i=0; $i -lt $($userLicences.count); $i++)
        {
            $userSkuId = $userLicences[$i].AccountSkuId
            #write-host "$($user.DisplayName) , $userSkuId"

            if ($userSkuId -eq $skuid)
            {
                write-host ("Processing " + $user.displayname)
                $datastring = ($user.displayname + "," + $user.userprincipalname + "," + $Sku.Item($userLicences[$i].AccountSku.SkuPartNumber))

		        foreach ($row in $($userLicences[$i].servicestatus)) {
			
			        # Build data string
			        $datastring = ($datastring + "," + $($row.provisioningstatus))
			    }
		
		        Out-File -FilePath $LogFile -InputObject $datastring -Encoding UTF8 -append
            }
        }
	}

         
    }              
 
write-host ("Script Completed.  Results available in " + $LogFile)

Open in new window