Forum Discussion

PaddyB's avatar
PaddyB
Brass Contributor
Sep 15, 2022

PowerShell Script to read Product SKU

Hi Pro's 🙂

 

i fight now since hours to fix a script. my script check for each use the assigned Microsoft licenses and generate a screen output and a CSV... so far so good.

 

the script generates the information according to data from a has table. one has table for the SKU - ProductName and the second hash table for the SKU - ProductPrice

 

i work for the ProductPrice with $Skuprice

the SKU - ProductName and all other stuff is working very nice but not the SKU - ProductPrice.

there is no output on screen and csv

 

 

  $CSV = "C:\Scripts\Office_365_User_Licensing.csv"

$Sku = @{

    "SPE_F1"		                     = "Enterprise - Plan F3"
    "DYN365_SCM"		                 = "Dynamics CRM SCM"
    "D365_SALES_ENT_ATTACH"		         = "Dynamics CRM Sales AddOn"
    "OFFICE365_MULTIGEO"		         = "Microsoft 365 GEO Location"
	"O365_BUSINESS_ESSENTIALS"		     = "Office 365 - Business Essentials"
	"O365_BUSINESS_PREMIUM"			     = "Office 365 - Business Premium"
	"DESKLESSPACK"					     = "Office 365 - Plan K1"
	"DESKLESSWOFFPACK"				     = "Office 365 - Plan K2"
	"LITEPACK"						     = "Office 365 - Plan P1"
	"EXCHANGESTANDARD"				     = "Office 365 - Exchange Online Only"
	"STANDARDPACK"					     = "Office 365 - Enterprise - Plan E1"
	"STANDARDWOFFPACK"				     = "Office 365 - Enterprise - Plan E2"
	"ENTERPRISEPACK"					 = "Office 365 - Enterprise - Plan E3"
	"ENTERPRISEPACKLRG"				     = "Office 365 - Enterprise - Plan E3"
	"ENTERPRISEWITHSCAL"				 = "Office 365 - Enterprise - Plan E4"
	"STANDARDPACK_STUDENT"			     = "Office 365 - Office 365 - Plan A1 for Students"
	"STANDARDWOFFPACKPACK_STUDENT"	     = "Office 365 - Office 365 - Plan A2 for Students"
	"ENTERPRISEPACK_STUDENT"			 = "Office 365 - Plan A3 for Students"
	"ENTERPRISEWITHSCAL_STUDENT"		 = "Office 365 - Plan A4 for Students"
	"STANDARDPACK_FACULTY"			     = "Office 365 - Plan A1 for Faculty"
	"STANDARDWOFFPACKPACK_FACULTY"	     = "Office 365 - Plan A2 for Faculty"
	"ENTERPRISEPACK_FACULTY"			 = "Office 365 - Plan A3 for Faculty"
	"ENTERPRISEWITHSCAL_FACULTY"		 = "Office 365 - Plan A4 for Faculty"
	"ENTERPRISEPACK_B_PILOT"			 = "Office 365 - Enterprise Preview"
	"STANDARD_B_PILOT"				     = "Office 365 - Small Business Preview"
	"VISIOCLIENT"					     = "Visio Pro Online"
	"POWER_BI_ADDON"					 = "Power BI - Addon"
	"POWER_BI_INDIVIDUAL_USE"		     = "Power BI - Individual User"
	"POWER_BI_STANDALONE"			     = "Power BI - Stand Alone"
	"POWER_BI_STANDARD"				     = "Power BI - Standard"
	"PROJECTESSENTIALS"				     = "Project Lite"
	"PROJECTCLIENT"					     = "Project Professional"
	"PROJECTONLINE_PLAN_1"			     = "Project Online - Plan 1"
	"PROJECTONLINE_PLAN_2"			     = "Project Online and PRO - Plan 2"
	"ProjectPremium"					 = "Project Online Premium - Plan 3"
	"ECAL_SERVICES"					     = "ECAL"
	"EMS"							     = "Enterprise Mobility Suite"
	"RIGHTSMANAGEMENT_ADHOC"			 = "Azure Rights Management"
	"MCOMEETADV"						 = "PSTN conferencing"
	"SHAREPOINTSTORAGE"				     = "SharePoint Storage"
	"PLANNERSTANDALONE"				     = "Office 365 - Planner Standalone"
	"CRMIUR"							 = "CMRIUR"
	"BI_AZURE_P1"					     = "Power BI - Reporting and Analytics"
	"INTUNE_A"						     = "Intune Plan A"
	"PROJECTWORKMANAGEMENT"			     = "Office 365 - Planner Preview"
	"ATP_ENTERPRISE"					 = "Exchange Online Advanced Threat Protection"
	"EQUIVIO_ANALYTICS"				     = "Office 365 - Advanced eDiscovery"
	"AAD_BASIC"						     = "Azure Active Directory Basic"
	"RMS_S_ENTERPRISE"				     = "Azure Active Directory Rights Management"
	"AAD_PREMIUM"					     = "Azure Active Directory Premium"
	"MFA_PREMIUM"					     = "Azure Multi-Factor Authentication"
	"STANDARDPACK_GOV"				     = "Office 365 - Plan G1 for Government"
	"STANDARDWOFFPACK_GOV"			     = "Office 365 - Plan G2 for Government"
	"ENTERPRISEPACK_GOV"				 = "Office 365 - Plan G3 for Government"
	"ENTERPRISEWITHSCAL_GOV"			 = "Office 365 - Plan G4 for Government"
	"DESKLESSPACK_GOV"				     = "Office 365 - Plan K1 for Government"
	"ESKLESSWOFFPACK_GOV"			     = "Office 365 - Plan K2 for Government"
	"EXCHANGESTANDARD_GOV"			     = "Office 365 Exchange Online - Plan 1 for Government"
	"EXCHANGEENTERPRISE_GOV"			 = "Office 365 Exchange Online - Plan 2 for Government"
	"SHAREPOINTDESKLESS_GOV"			 = "SharePoint Online Kiosk"
	"EXCHANGE_S_DESKLESS_GOV"		     = "Exchange Kiosk"
	"RMS_S_ENTERPRISE_GOV"			     = "Windows Azure Active Directory Rights Management"
	"OFFICESUBSCRIPTION_GOV"			 = "Office ProPlus"
	"MCOSTANDARD_GOV"				     = "Lync - Plan 2G"
	"SHAREPOINTWAC_GOV"				     = "Office Online for Government"
	"SHAREPOINTENTERPRISE_GOV"		     = "SharePoint - Plan 2G"
	"EXCHANGE_S_ENTERPRISE_GOV"		     = "Exchange - Plan 2G"
	"EXCHANGE_S_ARCHIVE_ADDON_GOV"	     = "Exchange Online Archiving"
	"EXCHANGE_S_DESKLESS"			     = "Exchange Online Kiosk"
	"SHAREPOINTDESKLESS"				 = "SharePoint Online Kiosk"
	"SHAREPOINTWAC"					     = "Office Online"
	"YAMMER_ENTERPRISE"				     = "Yammer Enterprise"
	"EXCHANGE_L_STANDARD"			     = "Exchange Online - Plan 1"
	"MCOLITE"						     = "Lync Online - Plan 1"
	"SHAREPOINTLITE"					 = "SharePoint Online - Plan 1"
	"OFFICE_PRO_PLUS_SUBSCRIPTION_SMBIZ" = "Office ProPlus"
	"EXCHANGE_S_STANDARD_MIDMARKET"	     = "Exchange Online - Plan 1"
	"MCOSTANDARD_MIDMARKET"			     = "Lync Online - Plan 1"
	"SHAREPOINTENTERPRISE_MIDMARKET"	 = "SharePoint Online - Plan 1"
	"OFFICESUBSCRIPTION"				 = "Office ProPlus"
	"YAMMER_MIDSIZE"					 = "Yammer"
	"DYN365_ENTERPRISE_PLAN1"		     = "Dynamics 365 Customer Engagement Plan Enterprise Edition"
	"ENTERPRISEPREMIUM_NOPSTNCONF"	     = "Enterprise E5 (without Audio Conferencing)"
	"ENTERPRISEPREMIUM"				     = "Enterprise E5 (with Audio Conferencing)"
	"MCOSTANDARD"					     = "Skype for Business Online Standalone - Plan 2"
	"PROJECT_MADEIRA_PREVIEW_IW_SKU"	 = "Dynamics 365 for Financials for IWs"
	"STANDARDWOFFPACK_IW_STUDENT"	     = "Office 365 - Education for Students"
	"STANDARDWOFFPACK_IW_FACULTY"	     = "Office 365 - Education for Faculty"
	"EOP_ENTERPRISE_FACULTY"			 = "Exchange Online Protection for Faculty"
	"EXCHANGESTANDARD_STUDENT"		     = "Exchange Online - Plan 1 for Students"
	"OFFICESUBSCRIPTION_STUDENT"		 = "Office ProPlus Student Benefit"
	"STANDARDWOFFPACK_FACULTY"		     = "Office 365 - Education - Plan E1 for Faculty"
	"STANDARDWOFFPACK_STUDENT"		     = "Office 365 - Plan A2 for Students"
	"DYN365_FINANCIALS_BUSINESS_SKU"	 = "Dynamics 365 for Financials Business Edition"
	"DYN365_FINANCIALS_TEAM_MEMBERS_SKU" = "Dynamics 365 for Team Members Business Edition"
	"FLOW_FREE"						     = "Flow Free"
	"POWER_BI_PRO"					     = "Power BI Pro"
	"O365_BUSINESS"					     = "Office 365 Business"
	"DYN365_ENTERPRISE_SALES"		     = "Dynamics Office 365 Enterprise Sales"
	"RIGHTSMANAGEMENT"				     = "Rights Management"
	"PROJECTPROFESSIONAL"			     = "Project Professional"
	"VISIOONLINE_PLAN1"				     = "Visio Online - Plan 1"
	"EXCHANGEENTERPRISE"				 = "Exchange Online - Plan 2"
	"DYN365_ENTERPRISE_P1_IW"		     = "Dynamics 365 P1 Trial for Information Workers"
	"DYN365_ENTERPRISE_TEAM_MEMBERS"	 = "Dynamics 365 For Team Members Enterprise Edition"
	"CRMSTANDARD"					     = "Microsoft Dynamics CRM Online Professional"
	"EXCHANGEARCHIVE_ADDON"			     = "Exchange Online Archiving For Exchange Online"
	"EXCHANGEDESKLESS"				     = "Exchange Online Kiosk"
	"SPZA_IW"						     = "App Connect"
	"WINDOWS_STORE"					     = "Windows Store for Business"
	"MCOEV"							     = "Phone System"
	"VIDEO_INTEROP"					     = "Polycom Skype Meeting Video Interop for Skype for Business"
	"SPE_E5"							 = "Microsoft 365 - Plan E5"
	"SPE_E3"							 = "Microsoft 365 - Plan E3"
	"ATA"							     = "Advanced Threat Analytics"
	"MCOPSTN2"						     = "Domestic and International Calling Plan"
	"FLOW_P1"						     = "Flow - Plan 1"
	"FLOW_P2"						     = "Flow - Plan 2"
	"CRMSTORAGE"						 = "Dynamics CRM Online Additional Storage"
	"SMB_APPS"						     = "Business Apps"
	"MICROSOFT_BUSINESS_CENTER"		     = "Business Center"
	"DYN365_TEAM_MEMBERS"			     = "Dynamics 365 Team Members"
	"STREAM"							 = "Stream Trial"
	"EMSPREMIUM"                         = "Enterprise Mobility + Security (EMS) - Plan E5"
    "M365_E5_SUITE_COMPONENTS"           = "Microsoft M365 - E5"
    "PROJECT_P1"                         = "Project - P1"
    "WIN_DEF_ATP"                        = "Defener ATP"
    "THREAT_INTELLIGENCE"                = "Defender for Office 365 - Plan 2"
    "TEAMS_EXPLORATORY"                  = "Teams Premium Access Trial"
    "POWERAUTOMATE_ATTENDED_RPA"         = "Power Automate per user plan with attended RPA Plan 2"
    "DYN365_REGULATORY_SERVICE"          = "Dynamics - Regulierungsdienst, Enterprise Edition"
  
    
}

$Skuprice = @{

    "SPE_F1"		                     = "12345"
    "DYN365_SCM"		                 = "123456"
    "D365_SALES_ENT_ATTACH"		         = "123457"
    "OFFICE365_MULTIGEO"		         = "123458"
	"O365_BUSINESS_ESSENTIALS"		     = "123459"
	"O365_BUSINESS_PREMIUM"			     = "1234510"
	"DESKLESSPACK"					     = "1234511"
	"DESKLESSWOFFPACK"				     = "1234512"
	"LITEPACK"						     = "1234513"
	"EXCHANGESTANDARD"				     = "1234514"
	"STANDARDPACK"					     = "1234515"
	"STANDARDWOFFPACK"				     = "1234516"
	"ENTERPRISEPACK"					 = "1234517"
	"ENTERPRISEPACKLRG"				     = "1234518"
	"ENTERPRISEWITHSCAL"				 = "1234519"
	"STANDARDPACK_STUDENT"			     = "1234520"
	"STANDARDWOFFPACKPACK_STUDENT"	     = "1234521"
	"ENTERPRISEPACK_STUDENT"			 = "1234522"
	"ENTERPRISEWITHSCAL_STUDENT"		 = "1234523"
	"STANDARDPACK_FACULTY"			     = "1234524"
	"STANDARDWOFFPACKPACK_FACULTY"	     = "1234525"
	"ENTERPRISEPACK_FACULTY"			 = "1234526"
	"ENTERPRISEWITHSCAL_FACULTY"		 = "1234527"
	"ENTERPRISEPACK_B_PILOT"			 = "1234528"
	"STANDARD_B_PILOT"				     = "1234529"
	"VISIOCLIENT"					     = "1234530"
    
}

$Cred = Get-Credential
Connect-MsolService -Credential $Cred
$Users = Get-MsolUser -All | Where-Object { $_.isLicensed -eq "TRUE" } | Sort-Object DisplayName
Foreach ($User in $Users)
{
	Write-Host "Working on $($User.DisplayName)..." -ForegroundColor Yellow
	#Gets users license and splits it at the semicolon
	Write-Host "Getting all licenses for $($User.DisplayName)..." -ForegroundColor White
	$Licenses = ((Get-MsolUser -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID
	If (($Licenses).Count -gt 1)
	{
		Foreach ($License in $Licenses)
		{
			Write-Host "Finding $License in the Hash Table..." -ForegroundColor White
			$LicenseItem = $License -split ":" | Select-Object -Last 1
			$TextLic = $Sku.Item("$LicenseItem")
            $Skuprice = $Skuprice
			If (!($TextLic))
			{
				Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
				$LicenseFallBackName = $License.AccountSkuId
				$NewObject02 = $null
				$NewObject02 = @()
				$NewObject01 = New-Object PSObject
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "Department" -Value $User.Department
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "City" -Value $User.City
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "UsageLocation" -Value $User.UsageLocation
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$LicenseFallBackName"
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "License Price per Month" -Value "$Skuprice"
				$NewObject02 += $NewObject01
				$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
			}
			Else
			{
				
				$NewObject02 = $null
				$NewObject02 = @()
				$NewObject01 = New-Object PSObject
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "Department" -Value $User.Department
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "City" -Value $User.City
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "UsageLocation" -Value $User.UsageLocation
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "License Price per Month" -Value "$Skuprice"
				$NewObject02 += $NewObject01
				$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
				
			}
		}
		
	}
	Else
	{
		Write-Host "Finding $Licenses in the Hash Table..." -ForegroundColor White
		$LicenseItem = ((Get-MsolUser -UserPrincipalName $User.UserPrincipalName).Licenses).AccountSkuID -split ":" | Select-Object -Last 1
		$TextLic = $Sku.Item("$LicenseItem")
		If (!($TextLic))
		{
			Write-Host "Error: The Hash Table has no match for $LicenseItem for $($User.DisplayName)!" -ForegroundColor Red
			$LicenseFallBackName = $License.AccountSkuId
			$NewObject02 = $null
			$NewObject02 = @()
			$NewObject01 = New-Object PSObject
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "Department" -Value $User.Department
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "City" -Value $User.City
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "UsageLocation" -Value $User.UsageLocation
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "License Price per Month" -Value "$Skuprice"
			$NewObject02 += $NewObject01
			$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
		}
		Else
		{
			$NewObject02 = $null
			$NewObject02 = @()
			$NewObject01 = New-Object PSObject
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "Name" -Value $User.DisplayName
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "User Principal Name" -Value $User.UserPrincipalName
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "Department" -Value $User.Department
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "City" -Value $User.City
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "UsageLocation" -Value $User.UsageLocation
				$NewObject01 | Add-Member -MemberType NoteProperty -Name "License" -Value "$TextLic"
                $NewObject01 | Add-Member -MemberType NoteProperty -Name "License Price per Month" -Value "$Skuprice"
			$NewObject02 += $NewObject01
			$NewObject02 | Export-CSV $CSV -NoTypeInformation -Append
		}
	}
}

 

 

any idea?

office 365
  • VasilMichev's avatar
    VasilMichev
    MVP
    Sep 15, 2022
    You might want to change line 187 ($Skuprice = $Skuprice) to using an actual lookup 🙂
    • PaddyB's avatar
      PaddyB
      Brass Contributor
      Sep 15, 2022
      Hi Vasil

      to be honest, i just playing around with PS. not so much deep knowledge. How i do the lookup you alk about?
      • VasilMichev's avatar
        VasilMichev
        MVP
        Sep 15, 2022
        You probably want something like this on said line:

        $Sku_price = $Skuprice.Item("$LicenseItem")

        Note I've also changed the variable name, as you will be overwriting the original hashtable otherwise. Thus, make sure to use $Sku_price for the output (line 201, etc)

Resources