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
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.

Vasil Michev (MVP)Commented:
Problem is, there are now like a 30 different licenses/services :)

Here's an example, you can add additional licenses as needed:

$all = Get-MsolUser -All
$licensed =  $all |? {$_.isLicensed -eq $true}

$arrMsolUserData = @()

foreach ($l in $licensed) {

 $objUser = New-Object PSObject
 Add-Member -InputObject $objUser -MemberType NoteProperty -Name "UserPrincipalName" -Value $l.UserPrincipalName
 Add-Member -InputObject $objUser -MemberType NoteProperty -Name "Name" -Value $l.DisplayName

 #Lync License
 $index = $l.licenses.servicestatus.serviceplan.servicename.IndexOf("MCOSTANDARD")
 if ($index -ne -1) { 
     Add-Member -InputObject $objUser -MemberType NoteProperty -Name "Lync License" -Value $l.licenses.servicestatus[$index].ProvisioningStatus
 }
 

 #SharePoint License
 $index = $l.licenses.servicestatus.serviceplan.servicename.IndexOf("SHAREPOINTENTERPRISE")
 if ($index -eq -1) { $index = $l.licenses.servicestatus.serviceplan.servicename.IndexOf("SHAREPOINTSTANDARD") }
 if ($index -ne -1) { 
    Add-Member -InputObject $objUser -MemberType NoteProperty -Name "SharePoint License" -Value $l.licenses.servicestatus[$index].serviceplan.servicename
    Add-Member -InputObject $objUser -MemberType NoteProperty -Name "SharePoint Status" -Value $l.licenses.servicestatus[$index].ProvisioningStatus
 }



 #Exchange License
 $index = $l.licenses.servicestatus.serviceplan.servicename.IndexOf("EXCHANGE_S_ENTERPRISE")
 if ($index -eq -1) { $index = $l.licenses.servicestatus.serviceplan.servicename.IndexOf("EXCHANGE_S_STANDARD") }
 if ($index -ne -1) {
    Add-Member -InputObject $objUser -MemberType NoteProperty -Name "Exchange License" -Value $l.licenses.servicestatus[$index].serviceplan.servicename
    Add-Member -InputObject $objUser -MemberType NoteProperty -Name "Exchange Status" -Value $l.licenses.servicestatus[$index].ProvisioningStatus
 }

 $arrMsolUserData += $objUser
}

$arrMsolUserData | select UserPrincipalName,Name,"Lync License","SharePoint License","SharePoint Status","Exchange License","Exchange Status" #| export-csv  ($(get-date -Format yyyy-MM-dd_HH-mm-ss) + "-licenses.csv") -Nti

Open in new window


List of all SKUs/services you can find here: http://blogs.technet.com/b/treycarlee/archive/2013/11/01/list-of-powershell-licensing-sku-s-for-office-365.aspx
0

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:
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

1
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.