Public/License/Get-CloudLicense.ps1

function Get-CloudLicense { 
    <#
    .SYNOPSIS
    Report on Office 365 Licenses
    Credit: Original script developed by Alan Byrne.
     
    .DESCRIPTION
    Report on Office 365 License SKUs and Options assigned to each user
     
    .PARAMETER DomainFilter
    Reports on licenses for users with an email address with the domain name(s) passed at the pipeline. One report per domain.
    Without DomainFilter, reports on all licensed users.
     
    .EXAMPLE
    Get-CloudLicense
     
    .EXAMPLE
    'contoso.com' | Get-CloudLicense
     
    .EXAMPLE
    'contoso.com','fabrikam.com' | Get-CloudLicense
     
    #>


    Param (
        [Parameter(ValueFromPipeline = $true, Mandatory = $false)]
        [string] $DomainFilter

    )

    Begin {

        <#
#############################################################
Below is Excel Macro to separate each SKU into its own tab
ALT + F11 / Right-Click Sheet1 > Insert > Module /
Copy & Paste text below (from Option Explicit to End Sub) /
ALT + Q / ALT + F8 / Click Run (large tenants take some time)
#############################################################
 
 
    Option Explicit
Sub createsheetabs()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, lrow As Long, wk As Worksheet
    Dim ws1 As Worksheet
    Dim i As Long
    Dim rwIn As Long, rwOut As Long
    Dim EmptyCount As Integer
     
    Set ws = ActiveSheet
    For Each wk In ActiveWorkbook.Worksheets
        Application.DisplayAlerts = False
        If wk.Name <> ws.Name Then
            wk.Delete
        End If
        Application.DisplayAlerts = True
    Next wk
    lrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row
    rwIn = 1
    While rwIn < lrow
        If InStr(1, ws.Range("C" & rwIn), "AccountSku", vbTextCompare) > 0 And InStr(1, ws.Range("C" & rwIn + 1), "AccountSku", vbTextCompare) = 0 Then
            'Make sheet
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws1 = ActiveSheet
            If ws.Range("C" & rwIn + 1) = "" Then
                EmptyCount = EmptyCount + 1
                ws1.Name = "Empty" & EmptyCount
            Else
                ws1.Name = Left(ws.Range("C" & rwIn + 1), 25)
            End If
            ws.Range("C" & rwIn).EntireRow.Copy ws1.Range("a1")
            rwOut = 1
            i = 0
            'Add data
            While InStr(1, ws.Range("C" & rwIn + 1), "AccountSku", vbTextCompare) = 0 And rwIn < lrow
                rwIn = rwIn + 1
                rwOut = rwOut + 1
                ws.Range("C" & rwIn).EntireRow.Copy ws1.Range("A" & rwOut)
                i = i + 1
            Wend
            ws1.Cells.EntireColumn.AutoFit
            ws1.Name = ws1.Name & "-" & i
        End If
        rwIn = rwIn + 1
    Wend
    Application.ScreenUpdating = True
End Sub
 
#>


    }
    Process {
        # Define Hashtables for lookup
        $u2fSku = @{ 
            "AX_ENTERPRISE_USER"                 = "AX ENTERPRISE USER";
            "AX_SELF-SERVE_USER"                 = "AX SELF-SERVE USER";
            "AX_SANDBOX_INSTANCE_TIER2"          = "AX_SANDBOX_INSTANCE_TIER2";
            "AX_TASK_USER"                       = "AX_TASK_USER";
            "AAD_PREMIUM"                        = "Azure Active Directory Premium P1";
            "RMS_S_ENTERPRISE"                   = "Azure Active Directory Rights Management";
            "RIGHTSMANAGEMENT_ADHOC"             = "Azure Rights Management Services Ad-hoc";
            "CRMPLAN2"                           = "Dynamics CRM Online Plan 2";
            "EMS"                                = "Enterprise Mobility + Security E3";
            "EMSPREMIUM"                         = "Enterprise Mobility + Security E5";
            "ENTERPRISEPACK_B_PILOT"             = "ENTERPRISEPACK_B_PILOT";
            "EXCHANGEENTERPRISE_FACULTY"         = "Exch Online Plan 2 for Faculty";
            "EXCHANGE_L_STANDARD"                = "Exchange Online (Plan 1)";
            "ATP_ENTERPRISE_FACULTY"             = "Exchange Online Advanced Threat Protection";
            "ATP_ENTERPRISE"                     = "Exchange Online ATP";
            "EXCHANGESTANDARD"                   = "Exchange Online Plan 1";
            "EXCHANGE_S_ENTERPRISE"              = "Exchange Online Plan 2 S";
            "EXCHANGEENTERPRISE"                 = "Exchange Online Plan 2";
            "RIGHTSMANAGEMENT_STANDARD_FACULTY"  = "Information Rights Management for Faculty";
            "RIGHTSMANAGEMENT_STANDARD_STUDENT"  = "Information Rights Management for Students";
            "INTUNE_A_VL"                        = "Intune (Volume License)";
            "MCOLITE"                            = "Lync Online (Plan 1)";
            "CRMSTORAGE"                         = "Microsoft Dynamics CRM Online Additional Storage";
            "FLOW_FREE"                          = "Microsoft Flow Free";
            "IT_ACADEMY_AD"                      = "Microsoft Imagine Academy";
            "POWERAPPS_INDIVIDUAL_USER"          = "Microsoft PowerApps and Logic flows";
            "STREAM"                             = "Microsoft Stream";
            "MICROSOFT_BUSINESS_CENTER"          = "MICROSOFT_BUSINESS_CENTER";
            "MEE_FACULTY"                        = "Minecraft Education Edition Faculty";
            "MEE_STUDENT"                        = "Minecraft Education Edition Student";
            "STANDARDWOFFPACK_STUDENT"           = "O365 Education E1 for Students";
            "STANDARDWOFFPACK_IW_FACULTY"        = "O365 Education for Faculty";
            "STANDARDWOFFPACK_IW_STUDENT"        = "O365 Education for Students";
            "STANDARDPACK_STUDENT"               = "Office 365 (Plan A1) for Students";
            "ENTERPRISEPACKLRG"                  = "Office 365 (Plan E3)";
            "EQUIVIO_ANALYTICS_FACULTY"          = "Office 365 Advanced Compliance for faculty";
            "STANDARDWOFFPACK_FACULTY"           = "Office 365 Education E1 for Faculty";
            "ENTERPRISEWITHSCAL_FACULTY"         = "Office 365 Education E4 for Faculty";
            "ENTERPRISEWITHSCAL_STUDENT"         = "Office 365 Education E4 for Students";
            "STANDARDPACK"                       = "Office 365 Enterprise E1";
            "STANDARDWOFFPACK"                   = "Office 365 Enterprise E2";
            "ENTERPRISEPACKWITHOUTPROPLUS"       = "Office 365 Enterprise E3 without ProPlus Add-on";
            "ENTERPRISEPACK"                     = "Office 365 Enterprise E3";
            "ENTERPRISEWITHSCAL"                 = "Office 365 Enterprise E4";
            "ENTERPRISEPREMIUM"                  = "Office 365 Enterprise E5";
            "DESKLESSPACK_YAMMER"                = "Office 365 Enterprise K1 with Yammer";
            "DESKLESSPACK"                       = "Office 365 Enterprise K1 without Yammer";
            "DESKLESSWOFFPACK"                   = "Office 365 Enterprise K2";
            "MIDSIZEPACK"                        = "Office 365 Midsize Business";
            "STANDARDWOFFPACKPACK_FACULTY"       = "Office 365 Plan A2 for Faculty";
            "STANDARDWOFFPACKPACK_STUDENT"       = "Office 365 Plan A2 for Students";
            "ENTERPRISEPACK_FACULTY"             = "Office 365 Plan A3 for Faculty";
            "ENTERPRISEPACK_STUDENT"             = "Office 365 Plan A3 for Students";
            "OFFICESUBSCRIPTION_FACULTY"         = "Office 365 ProPlus for Faculty";
            "LITEPACK_P2"                        = "Office 365 Small Business Premium";
            "WACSHAREPOINTSTD"                   = "Office Online STD";
            "SHAREPOINTWAC"                      = "Office Online";
            "OFFICESUBSCRIPTION_STUDENT"         = "Office ProPlus Student Benefit";
            "OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus";
            "POWER_BI_INDIVIDUAL_USER"           = "Power BI for Office 365 Individual";
            "POWER_BI_STANDALONE"                = "Power BI for Office 365 Standalone";
            "POWER_BI_STANDARD"                  = "Power BI for Office 365 Standard";
            "POWER_BI_PRO"                       = "POWER_BI_PRO";
            "PROJECTESSENTIALS"                  = "Project Lite";
            "PROJECTONLINE_PLAN_1_FACULTY"       = "Project Online for Faculty Plan 1";
            "PROJECTONLINE_PLAN_2_FACULTY"       = "Project Online for Faculty Plan 2";
            "PROJECTONLINE_PLAN_1_STUDENT"       = "Project Online for Students Plan 1";
            "PROJECTONLINE_PLAN_2_STUDENT"       = "Project Online for Students Plan 2";
            "PROJECTPREMIUM"                     = "Project Online Premium";
            "PROJECTPROFESSIONAL"                = "Project Online Professional";
            "PROJECTONLINE_PLAN_1"               = "Project Online with Project for Office 365";
            "PROJECTCLIENT"                      = "Project Pro for Office 365";
            "PROJECT_MADEIRA_PREVIEW_IW_SKU"     = "PROJECT_MADEIRA_PREVIEW_IW";
            "SPE_E3"                             = "Secure Productive Enterprise E3";
            "SHAREPOINTLITE"                     = "SharePoint Online (Plan 1) Lite";
            "SHAREPOINTENTERPRISE_MIDMARKET"     = "SharePoint Online (Plan 1) MidMarket";
            "SHAREPOINTENTERPRISE"               = "SharePoint Online (Plan 2)";
            "SHAREPOINTSTANDARD"                 = "SharePoint Online Plan 1";
            "STANDARD_B_PILOT"                   = "STANDARD_B_PILOT";
            "STANDARDPACK_FACULTY"               = "STANDARDPACK_FACULTY";
            "VISIOCLIENT"                        = "Visio Pro for Office 365";
            "YAMMER_ENTERPRISE"                  = "Yammer Enterprise";
            "YAMMER_MIDSIZE"                     = "Yammer Midsize"
        } 

        # The Output will be written to this file in the current working directory
        if ($DomainFilter) {
            $LogFile = ($(get-date -Format yyyy-MM-dd_HH-mm-ss) + "-" + $DomainFilter + "-licenses.csv")
        }
        else {
            $LogFile = ($(get-date -Format yyyy-MM-dd_HH-mm-ss) + "-licenses.csv")
        }

        # Get a list of all Licenses that exist within the tenant
        $licensetype = Get-MsolAccountSku # | Where {$_.SkuPartNumber -eq "STANDARDWOFFPACK_IW_STUDENT"}
        # $licensetype = Get-MsolAccountSku | Where {$_.AccountSkuId -eq "TENANT:ENTERPRISEPACK"}

        # Loop through all License 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)) { 
                    "AAD_PREMIUM" { $thisLicense = "Azure Active Directory Premium Plan 1"}
                    "AAD_PREMIUM_P2" { $thisLicense = "Azure Active Directory Premium P2"}
                    "IT_ACADEMY_AD" { $thisLicense = "Microsoft Imagine Academy"}
                    "ADALLOM_S_O365" { $thisLicense = "Office 365 Advanced Security Management"}
                    "ADALLOM_S_STANDALONE" { $thisLicense = "Microsoft Cloud App Security"}
                    "ATP_ENTERPRISE" { $thisLicense = "Exchange Online Advanced Threat Protection"}
                    "BI_AZURE_P0" { $thisLicense = "Power BI (free)"}
                    "BI_AZURE_P1" { $thisLicense = "Power BI Reporting and Analytics"}
                    "BI_AZURE_P2" { $thisLicense = "Power BI Pro"}
                    "CRMIUR" { $thisLicense = "CRM for Partners"}
                    "CRMSTANDARD" { $thisLicense = "CRM Online"}
                    "CRMSTORAGE" { $thisLicense = "Microsoft Dynamics CRM Online Additional Storage"}
                    "CRMTESTINSTANCE" { $thisLicense = "CRM Test Instance"}
                    "Deskless" { $thisLicense = "Microsoft StaffHub"}
                    "DESKLESSPACK_GOV" { $thisLicense = "Microsoft Office 365 (Plan K1) for Government"}
                    "DESKLESSWOFFPACK_GOV" { $thisLicense = "Microsoft Office 365 (Plan K2) for Government"}
                    "ENTERPRISEPACK_GOV" { $thisLicense = "Microsoft Office 365 (Plan G3) for Government"}
                    "ENTERPRISEWITHSCAL_GOV" { $thisLicense = "Microsoft Office 365 (Plan G4) for Government"}
                    "EOP_ENTERPRISE" { $thisLicense = "Exchange Online Protection"}
                    "EOP_ENTERPRISE_FACULTY" { $thisLicense = "Exchange Online Protection for Faculty"}
                    "EQUIVIO_ANALYTICS" { $thisLicense = "Office 365 Advanced eDiscovery"}
                    "EXCHANGE_ANALYTICS" { $thisLicense = "Microsoft MyAnalytics"}
                    "EXCHANGE_S_ARCHIVE_ADDON_GOV" { $thisLicense = "Exchange Online Archiving Govt"}
                    "EXCHANGE_S_DESKLESS" { $thisLicense = "Exchange Online Kiosk"}
                    "EXCHANGE_S_DESKLESS_GOV" { $thisLicense = "Exchange Kiosk"}
                    "EXCHANGE_S_ENTERPRISE" { $thisLicense = "Exchange Online (Plan 2) Ent"}
                    "EXCHANGE_S_ENTERPRISE_GOV" { $thisLicense = "Exchange Plan 2G"}
                    "EXCHANGE_S_FOUNDATION" { $thisLicense = "Exchange Foundation for certain SKUs"}
                    "EXCHANGE_S_STANDARD" { $thisLicense = "Exchange Online (Plan 2)"}
                    "EXCHANGE_S_STANDARD_MIDMARKET" { $thisLicense = "Exchange Online (Plan 1)"}
                    "EXCHANGEARCHIVE" { $thisLicense = "Exchange Online Archiving"}
                    "EXCHANGEENTERPRISE_GOV" { $thisLicense = "Microsoft Office 365 Exchange Online (Plan 2) only for Government"}
                    "EXCHANGESTANDARD_GOV" { $thisLicense = "Microsoft Office 365 Exchange Online (Plan 1) only for Government"}
                    "EXCHANGESTANDARD_STUDENT" { $thisLicense = "Exchange Online (Plan 1) for Students"}
                    "EXCHANGETELCO" { $thisLicense = "Exchange Online POP"}
                    "FLOW_O365_P2" { $thisLicense = "Flow"}
                    "FLOW_O365_P3" { $thisLicense = "Flow for Office 365"}
                    "FORMS_PLAN_E3" { $thisLicense = "Microsoft Forms (Plan E3)"}
                    "FORMS_PLAN_E5" { $thisLicense = "Microsoft Forms (Plan E5)"}
                    "INTUNE_A" { $thisLicense = "Intune for Office 365"}
                    "INTUNE_O365" { $thisLicense = "Mobile Device Management for Office 365"}
                    "LITEPACK" { $thisLicense = "Office 365 (Plan P1)"}
                    "LOCKBOX_ENTERPRISE" { $thisLicense = "Customer Lockbox"}
                    "MCOEV" { $thisLicense = "Skype for Business Cloud PBX"}
                    "MCOMEETADV" { $thisLicense = "Skype for Business PSTN Conferencing"}
                    "MCOSTANDARD" { $thisLicense = "Skype for Business Online (Plan 2)"}
                    "MCOSTANDARD_GOV" { $thisLicense = "Lync Plan 2G"}
                    "MCOSTANDARD_MIDMARKET" { $thisLicense = "Lync Online (Plan 1)"}
                    "MCVOICECONF" { $thisLicense = "Lync Online (Plan 3)"}
                    "MDM_SALES_COLLABORATION" { $thisLicense = "Microsoft Dynamics Marketing Sales Collaboration"}
                    "MFA_PREMIUM" { $thisLicense = "Azure Multi-Factor Authentication"}
                    "MICROSOFT_BUSINESS_CENTER" { $thisLicense = "Microsoft Business Center"}
                    "MINECRAFT_EDUCATION_EDITION" { $thisLicense = "Minecraft Education Edition Faculty"}
                    "NBPROFESSIONALFORCRM" { $thisLicense = "Microsoft Social Listening Professional"}
                    "OFFICE_FORMS_PLAN_2" { $thisLicense = "Microsoft Forms (Plan 2)"}
                    "OFFICESUBSCRIPTION" { $thisLicense = "Office 365 ProPlus"}
                    "OFFICESUBSCRIPTION_GOV" { $thisLicense = "Office ProPlus"}
                    "OFFICESUBSCRIPTION_STUDENT" { $thisLicense = "Office ProPlus Student Benefit"}
                    "ONEDRIVESTANDARD" { $thisLicense = "OneDrive"}
                    "POWERAPPS_O365_P2" { $thisLicense = "PowerApps"}
                    "POWERAPPS_O365_P3" { $thisLicense = "PowerApps for Office 365"}
                    "PROJECT_CLIENT_SUBSCRIPTION" { $thisLicense = "Project Pro for Office 365"}
                    "PROJECT_ESSENTIALS" { $thisLicense = "Project Lite"}
                    "PROJECTONLINE_PLAN_1" { $thisLicense = "Project Online (Plan 1)"}
                    "PROJECTONLINE_PLAN_2" { $thisLicense = "Project Online (Plan 2)"}
                    "PROJECTWORKMANAGEMENT" { $thisLicense = "Microsoft Planner"}
                    "RMS_S_ENTERPRISE" { $thisLicense = "Azure Rights Management"}
                    "RMS_S_ENTERPRISE_GOV" { $thisLicense = "Windows Azure Active Directory Rights Management"}
                    "RMS_S_PREMIUM" { $thisLicense = "Azure Information Protection Plan 1"}
                    "RMS_S_PREMIUM2" { $thisLicense = "Azure Information Protection Premium P2"}
                    "SCHOOL_DATA_SYNC_P1" { $thisLicense = "School Data Sync (Plan 1)"}
                    "SHAREPOINT_PROJECT" { $thisLicense = "SharePoint Online (Plan 2) Project"}
                    "SHAREPOINT_PROJECT_EDU" { $thisLicense = "Project Online Service for Education"}
                    "SHAREPOINTDESKLESS" { $thisLicense = "SharePoint Online Kiosk"}
                    "SHAREPOINTDESKLESS_GOV" { $thisLicense = "SharePoint Online Kiosk Gov"}
                    "SHAREPOINTENTERPRISE" { $thisLicense = "SharePoint Online (Plan 2)"}
                    "SHAREPOINTENTERPRISE_EDU" { $thisLicense = "SharePoint Plan 2 for EDU"}
                    "SHAREPOINTENTERPRISE_GOV" { $thisLicense = "SharePoint Plan 2G"}
                    "SHAREPOINTENTERPRISE_MIDMARKET" { $thisLicense = "SharePoint Online (Plan 1)"}
                    "SHAREPOINTPARTNER" { $thisLicense = "SharePoint Online Partner Access"}
                    "SHAREPOINTSTANDARD_EDU" { $thisLicense = "SharePoint Plan 1 for EDU"}
                    "SHAREPOINTSTORAGE" { $thisLicense = "SharePoint Online Storage"}
                    "SHAREPOINTWAC" { $thisLicense = "Office Online"}
                    "SHAREPOINTWAC_EDU" { $thisLicense = "Office Online for Education"}
                    "SHAREPOINTWAC_GOV" { $thisLicense = "Office Online for Government"}
                    "SQL_IS_SSIM" { $thisLicense = "Power BI Information Services"}
                    "STANDARDPACK" { $thisLicense = "Microsoft Office 365 (Plan E1)"}
                    "STANDARDPACK_FACULTY" { $thisLicense = "Microsoft Office 365 (Plan A1) for Faculty"}
                    "STANDARDPACK_GOV" { $thisLicense = "Microsoft Office 365 (Plan G1) for Government"}
                    "STANDARDPACK_STUDENT" { $thisLicense = "Microsoft Office 365 (Plan A1) for Students"}
                    "STANDARDWOFFPACK" { $thisLicense = "Microsoft Office 365 (Plan E2)"}
                    "STANDARDWOFFPACK_FACULTY" { $thisLicense = "Office 365 Education E1 for Faculty"}
                    "STANDARDWOFFPACK_GOV" { $thisLicense = "Microsoft Office 365 (Plan G2) for Government"}
                    "STANDARDWOFFPACK_IW_FACULTY" { $thisLicense = "Office 365 Education for Faculty"}
                    "STANDARDWOFFPACK_IW_STUDENT" { $thisLicense = "Office 365 Education for Students"}
                    "STANDARDWOFFPACK_STUDENT" { $thisLicense = "Microsoft Office 365 (Plan A2) for Students"}
                    "STREAM_O365_E3" { $thisLicense = "Microsoft Stream for O365 E3 SKU"}
                    "STREAM_O365_E5" { $thisLicense = "Microsoft Stream for O365 E5 SKU"}
                    "SWAY" { $thisLicense = "Sway"}
                    "TEAMS1" { $thisLicense = "Microsoft Teams"}
                    "THREAT_INTELLIGENCE" { $thisLicense = "Office 365 Threat Intelligence"}
                    "VISIO_CLIENT_SUBSCRIPTION" { $thisLicense = "Visio Pro for Office 365 Subscription"}
                    "VISIOCLIENT" { $thisLicense = "Visio Pro for Office 365"}
                    "WACONEDRIVESTANDARD" { $thisLicense = "OneDrive Pack"}
                    "WIN10_PRO_ENT_SUB" { $thisLicense = "Windows 10 Enterprise E3"}
                    "YAMMER_EDU" { $thisLicense = "Yammer for Academic"}
                    "YAMMER_ENTERPRISE" { $thisLicense = "Yammer Enterprise"}
                    "YAMMER_MIDSIZE" { $thisLicense = "Yammer"}

                    default { $thisLicense = $row.ServicePlan.servicename } 
                } 

                $headerstring = ($headerstring + "," + $thisLicense) 
            } 
            Out-File -FilePath $LogFile -InputObject $headerstring -Encoding UTF8 -append 
            write-host ("Gathering users with the following subscription: " + $license.accountskuid) 
            If ($DomainFilter) {
                $users = Get-MsolUser -Domain $DomainFilter | where {$_.isLicensed -eq "True"}
            }
            else {
                $users = Get-MsolUser -all | where {$_.isLicensed -eq "True"}
            }
            $skuid = $license.accountskuid
            foreach ($user in $users) {
                $userLicenses = $user.Licenses
                for ($i = 0; $i -lt $($userLicenses.count); $i++) {
                    $userSkuId = $userLicenses[$i].AccountSkuId

                    if ($userSkuId -eq $skuid) {
                        write-host ("Processing " + $user.displayname)
                        if ($u2fSku.Item($userLicenses[$i].AccountSku.SkuPartNumber)) {
                            $datastring = ("`"" + $user.displayname + "`"" + "," + $user.userprincipalname + "," + $u2fSku.Item($userLicenses[$i].AccountSku.SkuPartNumber))
                        }
                        else {
                            $datastring = ("`"" + $user.displayname + "`"" + "," + $user.userprincipalname + "," + ($userLicenses[$i].AccountSku.SkuPartNumber))
                        }

                        foreach ($row in $($userLicenses[$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)
    }
    End {

    }
}