(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.
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.