Powershell: get data from MsOnline

mfranhind115

Brass Contributor

Jul 15, 2022

Hi Harm!

definitive script, UGLY UGLY UGLY

but working really fine

sorted by columns (the same license in the same column)

it is:

$Total = @()
$lic1 = "myt:EXCHANGESTANDARD"
$lic2 = "myt:EXCHANGEENTERPRISE"
$lic3 = "myt:O365_BUSINESS"
$lic4 = "myt:O365_BUSINESS_ESSENTIALS"
$lic5 = "myt:O365_BUSINESS_PREMIUM"
$lic6 = "myt:SPB"
$lic7 = "myt:ATP_ENTERPRISE"
$lic8 = "myt:POWER_BI_PRO"
$LicensesList = ($lic1,$lic2,$lic3,$lic4,$lic5,$lic6,$lic7,$lic8)

$SKUfriendlyname0 = ""
$SKUfriendlyname1 = ""
$SKUfriendlyname2 = ""
$SKUfriendlyname3 = ""
$SKUfriendlyname4 = ""
$SKUfriendlyname5 = ""
$SKUfriendlyname6 = ""
$SKUfriendlyname7 = ""

$SKUfriendlyname0b = ""
$SKUfriendlyname1b = ""
$SKUfriendlyname2b = ""
$SKUfriendlyname3b = ""
$SKUfriendlyname4b = ""
$SKUfriendlyname5b = ""
$SKUfriendlyname6b = ""
$SKUfriendlyname7b = ""

$col1 = ""
$col2 = ""
$col3 = ""
$col4 = ""
$col5 = ""
$col6 = ""
$col7 = ""
$col8 = ""

$skucsv = Import-Csv -Path c:\temp\licensing.csv

foreach ($user in Get-MsolUser -All | Sort-Object UserPrincipalName) {

	$SKUfriendlyname0 = ""
	$SKUfriendlyname1 = ""
	$SKUfriendlyname2 = ""
	$SKUfriendlyname3 = ""
	$SKUfriendlyname4 = ""
	$SKUfriendlyname5 = ""
	$SKUfriendlyname6 = ""
	$SKUfriendlyname7 = ""
	
	$SKUfriendlyname0b = ""
	$SKUfriendlyname1b = ""
	$SKUfriendlyname2b = ""
	$SKUfriendlyname3b = ""
	$SKUfriendlyname4b = ""
	$SKUfriendlyname5b = ""
	$SKUfriendlyname6b = ""
	$SKUfriendlyname7b = ""

	$col1 = ""
	$col2 = ""
	$col3 = ""
	$col4 = ""
	$col5 = ""
	$col6 = ""
	$col7 = ""
	$col8 = ""

    if ($user.isLicensed -eq $True) {	

		$UPN = $user.UserPrincipalName
		$startpos = $UPN.IndexOf("@")
		$length = $UPN.length - $startpos
		
		<#
		Write-Output $UPN
		Write-Output $startpos
		Write-Output $length
		Write-Output $domainstr
		#>
		
		$domainstr = $UPN.substring($startpos, $length)

        if ($user.Licenses[0].AccountSkuId) { $SKUfriendlyname0 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[0].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[1].AccountSkuId) { $SKUfriendlyname1 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[1].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[2].AccountSkuId) { $SKUfriendlyname2 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[2].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[3].AccountSkuId) { $SKUfriendlyname3 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[3].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[4].AccountSkuId) { $SKUfriendlyname4 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[4].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[5].AccountSkuId) { $SKUfriendlyname5 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[5].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[6].AccountSkuId) { $SKUfriendlyname6 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[6].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[7].AccountSkuId) { $SKUfriendlyname7 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[7].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[8].AccountSkuId) { $SKUfriendlyname8 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[8].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[9].AccountSkuId) { $SKUfriendlyname9 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[9].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[10].AccountSkuId) { $SKUfriendlyname10 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[10].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[11].AccountSkuId) { $SKUfriendlyname11 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[11].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
    
		if ($LicensesList.contains($user.Licenses[0].AccountSkuId) -Or $LicensesList.contains($user.Licenses[1].AccountSkuId) -Or $LicensesList.contains($user.Licenses[2].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[3].AccountSkuId) -Or $LicensesList.contains($user.Licenses[4].AccountSkuId) -Or $LicensesList.contains($user.Licenses[5].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[6].AccountSkuId) -Or $LicensesList.contains($user.Licenses[7].AccountSkuId) -Or $LicensesList.contains($user.Licenses[8].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[9].AccountSkuId) -Or $LicensesList.contains($user.Licenses[10].AccountSkuId) -Or $LicensesList.contains($user.Licenses[11].AccountSkuId) ) {
			
			if ($LicensesList.contains($user.Licenses[0].AccountSkuId)) {
				$SKUfriendlyname0b = $SKUfriendlyname0
			} else {$SKUfriendlyname0b = ""} 

			if ($LicensesList.contains($user.Licenses[1].AccountSkuId)) {
				$SKUfriendlyname1b = $SKUfriendlyname1
			} else {$SKUfriendlyname1b = ""} 
		
			if ($LicensesList.contains($user.Licenses[2].AccountSkuId)) {
				$SKUfriendlyname2b = $SKUfriendlyname2
			} else {$SKUfriendlyname2b = ""} 
			
			if ($LicensesList.contains($user.Licenses[3].AccountSkuId)) {
				$SKUfriendlyname3b = $SKUfriendlyname3
			} else {$SKUfriendlyname3b = ""} 
			
			if ($LicensesList.contains($user.Licenses[4].AccountSkuId)) {
				$SKUfriendlyname4b = $SKUfriendlyname4
			} else {$SKUfriendlyname4b = ""} 
			
			if ($LicensesList.contains($user.Licenses[5].AccountSkuId)) {
				$SKUfriendlyname5b = $SKUfriendlyname5
			} else {$SKUfriendlyname5b = ""} 
			
			if ($LicensesList.contains($user.Licenses[6].AccountSkuId)) {
				$SKUfriendlyname6b = $SKUfriendlyname6
			} else {$SKUfriendlyname6b = ""} 
			
			if ($LicensesList.contains($user.Licenses[7].AccountSkuId)) {
				$SKUfriendlyname7b = $SKUfriendlyname7
			} else {$SKUfriendlyname7b = ""} 
			
			if ($LicensesList.contains($user.Licenses[8].AccountSkuId)) {
				$SKUfriendlyname8b = $SKUfriendlyname8
			} else {$SKUfriendlyname8b = ""} 
			
			if ($LicensesList.contains($user.Licenses[9].AccountSkuId)) {
				$SKUfriendlyname9b = $SKUfriendlyname9
			} else {$SKUfriendlyname9b = ""} 
			
			if ($LicensesList.contains($user.Licenses[10].AccountSkuId)) {
				$SKUfriendlyname10b = $SKUfriendlyname10
			} else {$SKUfriendlyname10b = ""} 
			
			if ($LicensesList.contains($user.Licenses[11].AccountSkuId)) {
				$SKUfriendlyname11b = $SKUfriendlyname11
			} else {$SKUfriendlyname11b = ""}  

			
			if ($user.Licenses[0].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[0].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[1].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[1].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[2].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[2].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[3].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[3].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[4].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[4].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[5].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[5].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[6].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[6].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[7].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[7].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[8].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[8].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[9].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[9].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[10].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[10].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}

			if ($user.Licenses[11].AccountSkuId -eq $lic1) {
				$col1 = $lic1
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic2) {
				$col2 = $lic2
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic3) {
				$col3 = $lic3
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic4) {
				$col4 = $lic4
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic5) {
				$col5 = $lic5
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic6) {
				$col6 = $lic6
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic7) {
				$col7 = $lic7
			} elseif ($user.Licenses[11].AccountSkuId -eq $lic8) {
				$col8 = $lic8
			}
			
			$licenseinfo = [PSCustomObject]@{
				domainstr		 = $domainstr
				User             = $user.UserPrincipalName
				DisplayName      = $user.DisplayName		
				
				<#
				SKUfriendlyname0 = $SKUfriendlyname0b
				SKUfriendlyname1 = $SKUfriendlyname1b
				SKUfriendlyname2 = $SKUfriendlyname2b
				SKUfriendlyname3 = $SKUfriendlyname3b
				SKUfriendlyname4 = $SKUfriendlyname4b
				SKUfriendlyname5 = $SKUfriendlyname5b
				SKUfriendlyname6 = $SKUfriendlyname6b
				SKUfriendlyname7 = $SKUfriendlyname7b
				#>
				
				SKUfriendlyname0 = $col1
				SKUfriendlyname1 = $col2
				SKUfriendlyname2 = $col3
				SKUfriendlyname3 = $col4
				SKUfriendlyname4 = $col5
				SKUfriendlyname5 = $col6
				SKUfriendlyname6 = $col7
				SKUfriendlyname7 = $col8				
			}
			$Total += $licenseinfo	   

		}				
    }
}

$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv
get-content c:\temp\users.csv

mfranhind115
Brass Contributor
Jul 15, 2022
Harm_Veenstra
Harm_Veenstra
MVP
Jul 15, 2022
Nice, expanding on my ugly script 😛

mfranhind115

Brass Contributor

Jul 15, 2022

Hi @Harm_Veenstra

DONE!!!

really ugly but working!

now I have only to put a specific license always under the same column...

this is:

$Total = @()
$LicensesList = ('mytenant:EXCHANGESTANDARD','mytenant:EXCHANGEENTERPRISE','mytenant:O365_BUSINESS','mytenant:O365_BUSINESS_ESSENTIALS','mytenant:O365_BUSINESS_PREMIUM','mytenant:SPB','mytenant:ATP_ENTERPRISE','mytenant:POWER_BI_PRO')

$skucsv = Import-Csv -Path c:\temp\licensing.csv
foreach ($user in Get-MsolUser -DomainName mydomain.com | Sort-Object UserPrincipalName) {

    if ($user.isLicensed -eq $True) {	
		
        if ($user.Licenses[0].AccountSkuId) { $SKUfriendlyname0 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[0].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[1].AccountSkuId) { $SKUfriendlyname1 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[1].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[2].AccountSkuId) { $SKUfriendlyname2 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[2].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[3].AccountSkuId) { $SKUfriendlyname3 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[3].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[4].AccountSkuId) { $SKUfriendlyname4 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[4].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[5].AccountSkuId) { $SKUfriendlyname5 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[5].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[6].AccountSkuId) { $SKUfriendlyname6 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[6].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[7].AccountSkuId) { $SKUfriendlyname7 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[7].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[8].AccountSkuId) { $SKUfriendlyname8 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[8].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[9].AccountSkuId) { $SKUfriendlyname9 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[9].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[10].AccountSkuId) { $SKUfriendlyname10 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[10].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[11].AccountSkuId) { $SKUfriendlyname11 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[11].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
    
		if ($LicensesList.contains($user.Licenses[0].AccountSkuId) -Or $LicensesList.contains($user.Licenses[1].AccountSkuId) -Or $LicensesList.contains($user.Licenses[2].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[3].AccountSkuId) -Or $LicensesList.contains($user.Licenses[4].AccountSkuId) -Or $LicensesList.contains($user.Licenses[5].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[6].AccountSkuId) -Or $LicensesList.contains($user.Licenses[7].AccountSkuId) -Or $LicensesList.contains($user.Licenses[8].AccountSkuId) -Or `
			$LicensesList.contains($user.Licenses[9].AccountSkuId) -Or $LicensesList.contains($user.Licenses[10].AccountSkuId) -Or $LicensesList.contains($user.Licenses[11].AccountSkuId) ) {

			if ($LicensesList.contains($user.Licenses[0].AccountSkuId)) {
				$SKUfriendlyname0b = $SKUfriendlyname0
			} else {$SKUfriendlyname0b = ""} 

			if ($LicensesList.contains($user.Licenses[1].AccountSkuId)) {
				$SKUfriendlyname1b = $SKUfriendlyname1
			} else {$SKUfriendlyname1b = ""} 
			
			if ($LicensesList.contains($user.Licenses[2].AccountSkuId)) {
				$SKUfriendlyname2b = $SKUfriendlyname2
			} else {$SKUfriendlyname2b = ""} 
			
			if ($LicensesList.contains($user.Licenses[3].AccountSkuId)) {
				$SKUfriendlyname3b = $SKUfriendlyname3
			} else {$SKUfriendlyname3b = ""} 
			
			if ($LicensesList.contains($user.Licenses[4].AccountSkuId)) {
				$SKUfriendlyname4b = $SKUfriendlyname4
			} else {$SKUfriendlyname4b = ""} 
			
			if ($LicensesList.contains($user.Licenses[5].AccountSkuId)) {
				$SKUfriendlyname5b = $SKUfriendlyname5
			} else {$SKUfriendlyname5b = ""} 
			
			if ($LicensesList.contains($user.Licenses[6].AccountSkuId)) {
				$SKUfriendlyname6b = $SKUfriendlyname6
			} else {$SKUfriendlyname6b = ""} 
			
			if ($LicensesList.contains($user.Licenses[7].AccountSkuId)) {
				$SKUfriendlyname7b = $SKUfriendlyname7
			} else {$SKUfriendlyname7b = ""} 
			
			if ($LicensesList.contains($user.Licenses[8].AccountSkuId)) {
				$SKUfriendlyname8b = $SKUfriendlyname8
			} else {$SKUfriendlyname8b = ""} 
			
			if ($LicensesList.contains($user.Licenses[9].AccountSkuId)) {
				$SKUfriendlyname9b = $SKUfriendlyname9
			} else {$SKUfriendlyname9b = ""} 
			
			if ($LicensesList.contains($user.Licenses[10].AccountSkuId)) {
				$SKUfriendlyname10b = $SKUfriendlyname10
			} else {$SKUfriendlyname10b = ""} 
			
			if ($LicensesList.contains($user.Licenses[11].AccountSkuId)) {
				$SKUfriendlyname11b = $SKUfriendlyname11
			} else {$SKUfriendlyname11b = ""}  
			
			$licenseinfo = [PSCustomObject]@{
				User             = $user.UserPrincipalName
				DisplayName      = $user.DisplayName		
				SKUfriendlyname0 = $SKUfriendlyname0b
				SKUfriendlyname1 = $SKUfriendlyname1b
				SKUfriendlyname2 = $SKUfriendlyname2b
				SKUfriendlyname3 = $SKUfriendlyname3b
				SKUfriendlyname4 = $SKUfriendlyname4b
				SKUfriendlyname5 = $SKUfriendlyname5b
				SKUfriendlyname6 = $SKUfriendlyname6b
				SKUfriendlyname7 = $SKUfriendlyname7b
				SKUfriendlyname8 = $SKUfriendlyname8b
				SKUfriendlyname9 = $SKUfriendlyname9b
				SKUfriendlyname10 = $SKUfriendlyname10b
				SKUfriendlyname11 = $SKUfriendlyname11b
			}
			$Total += $licenseinfo	   

		}					
    }
}

$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv
get-content c:\temp\users.csv

mfranhind115
Brass Contributor
Jul 15, 2022
this output still put in a single column the licenses...

But, since I'm only interested to some licenses (not everything but only few and used, see the beginning of this thread), do you think it could help to make a limited list and, only if the sku is included there, get it out on output?
Harm_Veenstra
MVP
Jul 14, 2022
And I see it has double entries.. Hmmm... Difficult 🙂

Harm_Veenstra

MVP

Jul 14, 2022

mfranhind115 Problem is that you don't know how many colums you need, not all users have the same amount of licenses. And because of that, you don't know the amount of colums needed for every user.

But... I made this which does work but I'm not happy about it 😉 It supports up to 9 SKU's per user 😛

$Total = @()
$skucsv = Import-Csv -Path d:\temp\licensing.csv
foreach ($user in Get-MsolUser | Sort-Object UserPrincipalName) {
    if ($user.isLicensed -eq $True) {	
        if ($user.Licenses[0].AccountSkuId) { $SKUfriendlyname0 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[0].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[1].AccountSkuId) { $SKUfriendlyname1 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[1].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[2].AccountSkuId) { $SKUfriendlyname2 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[2].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[3].AccountSkuId) { $SKUfriendlyname3 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[3].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[4].AccountSkuId) { $SKUfriendlyname4 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[4].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[5].AccountSkuId) { $SKUfriendlyname5 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[5].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[6].AccountSkuId) { $SKUfriendlyname6 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[6].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[7].AccountSkuId) { $SKUfriendlyname7 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[7].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[8].AccountSkuId) { $SKUfriendlyname8 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[8].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        if ($user.Licenses[9].AccountSkuId) { $SKUfriendlyname9 = ($skucsv | Where-Object String_Id -Contains $user.Licenses[9].AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1).Product_Display_Name }
        
        $licenseinfo = [PSCustomObject]@{
            User             = $user.UserPrincipalName
            DisplayName      = $user.DisplayName		
            SKUfriendlyname0 = $SKUfriendlyname0
            SKUfriendlyname1 = $SKUfriendlyname1
            SKUfriendlyname2 = $SKUfriendlyname2
            SKUfriendlyname3 = $SKUfriendlyname3
            SKUfriendlyname4 = $SKUfriendlyname4
            SKUfriendlyname5 = $SKUfriendlyname5
            SKUfriendlyname6 = $SKUfriendlyname6
            SKUfriendlyname7 = $SKUfriendlyname7
            SKUfriendlyname8 = $SKUfriendlyname8
            SKUfriendlyname9 = $SKUfriendlyname9
        }
        $Total += $licenseinfo	   
    }
}


$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path d:\temp\users.csv
get-content d:\temp\users.csv

Output:

"User";"DisplayName";"SKUfriendlyname0";"SKUfriendlyname1";"SKUfriendlyname2";"SKUfriendlyname3";"SKUfriendlyname4";"SKUfriendlyname5";"SKUfriendlyname6";"SKUfriendlyname7";"SKUfriendlyname8";"SKUfriendlyname9"
"email address removed for privacy reasons";"MOD Administrator";"Power BI (free)";"MICROSOFT FLOW FREE";"Microsoft 365 E5 Compliance";"WINDOWS 10 ENTERPRISE E3";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Alex Wilber";"Power BI (free)";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Allan Deyoung";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Christie Cline";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Debra Berger";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Diego Siciliani";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Grady Archie";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Irvin Sayers";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Isaiah Langer";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Johanna Lorenz";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Joni Sherman";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Lee Gu";"WINDOWS 10 ENTERPRISE E3";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Lidia Holloway";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Lynne Robbins";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Megan Bowen";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Miriam Graham";"WINDOWS 10 ENTERPRISE E3";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Nestor Wilke";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Patti Fernandez";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Pradeep Gupta";"Microsoft 365 E5 Compliance";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;
"email address removed for privacy reasons";"Test Unlicensed User";"Power BI (free)";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E5";"Office 365 E5";"ENTERPRISE MOBILITY + SECURITY E5";"Office 365 E3";"Office 365 E5";;;

mfranhind115

Brass Contributor

Jul 14, 2022

mfranhind115

Hi Harm,

almost done...

$Total = @()

$skucsv = Import-Csv -Path c:\temp\licensing.csv

foreach ($user in Get-MsolUser -DomainName mydomain.com | Sort-Object UserPrincipalName) {
	
	$Lic = ""

    if ($user.isLicensed -eq $True) {	
	
        foreach ($License in $User.licenses) {
            $SKUfriendlyname = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1
			$Lic = $Lic + $SKUfriendlyname + ';'
		}
		
		$Licenses = [PSCustomObject]@{
			User        = $user.UserPrincipalName
			DisplayName = $user.DisplayName		
			LicenseSKU  = $Lic.replace('@{Product_Display_Name=','').replace('}','')
		}
		
		$Total += $Licenses	   
		
    }
}

$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv

but my output is like this:

"User";"DisplayName";"LicenseSKU"
"mailbox address 1";"name1 surname1";"MICROSOFT 365 BUSINESS STANDARD;Microsoft Defender for Office 365 (Plan 1);"
"mailbox address 2";"name2 surname2";"MICROSOFT 365 BUSINESS BASIC;Microsoft Defender for Office 365 (Plan 1);"

then, the license strings are not really "separated" by delimiter, and then they are not seen as 2 different columns... but 1 single column...

any idea?

mfranhind115
Brass Contributor
Jul 14, 2022
Excellent Harm_Veenstra, it works now!
just still not getting on a single row all the info for the user

If you have some idea you are welcome, I go ahead struggling...
😉

here what I'm getting:

Harm_Veenstra

MVP

Jul 14, 2022

mfranhind115 Something like this? (Didn't test it, but shortend it)

$Plans = @()
$Total = @()

$skucsv = Import-Csv -Path c:\temp\licensing.csv

foreach ($user in Get-MsolUser -DomainName mydomain.com | Sort-Object UserPrincipalName) {
	
    if ($user.isLicensed -eq $True) {		
        foreach ($License in $User.licenses) {
            $SKUfriendlyname = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1
            $SKUserviceplan = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Sort-Object Service_Plans_Included_Friendly_Names
            foreach ($serviceplan in $SKUserviceplan) {
                $Licenses = [PSCustomObject]@{
                    User        = $user.UserPrincipalName
                    DisplayName = $user.DisplayName		
                    LicenseSKU  = $SKUfriendlyname.Product_Display_Name
                    Serviceplan = $serviceplan.Service_Plans_Included_Friendly_Names
                }
                $Plans += $Licenses	
            }
            $total += $plans			
        }			
    }
}

$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv

mfranhind115

Brass Contributor

Jul 14, 2022

Hi Harm_Veenstra ,
really apologize but I'm still struggling with that.

I started from your suggestions but I'm now stuck.

I wrote this:

$Users = @()
$Plans = @()
$GlobalPlans = @()
$Total = @()

$skucsv = Import-Csv -Path c:\temp\licensing.csv

foreach ($user in Get-MsolUser -DomainName mydomain.com | Sort-Object UserPrincipalName) {
	
    if ($user.isLicensed -eq $True) {
   
		$info1 = [PSCustomObject]@{
		
			User        = $user.UserPrincipalName
			DisplayName = $user.DisplayName
		}
			
		$Users += $info1 
				
		foreach ($License in $User.licenses) {
			
			$SKUfriendlyname = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Select-Object Product_Display_Name -First 1
			$SKUserviceplan = $skucsv | Where-Object String_Id -Contains $License.AccountSkuId.Split(':')[1] | Sort-Object Service_Plans_Included_Friendly_Names
					
			foreach ($serviceplan in $SKUserviceplan) {
				
				$Licenses = [PSCustomObject]@{
							
					LicenseSKU  = $SKUfriendlyname.Product_Display_Name
					Serviceplan = $serviceplan.Service_Plans_Included_Friendly_Names
					
				}
						
				$Plans += $Licenses
					
			}
							
		}

		$GlobalPlans += $Plans
				
    }

	$Total += $Users + $GlobalPlans
	
}

$Total | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv

but I only get UPN and display name, not the licenses on the same row...

Please, be patient, where I'm wrong?

thanks

Harm_Veenstra
MVP
Jul 14, 2022
You can do that, this was just an example of license gathering. You can start with something like this?

$Users = @()
foreach ($user in Get-MsolUser -All | Sort-Object UserPrincipalName) {
$info = [PSCustomObject]@{
User = $User.UserPrincipalName
DisplayName = $User.DisplayName
}
$Users += $info
}

$Users | Sort-Object User | Export-Csv -NoTypeInformation -Delimiter ';' -Encoding UTF8 -Path c:\temp\users.csv

Forum Discussion

Resources