O365 users, asset list

[Archie]

Hi,

   We have a large amount of our staff working remotely.  I am looking for a report that can tell me active O365 users with a license to buisness standard, E1, E3, etc  and their assets.

I know that I have assets that lansweeper doesn't know about and want to get LSAgent installed.

I am doing this by hand right now and I think that Lansweeper should be able to give a report to help.

Thanks

Joe

[Archie]

I think that I have a report that finally like. took a bit to create this

Select Top 1000000 'user.png' As Icon,
  tblO365User.DisplayName,
  tblO365User.UserPrincipalName,
  tblADusers.Username,
  tblADusers.Userdomain,
  tblO365User.Department,
  tblO365User.Mail,
  tblO365Organization.DisplayName As Organization,
  tblO365Sku.DisplayName As LicensePlan,
  tblAssets.AssetName,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssets.Processor,
  Ceiling(tblAssets.Memory / 1024) As [RAM in GB],
  tsysOS.OSname
From tblO365User
  Inner Join tblO365Organization On tblO365User.OrganizationId =
      tblO365Organization.OrganizationId
  Left Outer Join tblADusers On tblADusers.UPN = tblO365User.UserPrincipalName
  Left Join tblO365License On tblO365Organization.OrganizationId =
      tblO365License.OrganizationId
  Inner Join tblO365AssignedLicense On tblO365License.LicenseId =
      tblO365AssignedLicense.LicenseId And tblO365AssignedLicense.UserId =
      tblO365User.UserId
  Left Join tblO365Sku On tblO365Sku.Name = tblO365License.SkuPartNumber
  Left Join tblAssets On tblAssets.username = tblADusers.username
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblO365Sku.DisplayName = 'Office 365 Business Premium' Or
    tblO365Sku.DisplayName = 'Enterprise Plan E3' Or tblO365Sku.DisplayName =
    'Enterprise Plan E1')
Order By tblO365User.DisplayName

This report tells me what O365 licensed users I have in lansweeper, gives me the asset name, model, processor, etc

now I am trying to get the drive capacity and free space figured out.  

[rader]

Sadly I don't have any Microsoft 365 licenses out there, so my reports show 0 assets.

Good luck with your search.

[Archie]

Hi Rader,

   Thanks for the response.  This is close where it provides the license names with the amount of consumed licenses.

I am looking for the "Users With License: O365_BUSINESS_PREMIUM (130)", that I can access from the "Microsoft 365: Licenses" report.  I can't edit it though.

So, I am looking to create a report to find what computers are associated to the Users with a business premium license.

I am trying to find computers that I might not have in my asset lists.

Thanks for your assistance

[rader]

I think that this report was in an article. This may help you narrow down your search.

MS 365 License Report

Select Top 1000000 'license.png' As Icon,
  tblO365License.LicenseId,
  Case
    When tblO365License.SkuPartNumber = 'ADV_COMMS' Then
      'Advanced Communications'
    When tblO365License.SkuPartNumber = 'CDSAICAPACITY' Then
      'AI Builder Capacity add-on'
    When tblO365License.SkuPartNumber = 'SPZA_IW' Then 'APP CONNECT IW'
    When tblO365License.SkuPartNumber = 'MCOMEETADV' Then
      'Microsoft 365 Audio Conferencing'
    When tblO365License.SkuPartNumber = 'AAD_BASIC' Then
      'AZURE ACTIVE DIRECTORY BASIC'
    When tblO365License.SkuPartNumber = 'AAD_PREMIUM' Then
      'AZURE ACTIVE DIRECTORY PREMIUM P1'
    When tblO365License.SkuPartNumber = 'AAD_PREMIUM_P2' Then
      'AZURE ACTIVE DIRECTORY PREMIUM P2'
    When tblO365License.SkuPartNumber = 'RIGHTSMANAGEMENT' Then
      'AZURE INFORMATION PROTECTION PLAN 1'
    When tblO365License.SkuPartNumber = 'SMB_APPS' Then 'Business Apps (free)'
    When tblO365License.SkuPartNumber = 'MCOCAP' Then 'COMMON AREA PHONE'
    When tblO365License.SkuPartNumber = 'MCOCAP_GOV' Then
      'Common Area Phone for GCC'
    When tblO365License.SkuPartNumber = 'CDS_DB_CAPACITY' Then
      'Common Data Service Database Capacity'
    When tblO365License.SkuPartNumber = 'CDS_LOG_CAPACITY' Then
      'Common Data Service Log Capacity'
    When tblO365License.SkuPartNumber = 'MCOPSTNC' Then 'COMMUNICATIONS CREDITS'
    When tblO365License.SkuPartNumber = 'CRMSTORAGE' Then
      'Dynamics 365 - Additional Database Storage (Qualified Offer)'
    When tblO365License.SkuPartNumber = 'CRMINSTANCE' Then
      'Dynamics 365 - Additional Production Instance (Qualified Offer)'
    When tblO365License.SkuPartNumber = 'CRMTESTINSTANCE' Then
      'Dynamics 365 - Additional Non-Production Instance (Qualified Offer)'
    When tblO365License.SkuPartNumber = 'DYN365_ASSETMANAGEMENT' Then
      'Dynamics 365 Asset Management Addl Assets'
    When tblO365License.SkuPartNumber = 'DYN365_BUSCENTRAL_ADD_ENV_ADDON' Then
      'Dynamics 365 Business Central Additional Environment Addon'
    When tblO365License.SkuPartNumber = 'DYN365_BUSCENTRAL_DB_CAPACITY' Then
      'Dynamics 365 Business Central Database Capacity'
    When tblO365License.SkuPartNumber = 'DYN365_BUSCENTRAL_ESSENTIAL' Then
      'Dynamics 365 Business Central Essentials'
    When tblO365License.SkuPartNumber = 'DYN365_FINANCIALS_ACCOUNTANT_SKU' Then
      'Dynamics 365 Business Central External Accountant'
    When tblO365License.SkuPartNumber = 'PROJECT_MADEIRA_PREVIEW_IW_SKU' Then
      'Dynamics 365 Business Central for IWs'
    When tblO365License.SkuPartNumber = 'DYN365_BUSCENTRAL_PREMIUM' Then
      'Dynamics 365 Business Central Premium'
    When tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_PLAN1' Then
      'Dynamics 365 Customer Engagement Plan'
    When tblO365License.SkuPartNumber = 'DYN365_AI_SERVICE_INSIGHTS' Then
      'Dynamics 365 Customer Service Insights Trial'
    When tblO365License.SkuPartNumber = 'FORMS_PRO' Then
      'Dynamics 365 Customer Voice Trial'
    When tblO365License.SkuPartNumber = 'DYN365_CUSTOMER_SERVICE_PRO' Then
      'Dynamics 365 Customer Service Professional'
    When tblO365License.SkuPartNumber = 'Forms_Pro_AddOn' Then
      'Dynamics 365 Customer Voice Additional Responses'
    When tblO365License.SkuPartNumber = 'DYN365_CUSTOMER_VOICE_ADDON' Then
      'Dynamics 365 Customer Voice Additional Responses'
    When tblO365License.SkuPartNumber = 'Forms_Pro_USL' Then
      'Dynamics 365 Customer Voice USL'
    When tblO365License.SkuPartNumber = 'CRM_ONLINE_PORTAL' Then
      'Dynamics 365 Enterprise Edition - Additional Portal (Qualified Offer)'
    When tblO365License.SkuPartNumber = 'DYN365_FINANCE' Then
      'Dynamics 365 Finance'
    When tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_CUSTOMER_SERVICE'
    Then 'DYNAMICS 365 FOR CUSTOMER SERVICE ENTERPRISE EDITION'
    When tblO365License.SkuPartNumber = 'DYN365_FINANCIALS_BUSINESS_SKU' Then
      'DYNAMICS 365 FOR FINANCIALS BUSINESS EDITION'
    When
      tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_SALES_CUSTOMERSERVICE'
    Then 'DYNAMICS 365 FOR SALES AND CUSTOMER SERVICE ENTERPRISE EDITION'
    When tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_SALES' Then
      'DYNAMICS 365 FOR SALES ENTERPRISE EDITION'
    When tblO365License.SkuPartNumber = 'D365_SALES_PRO ' Then
      'Dynamics 365 Sales Professional'
    When tblO365License.SkuPartNumber = 'D365_SALES_PRO_ATTACH' Then
      'Dynamics 365 Sales Professional Attach to Qualifying Dynamics 365 Base Offer'
    When tblO365License.SkuPartNumber = 'DYN365_SCM' Then
      'DYNAMICS 365 FOR SUPPLY CHAIN MANAGEMENT'
    When tblO365License.SkuPartNumber = 'SKU_Dynamics_365_for_HCM_Trial' Then
      'Dynamics 365 for Talent'
    When tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_TEAM_MEMBERS' Then
      'DYNAMICS 365 FOR TEAM MEMBERS ENTERPRISE EDITION'
    When tblO365License.SkuPartNumber = 'GUIDES_USER' Then 'Dynamics 365 Guides'
    When tblO365License.SkuPartNumber = 'Dynamics_365_for_Operations_Devices'
    Then 'Dynamics 365 Operations - Device'
    When
      tblO365License.SkuPartNumber =
      'Dynamics_365_for_Operations_Sandbox_Tier2_SKU' Then
      'Dynamics 365 Operations - Sandbox Tier 2:Standard Acceptance Testing'
    When
      tblO365License.SkuPartNumber =
      'Dynamics_365_for_Operations_Sandbox_Tier4_SKU' Then
      'Dynamics 365 Operations - Sandbox Tier 4:Standard Performance Testing'
    When tblO365License.SkuPartNumber = 'DYN365_ENTERPRISE_P1_IW' Then
      'DYNAMICS 365 P1 TRIAL FOR INFORMATION WORKERS'
    When tblO365License.SkuPartNumber = 'MICROSOFT_REMOTE_ASSIST' Then
      'Dynamics 365 Remote Assist'
    When tblO365License.SkuPartNumber = 'MICROSOFT_REMOTE_ASSIST_HOLOLENS' Then
      'Dynamics 365 Remote Assist HoloLens'
    When tblO365License.SkuPartNumber = 'D365_SALES_ENT_ATTACH' Then
      'Dynamics 365 Sales Enterprise Attach to Qualifying Dynamics 365 Base Offer'
    When tblO365License.SkuPartNumber = 'DYNAMICS_365_ONBOARDING_SKU' Then
      'DYNAMICS 365 TALENT: ONBOARD'
    When tblO365License.SkuPartNumber = 'DYN365_TEAM_MEMBERS' Then
      'DYNAMICS 365 TEAM MEMBERS'
    When tblO365License.SkuPartNumber = 'Dynamics_365_for_Operations' Then
      'DYNAMICS 365 UNF OPS PLAN ENT EDITION'
    When tblO365License.SkuPartNumber = 'EMS' Then
      'ENTERPRISE MOBILITY + SECURITY E3'
    When tblO365License.SkuPartNumber = 'EMSPREMIUM' Then
      'ENTERPRISE MOBILITY + SECURITY E5'
    When tblO365License.SkuPartNumber = 'EMS_GOV' Then
      'Enterprise Mobility + Security G3 GCC'
    When tblO365License.SkuPartNumber = 'EMSPREMIUM_GOV' Then
      'Enterprise Mobility + Security G5 GCC'
    When tblO365License.SkuPartNumber = 'EOP_ENTERPRISE_PREMIUM' Then
      'Exchange Enterprise CAL Services (EOP DLP)'
    When tblO365License.SkuPartNumber = 'EXCHANGESTANDARD' Then
      'Exchange Online (Plan 1)'
    When tblO365License.SkuPartNumber = 'EXCHANGEENTERPRISE' Then
      'EXCHANGE ONLINE (PLAN 2)'
    When tblO365License.SkuPartNumber = 'EXCHANGEARCHIVE_ADDON' Then
      'EXCHANGE ONLINE ARCHIVING FOR EXCHANGE ONLINE'
    When tblO365License.SkuPartNumber = 'EXCHANGEARCHIVE' Then
      'EXCHANGE ONLINE ARCHIVING FOR EXCHANGE SERVER'
    When tblO365License.SkuPartNumber = 'EXCHANGEESSENTIALS' Then
      'EXCHANGE ONLINE ESSENTIALS (ExO P1 BASED)'
    When tblO365License.SkuPartNumber = 'EXCHANGE_S_ESSENTIALS' Then
      'EXCHANGE ONLINE ESSENTIALS'
    When tblO365License.SkuPartNumber = 'EXCHANGEDESKLESS' Then
      'EXCHANGE ONLINE KIOSK'
    When tblO365License.SkuPartNumber = 'EXCHANGETELCO' Then
      'EXCHANGE ONLINE POP'
    When tblO365License.SkuPartNumber = 'INTUNE_A' Then 'INTUNE'
    When tblO365License.SkuPartNumber = 'AX7_USER_TRIAL' Then
      'Microsoft Dynamics AX7 User Trial'
    When tblO365License.SkuPartNumber = 'MFA_STANDALONE' Then
      'Microsoft Azure Multi-Factor Authentication'
    When tblO365License.SkuPartNumber = 'THREAT_INTELLIGENCE' Then
      'Microsoft Defender for Office 365 (Plan 2)'
    When tblO365License.SkuPartNumber = 'M365EDU_A1' Then 'Microsoft 365 A1'
    When tblO365License.SkuPartNumber = 'M365EDU_A3_FACULTY' Then
      'Microsoft 365 A3 for Faculty'
    When tblO365License.SkuPartNumber = 'M365EDU_A3_STUDENT' Then
      'MICROSOFT 365 A3 FOR STUDENTS'
    When tblO365License.SkuPartNumber = 'M365EDU_A3_STUUSEBNFT' Then
      'Microsoft 365 A3 for students use benefit'
    When tblO365License.SkuPartNumber = 'M365EDU_A3_STUUSEBNFT_RPA1' Then
      'Microsoft 365 A3 - Unattended License for students use benefit'
    When tblO365License.SkuPartNumber = 'M365EDU_A5_FACULTY' Then
      'Microsoft 365 A5 for Faculty'
    When tblO365License.SkuPartNumber = 'M365EDU_A5_STUDENT' Then
      'MICROSOFT 365 A5 FOR STUDENTS'
    When tblO365License.SkuPartNumber = 'M365EDU_A5_STUUSEBNFT' Then
      'Microsoft 365 A5 for students use benefit'
    When tblO365License.SkuPartNumber = 'M365EDU_A5_NOPSTNCONF_STUUSEBNFT' Then
      'Microsoft 365 A5 without Audio Conferencing for students use benefit'
    When tblO365License.SkuPartNumber = 'O365_BUSINESS' Then
      'MICROSOFT 365 APPS FOR BUSINESS'
    When tblO365License.SkuPartNumber = 'SMB_BUSINESS' Then
      'MICROSOFT 365 APPS FOR BUSINESS'
    When tblO365License.SkuPartNumber = 'OFFICESUBSCRIPTION' Then
      'MICROSOFT 365 APPS FOR ENTERPRISE'
    When tblO365License.SkuPartNumber = 'MCOMEETADV_GOC' Then
      'MICROSOFT 365 AUDIO CONFERENCING FOR GCC'
    When tblO365License.SkuPartNumber = 'O365_BUSINESS_ESSENTIALS' Then
      'MICROSOFT 365 BUSINESS BASIC'
    When tblO365License.SkuPartNumber = 'SMB_BUSINESS_ESSENTIALS' Then
      'MICROSOFT 365 BUSINESS BASIC'
    When tblO365License.SkuPartNumber = 'O365_BUSINESS_PREMIUM' Then
      'MICROSOFT 365 BUSINESS STANDARD'
    When tblO365License.SkuPartNumber = 'SMB_BUSINESS_PREMIUM' Then
      'MICROSOFT 365 BUSINESS STANDARD - PREPAID LEGACY'
    When tblO365License.SkuPartNumber = 'SPB' Then
      'MICROSOFT 365 BUSINESS PREMIUM'
    When tblO365License.SkuPartNumber = 'BUSINESS_VOICE_MED2_TELCO' Then
      'Microsoft 365 Business Voice (US)'
    When tblO365License.SkuPartNumber = 'BUSINESS_VOICE_DIRECTROUTING'
    Then 'Microsoft 365 Business Voice (without calling plan) '
    When tblO365License.SkuPartNumber = 'BUSINESS_VOICE_DIRECTROUTING_MED' Then
      'Microsoft 365 Business Voice (without Calling Plan) for US'
    When tblO365License.SkuPartNumber = 'MCOPSTN_5' Then
      'MICROSOFT 365 DOMESTIC CALLING PLAN (120 Minutes)'
    When tblO365License.SkuPartNumber = 'MCOPSTN_1_GOV' Then
      'Microsoft 365 Domestic Calling Plan for GCC'
    When tblO365License.SkuPartNumber = 'SPE_E3' Then 'MICROSOFT 365 E3'
    When tblO365License.SkuPartNumber = 'SPE_E3_RPA1' Then
      'Microsoft 365 E3 - Unattended License'
    When tblO365License.SkuPartNumber = 'SPE_E3_USGOV_DOD' Then
      'Microsoft 365 E3_USGOV_DOD'
    When tblO365License.SkuPartNumber = 'SPE_E3_USGOV_GCCHIGH' Then
      'Microsoft 365 E3_USGOV_GCCHIGH'
    When tblO365License.SkuPartNumber = 'SPE_E5' Then 'Microsoft 365 E5'
    When tblO365License.SkuPartNumber = 'INFORMATION_PROTECTION_COMPLIANCE' Then
      'Microsoft 365 E5 Compliance'
    When tblO365License.SkuPartNumber = 'IDENTITY_THREAT_PROTECTION' Then
      'Microsoft 365 E5 Security'
    When tblO365License.SkuPartNumber = 'IDENTITY_THREAT_PROTECTION_FOR_EMS_E5'
    Then 'Microsoft 365 E5 Security for EMS E5'
    When tblO365License.SkuPartNumber = 'M365_F1' Then 'Microsoft 365 F1'
    When tblO365License.SkuPartNumber = 'SPE_F1' Then 'Microsoft 365 F3'
    When tblO365License.SkuPartNumber = 'FLOW_FREE' Then 'MICROSOFT FLOW FREE'
    When tblO365License.SkuPartNumber = 'MCOMEETADV_GOV' Then
      'MICROSOFT 365 AUDIO CONFERENCING FOR GCC'
    When tblO365License.SkuPartNumber = 'M365_E5_SUITE_COMPONENTS' Then
      'Microsoft 365 E5 Suite features'
    When tblO365License.SkuPartNumber = 'M365_F1_COMM' Then 'Microsoft 365 F1'
    When tblO365License.SkuPartNumber = 'M365_G3_GOV' Then
      'MICROSOFT 365 G3 GCC'
    When tblO365License.SkuPartNumber = 'MCOEV' Then
      'MICROSOFT 365 PHONE SYSTEM'
    When tblO365License.SkuPartNumber = 'MCOEV_DOD' Then
      'MICROSOFT 365 PHONE SYSTEM FOR DOD'
    When tblO365License.SkuPartNumber = 'MCOEV_FACULTY' Then
      'MICROSOFT 365 PHONE SYSTEM FOR FACULTY'
    When tblO365License.SkuPartNumber = 'MCOEV_GOV' Then
      'MICROSOFT 365 PHONE SYSTEM FOR GCC'
    When tblO365License.SkuPartNumber = 'MCOEV_GCCHIGH' Then
      'MICROSOFT 365 PHONE SYSTEM FOR GCCHIGH'
    When tblO365License.SkuPartNumber = 'MCOEVSMB_1' Then
      'MICROSOFT 365 PHONE SYSTEM FOR SMALL AND MEDIUM BUSINESS'
    When tblO365License.SkuPartNumber = 'MCOEV_STUDENT' Then
      'MICROSOFT 365 PHONE SYSTEM FOR STUDENTS'
    When tblO365License.SkuPartNumber = 'MCOEV_TELSTRA' Then
      'MICROSOFT 365 PHONE SYSTEM FOR TELSTRA'
    When tblO365License.SkuPartNumber = 'MCOEV_USGOV_DOD' Then
      'MICROSOFT 365 PHONE SYSTEM_USGOV_DOD'
    When tblO365License.SkuPartNumber = 'MCOEV_USGOV_GCCHIGH' Then
      'MICROSOFT 365 PHONE SYSTEM_USGOV_GCCHIGH'
    When tblO365License.SkuPartNumber = 'PHONESYSTEM_VIRTUALUSER' Then
      'MICROSOFT 365 PHONE SYSTEM - VIRTUAL USER'
    When tblO365License.SkuPartNumber = 'PHONESYSTEM_VIRTUALUSER_GOV' Then
      'Microsoft 365 Phone System - Virtual User for GCC'
    When tblO365License.SkuPartNumber = 'M365_SECURITY_COMPLIANCE_FOR_FLW' Then
      'Microsoft 365 Security and Compliance for Firstline Workers'
    When tblO365License.SkuPartNumber = 'MICROSOFT_BUSINESS_CENTER' Then
      'MICROSOFT BUSINESS CENTER'
    When tblO365License.SkuPartNumber = 'ADALLOM_STANDALONE' Then
      'Microsoft Cloud App Security'
    When tblO365License.SkuPartNumber = 'WIN_DEF_ATP' Then
      'MICROSOFT DEFENDER FOR ENDPOINT'
    When tblO365License.SkuPartNumber = 'MDATP_Server' Then
      'Microsoft Defender for Endpoint Server'
    When tblO365License.SkuPartNumber = 'CRMPLAN2' Then
      'MICROSOFT DYNAMICS CRM ONLINE BASIC'
    When tblO365License.SkuPartNumber = 'ATA' Then
      'Microsoft Defender for Identity'
    When tblO365License.SkuPartNumber = 'THREAT_INTELLIGENCE_GOV' Then
      'Microsoft Defender for Office 365 (Plan 2) GCC'
    When tblO365License.SkuPartNumber = 'CRMSTANDARD' Then
      'MICROSOFT DYNAMICS CRM ONLINE'
    When tblO365License.SkuPartNumber = 'IT_ACADEMY_AD' Then
      'MS IMAGINE ACADEMY'
    When tblO365License.SkuPartNumber = 'INTUNE_A_D' Then
      'Microsoft Intune Device'
    When tblO365License.SkuPartNumber = 'INTUNE_A_D_GOV' Then
      'MICROSOFT INTUNE DEVICE FOR GOVERNMENT'
    When tblO365License.SkuPartNumber = 'POWERAPPS_VIRAL' Then
      'Microsoft Power Apps Plan 2 Trial'
    When tblO365License.SkuPartNumber = 'FLOW_P2' Then
      'MICROSOFT POWER AUTOMATE PLAN 2'
    When tblO365License.SkuPartNumber = 'INTUNE_SMB' Then 'MICROSOFT INTUNE SMB'
    When tblO365License.SkuPartNumber = 'POWERFLOW_P2' Then
      'Microsoft Power Apps Plan 2 (Qualified Offer)'
    When tblO365License.SkuPartNumber = 'STREAM' Then 'MICROSOFT STREAM'
    When tblO365License.SkuPartNumber = 'STREAM_P2' Then
      'Microsoft Stream Plan 2'
    When tblO365License.SkuPartNumber = 'STREAM_STORAGE' Then
      'Microsoft Stream Storage Add-On (500 GB)'
    When tblO365License.SkuPartNumber = 'TEAMS_FREE' Then
      'MICROSOFT TEAMS (FREE)'
    When tblO365License.SkuPartNumber = 'TEAMS_EXPLORATORY' Then
      'MICROSOFT TEAMS EXPLORATORY'
    When tblO365License.SkuPartNumber = 'MEETING_ROOM' Then
      'Microsoft Teams Rooms Standard'
    When tblO365License.SkuPartNumber = 'MS_TEAMS_IW' Then
      'Microsoft Teams Trial'
    When tblO365License.SkuPartNumber = 'EXPERTS_ON_DEMAND' Then
      'Microsoft Threat Experts - Experts on Demand'
    When tblO365License.SkuPartNumber = 'OFFICE365_MULTIGEO' Then
      'Multi-Geo Capabilities in Office 365'
    When tblO365License.SkuPartNumber = 'MTR_PREM' Then 'Teams Rooms Premium'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACKPLUS_FACULTY' Then
      'Office 365 A3 for faculty'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACKPLUS_STUDENT' Then
      'Office 365 A3 for students'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPREMIUM_FACULTY' Then
      'Office 365 A5 for faculty'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPREMIUM_STUDENT' Then
      'Office 365 A5 for students'
    When tblO365License.SkuPartNumber = 'EQUIVIO_ANALYTICS' Then
      'Office 365 Advanced Compliance'
    When tblO365License.SkuPartNumber = 'ATP_ENTERPRISE' Then
      'Microsoft Defender for Office 365 (Plan 1)'
    When tblO365License.SkuPartNumber = 'SHAREPOINTSTORAGE_GOV' Then
      'Office 365 Extra File Storage for GCC'
    When tblO365License.SkuPartNumber = 'TEAMS_COMMERCIAL_TRIAL' Then
      'Microsoft Teams Commercial Cloud'
    When tblO365License.SkuPartNumber = 'ADALLOM_O365' Then
      'Office 365 Cloud App Security'
    When tblO365License.SkuPartNumber = 'SHAREPOINTSTORAGE' Then
      'Office 365 Extra File Storage'
    When tblO365License.SkuPartNumber = 'STANDARDPACK' Then 'OFFICE 365 E1'
    When tblO365License.SkuPartNumber = 'STANDARDWOFFPACK' Then 'OFFICE 365 E2'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACK' Then 'Office 365 E3'
    When tblO365License.SkuPartNumber = 'DEVELOPERPACK' Then
      'OFFICE 365 E3 DEVELOPER'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACK_USGOV_DOD' Then
      'Office 365 E3_USGOV_DOD'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACK_USGOV_GCCHIGH'
    Then 'Office 365 E3_USGOV_GCCHIGH'
    When tblO365License.SkuPartNumber = 'ENTERPRISEWITHSCAL' Then
      'OFFICE 365 E4'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPREMIUM' Then 'Office 365 E5'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPREMIUM_NOPSTNCONF'
    Then 'OFFICE 365 E5 WITHOUT AUDIO CONFERENCING'
    When tblO365License.SkuPartNumber = 'DESKLESSPACK' Then 'OFFICE 365 F3'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPACK_GOV' Then
      'OFFICE 365 G3 GCC'
    When tblO365License.SkuPartNumber = 'ENTERPRISEPREMIUM_GOV' Then
      'Office 365 G5 GCC'
    When tblO365License.SkuPartNumber = 'MIDSIZEPACK' Then
      'OFFICE 365 MIDSIZE BUSINESS'
    When tblO365License.SkuPartNumber = 'LITEPACK' Then
      'OFFICE 365 SMALL BUSINESS'
    When tblO365License.SkuPartNumber = 'LITEPACK_P2' Then
      'OFFICE 365 SMALL BUSINESS PREMIUM'
    When tblO365License.SkuPartNumber = 'WACONEDRIVESTANDARD' Then
      'ONEDRIVE FOR BUSINESS (PLAN 1)'
    When tblO365License.SkuPartNumber = 'WACONEDRIVEENTERPRISE' Then
      'ONEDRIVE FOR BUSINESS (PLAN 2)'
    When tblO365License.SkuPartNumber = 'POWERAPPS_INDIVIDUAL_USER' Then
      'POWERAPPS AND LOGIC FLOWS'
    When tblO365License.SkuPartNumber = 'POWERAPPS_PER_APP_IW' Then
      'PowerApps per app baseline access'
    When tblO365License.SkuPartNumber = 'POWERAPPS_PER_APP' Then
      'Power Apps per app plan'
    When tblO365License.SkuPartNumber = 'POWERAPPS_PER_USER' Then
      'Power Apps per user plan'
    When tblO365License.SkuPartNumber = 'FLOW_BUSINESS_PROCESS' Then
      'Power Automate per flow plan'
    When tblO365License.SkuPartNumber = 'FLOW_PER_USER' Then
      'Power Automate per user plan'
    When tblO365License.SkuPartNumber = 'FLOW_PER_USER_DEPT' Then
      'Power Automate per user plan dept'
    When tblO365License.SkuPartNumber = 'POWERAUTOMATE_ATTENDED_RPA' Then
      'Power Automate per user with attended RPA plan'
    When tblO365License.SkuPartNumber = 'POWERAUTOMATE_UNATTENDED_RPA'
    Then 'Power Automate unattended RPA add-on'
    When tblO365License.SkuPartNumber = 'POWER_BI_INDIVIDUAL_USER' Then
      'Power BI'
    When tblO365License.SkuPartNumber = 'POWER_BI_STANDARD' Then
      'Power BI (free)'
    When tblO365License.SkuPartNumber = 'POWER_BI_ADDON' Then
      'POWER BI FOR OFFICE 365 ADD-ON'
    When tblO365License.SkuPartNumber = 'PBI_PREMIUM_P1_ADDON' Then
      'Power BI Premium P1'
    When tblO365License.SkuPartNumber = 'PBI_PREMIUM_PER_USER' Then
      'Power BI Premium Per User'
    When tblO365License.SkuPartNumber = 'PBI_PREMIUM_PER_USER_ADDON' Then
      'Power BI Premium Per User Add-On'
    When tblO365License.SkuPartNumber = 'PBI_PREMIUM_PER_USER_DEPT' Then
      'Power BI Premium Per User Dept'
    When tblO365License.SkuPartNumber = 'POWER_BI_PRO' Then 'Power BI Pro'
    When tblO365License.SkuPartNumber = 'POWER_BI_PRO_CE' Then 'Power BI Pro CE'
    When tblO365License.SkuPartNumber = 'POWER_BI_PRO_DEPT' Then
      'Power BI Pro Dept'
    When tblO365License.SkuPartNumber = 'VIRTUAL_AGENT_BASE' Then
      'Power Virtual Agent'
    When tblO365License.SkuPartNumber = 'CCIBOTS_PRIVPREV_VIRAL' Then
      'Power Virtual Agents Viral Trial'
    When tblO365License.SkuPartNumber = 'PROJECTCLIENT' Then
      'PROJECT FOR OFFICE 365'
    When tblO365License.SkuPartNumber = 'PROJECTESSENTIALS' Then
      'Project Online Essentials'
    When tblO365License.SkuPartNumber = 'PROJECTPREMIUM' Then
      'PROJECT ONLINE PREMIUM'
    When tblO365License.SkuPartNumber = 'PROJECTONLINE_PLAN_1' Then
      'PROJECT ONLINE PREMIUM WITHOUT PROJECT CLIENT'
    When tblO365License.SkuPartNumber = 'PROJECTONLINE_PLAN_2' Then
      'PROJECT ONLINE WITH PROJECT FOR OFFICE 365'
    When tblO365License.SkuPartNumber = 'PROJECT_P1' Then 'PROJECT PLAN 1'
    When tblO365License.SkuPartNumber = 'PROJECT_PLAN1_DEPT' Then
      'Project Plan 1 (for Department)'
    When tblO365License.SkuPartNumber = 'PROJECTPROFESSIONAL' Then
      'Project Plan 3'
    When tblO365License.SkuPartNumber = 'PROJECT_PLAN3_DEPT' Then
      'Project Plan 3 (for Department)'
    When tblO365License.SkuPartNumber = 'PROJECTPROFESSIONAL_GOV' Then
      'Project Plan 3 for GCC'
    When tblO365License.SkuPartNumber = 'PROJECTPREMIUM_GOV' Then
      'Project Plan 5 for GCC'
    When tblO365License.SkuPartNumber = 'RIGHTSMANAGEMENT_ADHOC' Then
      'Rights Management Adhoc'
    When tblO365License.SkuPartNumber = 'RMSBASIC' Then
      'Rights Management Service Basic Content Protection'
    When tblO365License.SkuPartNumber = 'DYN365_IOT_INTELLIGENCE_ADDL_MACHINES'
    Then
      'Sensor Data Intelligence Additional Machines Add-in for Dynamics 365 Supply Chain Management'
    When tblO365License.SkuPartNumber = 'DYN365_IOT_INTELLIGENCE_SCENARIO' Then
      'Sensor Data Intelligence Scenario Add-in for Dynamics 365 Supply Chain Management'
    When tblO365License.SkuPartNumber = 'SHAREPOINTSTANDARD' Then
      'SHAREPOINT ONLINE (PLAN 1)'
    When tblO365License.SkuPartNumber = 'SHAREPOINTENTERPRISE' Then
      'SHAREPOINT ONLINE (PLAN 2)'
    When tblO365License.SkuPartNumber = 'Intelligent_Content_Services'
    Then 'SharePoint Syntex'
    When tblO365License.SkuPartNumber = 'MCOIMP' Then
      'SKYPE FOR BUSINESS ONLINE (PLAN 1)'
    When tblO365License.SkuPartNumber = 'MCOSTANDARD' Then
      'SKYPE FOR BUSINESS ONLINE (PLAN 2)'
    When tblO365License.SkuPartNumber = 'MCOPSTN2' Then
      'SKYPE FOR BUSINESS PSTN DOMESTIC AND INTERNATIONAL CALLING'
    When tblO365License.SkuPartNumber = 'MCOPSTN1' Then
      'SKYPE FOR BUSINESS PSTN DOMESTIC CALLING'
    When tblO365License.SkuPartNumber = 'MCOPSTN5' Then
      'SKYPE FOR BUSINESS PSTN DOMESTIC CALLING (120 Minutes)'
    When tblO365License.SkuPartNumber = 'MCOPSTNEAU2' Then
      'TELSTRA CALLING FOR O365'
    When tblO365License.SkuPartNumber = 'UNIVERSAL_PRINT' Then 'Universal Print'
    When tblO365License.SkuPartNumber = 'VISIO_PLAN1_DEPT' Then 'Visio Plan 1'
    When tblO365License.SkuPartNumber = 'VISIO_PLAN2_DEPT' Then 'Visio Plan 2'
    When tblO365License.SkuPartNumber = 'VISIOONLINE_PLAN1' Then
      'VISIO ONLINE PLAN 1'
    When tblO365License.SkuPartNumber = 'VISIOCLIENT' Then 'VISIO ONLINE PLAN 2'
    When tblO365License.SkuPartNumber = 'VISIOCLIENT_GOV' Then
      'VISIO PLAN 2 FOR GCC'
    When tblO365License.SkuPartNumber = 'TOPIC_EXPERIENCES' Then 'Viva Topics'
    When tblO365License.SkuPartNumber = 'WIN10_ENT_A3_FAC' Then
      'Windows 10 Enterprise A3 for faculty'
    When tblO365License.SkuPartNumber = 'WIN10_ENT_A3_STU' Then
      'Windows 10 Enterprise A3 for students'
    When tblO365License.SkuPartNumber = 'WIN10_PRO_ENT_SUB' Then
      'WINDOWS 10 ENTERPRISE E3'
    When tblO365License.SkuPartNumber = 'WIN10_VDA_E3' Then
      'WINDOWS 10 ENTERPRISE E3'
    When tblO365License.SkuPartNumber = 'WIN10_VDA_E5' Then
      'Windows 10 Enterprise E5'
    When tblO365License.SkuPartNumber = 'WINE5_GCC_COMPAT' Then
      'Windows 10 Enterprise E5 Commercial (GCC Compatible)'
    When tblO365License.SkuPartNumber = 'CPC_B_2C_4RAM_64GB' Then
      'Windows 365 Business 2 vCPU 4 GB 64 GB'
    When tblO365License.SkuPartNumber = 'CPC_B_4C_16RAM_128GB_WHB' Then
      'Windows 365 Business 4 vCPU 16 GB 128 GB (with Windows Hybrid Benefit)'
    When tblO365License.SkuPartNumber = 'CPC_E_2C_4GB_64GB' Then
      'Windows 365 Enterprise 2 vCPU 4 GB 64 GB'
    When tblO365License.SkuPartNumber = 'WINDOWS_STORE' Then
      'WINDOWS STORE FOR BUSINESS'
    When tblO365License.SkuPartNumber = 'WORKPLACE_ANALYTICS' Then
      'Microsoft Workplace Analytics'
    When tblO365Sku.DisplayName Is Not Null Then tblO365Sku.DisplayName
    Else tblO365License.SkuPartNumber
  End As 'ActualProduct',
  tblO365License.SkuPartNumber As DisplayName,
  tblO365License.AppliesTo,
  tblO365License.ConsumedUnits As Consumed,
  tblO365License.PrepaidUnitsEnabled As Enabled,
  Case
    When tblO365Sku.DisplayName Is Null Then tblO365License.SkuPartNumber
    Else tblO365Sku.DisplayName
  End As Old_Name,
  tblO365License.PrepaidUnitsSuspended As Suspended,
  tblO365License.PrepaidUnitsWarning As Warnings,
  tblO365Organization.DisplayName As Organization,
  tblO365Organization.TenantId
From tblO365License
  Left Outer Join tblO365Sku On tblO365Sku.Name = tblO365License.SkuPartNumber
  Inner Join tblO365Organization On tblO365License.OrganizationId =
      tblO365Organization.OrganizationId
Order By Organization