Question 105988 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Count SKU pr CompanyName in tenant with multiple CompanyNames – Powershell

(first of all, sorry if my english is a little all over the place, still learning)

So i was hoping someone had any input on how to do this. Right now im working at a tenant that has mutiple companies in it. I was hoping to automate how I would get all the SKU’s pr company, as they are billed separately etc.

right now i have the powershell-script below. that kinda gets the job done. However, its not counting and giving the best output possible for what i want.

$Report = [System.Collections.Generic.List[Object]]::new() # Create output file 
$Skus = Get-AzureADSubscribedSku | Select Sku*, ConsumedUnits 
ForEach ($Sku in $Skus) {
    Write-Host "Processing license holders for" $Sku.SkuPartNumber
    $SkuUsers = Get-AzureADUser -All $True | ? { $_.AssignedLicenses -Match $Sku.SkuId }
    ForEach ($User in $SkuUsers) {
        $ReportLine = [PSCustomObject] @{
            User        = $User.DisplayName 
            UPN         = $User.UserPrincipalName
            Companyname = $User.Companyname
            Country     = $User.Country
            SKU         = $Sku.SkuId
            SKUName     = $Sku.SkuPartNumber
        }
        $Report.Add($ReportLine) 
    }
}
$Report | Sort Companyname| Select Companyname, SKU, SKUName | Format-Table 
#or replace Format-Table with Out-GridView for easier reading

and this is working ok as i said. its just not really automated for what i want to do. after i run this i would have to count up the SKU’s pr company. from the output below.


Companyname       SKU                                       SKUName                 
-----------       ---                                       -------                 
company 01        18181a46-0d4e-45cd-891e-60aabd171b4e      STANDARDPACK        
company 01        3b555118-da6a-4418-894f-7df1e2096870      O365_BUSINESS_ESSENTIALS
company 02        3b555118-da6a-4418-894f-7df1e2096870      O365_BUSINESS_ESSENTIALS
company 02        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                       
company 02        06ebc4ee-1bb5-47dd-8120-11324bc54e06      SPE_E5                  
company 03        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                     
company 03        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                     
company 04        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                     
company 04        a403ebcc-fae0-4ca2-8c8c-7a907fd6c235      POWER_BI_STANDARD
company 04        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                     
company 04        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                     
company 04        a403ebcc-fae0-4ca2-8c8c-7a907fd6c235      POWER_BI_STANDARD

the output i would like woud be something like im illustrating below:

Companyname       SKU                                       SKUName                     Amount
-----------       ---                                       -------                     ------
company 01        18181a46-0d4e-45cd-891e-60aabd171b4e      STANDARDPACK                1
company 01        3b555118-da6a-4418-894f-7df1e2096870      O365_BUSINESS_ESSENTIALS    4
company 01        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                         2
company 02        3b555118-da6a-4418-894f-7df1e2096870      O365_BUSINESS_ESSENTIALS    5
company 02        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                         2
company 02        06ebc4ee-1bb5-47dd-8120-11324bc54e06      SPE_E5                      3
company 03        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                         2     
company 04        a403ebcc-fae0-4ca2-8c8c-7a907fd6c235      POWER_BI_STANDARD           2
company 04        cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46      SPB                         6

I might just be missing something super obv, im not sure. but any help would be very nice.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So i finally managed to do it! I'm probably not at all doing this the most 1337 way. but this is now working. its still not fully automated.

    so i will also be posting a version where i'll use graph etc. because im using this script later with a bot in teams to kinda log changes in SKUS.

    However, here is the script as it is right now. working.

    cls
#console is cleared for easier reading.

#Connects to Azure (you need the azure AD PS module to run this script btw)
#in the ms365 prompt that shows up, login with the tenant admin account.
Connect-AzureAD

#creates a list, and gathers all the info about users and SKU's etc for the tenant.
$Report = [System.Collections.Generic.List[Object]]::new()
$Skus = Get-AzureADSubscribedSku | Select Sku*, ConsumedUnits 

#formatting the table we will use in out output
ForEach ($Sku in $Skus) {
    Write-Host "Processing license holders for" $Sku.SkuPartNumber
    $SkuUsers = Get-AzureADUser -All $True | ? { $_.AssignedLicenses -Match $Sku.SkuId }
    ForEach ($User in $SkuUsers) {
        $ReportLine = [PSCustomObject] @{
            User        = $User.DisplayName 
            UPN         = $User.UserPrincipalName
            Companyname = $User.Companyname
            Country     = $User.Country
            SKU         = $Sku.SkuId
            SKUName     = $Sku.SkuPartNumber
        }
        $Report.Add($ReportLine) 
    }
}

#clearing the console again
cls

$commands = {
    #Collects and creates a small list/table with all companies within the tenant
    $firmaer = $Report | Select-Object Companyname -Unique

    #Prints the list out so you can copy the names correctly into the prompt
    write-host "Below is a list of all available companynames";
    $firmaer | Out-Host #out-host is important here to make sure it gets printed before the funtion below runs.

    #Im just setting the userimput to blank just in case
    $userinput = ""

    #Here we prompt the user for the name of the company within the tenant they want to check against.
    $userinput = Read-Host "Write the NAME of the company you want to look at: ";

    #then we Print the table with only the relevant information for that company
    write-host "Skus for: $userinput"
    $Report | Where-Object Companyname -like $userinput | Group-Object -Property SKU | Select-Object Count, Name, @{Name = 'SKUName' ; expression = { ($_.Group.SKUName) -join ', ' } } | Format-Table

    #then here we have a check if maybe the user wants to check another companyname.
    #if they respond to the prompt with y - then the script will run the commands function again from the start.
    $again = Read-Host "Do you want to check another company [ y / n ]"
    if ($again -eq "y") {
        &$commands
    }
    else { # and if not the script wil end.
        Write-Host "Exited..."    
    }
}

#calling the function.
&$commands

    this script will list all available companynames in a table for you. and then you can check each company's SKUS 1 by 1.

    the output will be a table.

    it also ask if you want to check another company ad start over after your first check.

    as i said, im planning to automate this to run through all companies you have partner in or something.

    I hope this can help someone else out aswell.


  2. 0

    Could you try using Group-Object to group by company. This fives you a count by default

    ❯ $Report | Group-Object -Property companyName

Count Name                      Group
----- ----                      -----
    2 company 01                {@{Companyname=company 01; SKU=18181a46-0d4e-45cd-891e-60aabd171b4e; SKUName=STANDARDP…
    3 company 02                {@{Companyname=company 02; SKU=3b555118-da6a-4418-894f-7df1e2096870; SKUName=O365_BUSI…
    2 company 03                {@{Companyname=company 03; SKU=cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46; SKUName=SPB}, @{C…
    5 company 04                {@{Companyname=company 04; SKU=cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46; SKUName=SPB}, @{C…
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search