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 8
K BAsked:
Who is Participating?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.