Powershell: get data from MsOnline

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

Harm_Veenstra