Public/License/Get-CloudLicense.ps1
|
function Get-CloudLicense { 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 $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) $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) $datastring = ("`"" + $user.displayname + "`"" + "," + $user.userprincipalname + "," + $u2fSku.Item($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 { } } |