Reporting on Microsoft 365 Licensing using PowerShell – Part 3

Doctor Scripto

Summary: Will Martin finalizes his discussion on how to report on Microsoft 365 licensing in the cloud.

Previous Posts on this article can be found here for continuity

Reporting on Microsoft 365 Licensing using PowerShell – Part 1

Reporting on Microsoft 365 Licensing using PowerShell – Part 2

Dr. Scripto here! When last we saw Will Martin, he was deep in a script pulling down licensing tables and produced a lovely hash table. Let’s watch for the exciting conclusion!

From last week, we placed our data in a hash table. Now, let’s swap it from a hash table to CSV format by changing our last active line from above. It will now be:

$OutPut += New-Object PSObject -Property $UsrSpData

And we’ll create our $OutPut variable as an array so we don’t lose something in the process:

Now, what data do we have? I’ve taken the liberty of selecting only a few fields from the list:

Yay!! Data!! 😀 But wait – what is that “FORMS*” in the second two accounts? It appears these accounts didn’t have anything in their licenses that mapped to the word “FORMS*”. Well, no matter, we can put them into the CSV anyway, can’t we? Well, yes, and no. First the yes:

This shows the CSV can be created, and you can see the empty cells (as consecutive commas) in the second and third items. However, let’s see what happens if the first account (which has the most licensed products) isn’t first anymore, by sorting on the display name:

Uh, oh … You can tell just by looking at the output that there’s a lot less data here than we had before. Now, the only fields in the CSV are ones that existed in the first account that entered the CSV – the data about our friend Irwin’s account having several other licenses we need to consider is lost to the world. So, what to do?

It took me a lot of soul searching, but I figured out that I’d need to do several things to ensure some important information didn’t get lost. First, I’d have to track all of the headers I was adding to any of the users. That’s easy – I just define a new variable, $CsvHeaders, that will keep the full list of headers, then add the following after I add the item to the hash table, so I’ve saved the header information for use later:

$CsvHeaders += $SpName

Now, to reduce confusion, I am saving this into a script that I’ll name Get-O365LicenseInfo.ps1. I’ll save it to my Scripts directory, then run it:

Hmm, forgot something, didn’t I? Previously, I’d been writing the data to the screen, since I only had to worry about a single variable. But now I have two – $Output and $CsvHeaders – that I need to keep track of, and my code saves them – but doesn’t display them. I have headers, and I have an array of hash tables – and I need to figure out how to get them into a single list. What to do …

I’ll save you about two hours of me trying to create a file with the headers, then throw the data into it, or other fruitless avenues to attack this beast – after all that work, I realized I had headers and hash tables, and I could slam them together the same way I handled the original data previously – but now my headers would be static and supplied by an array. Long story short, here’s the final code that fixed this process:

$FinalOut = @()

$OutPut | % {

$UserInfo = $_

$UserOut = @{}

$CsvHeaders | Select -Unique | % {

$UserOut.$_ += $UserInfo.$_

}

$FinalOut += New-Object PSObject -Property $UserOut

}

This gave me output where everything mapped to either empty cells or cells with data, regardless of my input. But I still needed something – notice the line above that says $CsvHeaders | Select -Unique – well, that ensures I only have individual headers for my data. Earlier, I’d been grabbing every header and adding it to my array. Early on, when I used an IF statement to check if the header already existed, I ran into a problem because it failed for some reason. So, I added this line and removed my IF test. And this will run just fine – however, it will still have names for every licensed product for every user as it goes through the script. In my demo tenant, with 35 total users, this isn’t an issue. But with 10,000 users, this variable gets to be a bit huge – OK, more like gigantic. So, I added the following line right after I added the header to my list:

$CsvHeaders = $CsvHeaders | Select -Unique

Yeah, there were other things I could do, but this worked. And now, if I run my script against my last three accounts, regardless of order, I get the same output. And with a few tweaks for ease of data access, here’s the final script:

$AllUsers = Get-MSOlUser -All $OutPut = @() [Array]$CsvHeaders = @() $PrimaryAttrs = “ObjectID”,”UPN”,”DisplayName”,”Department” forEach ($LicUser in $AllUsers) { $UserInfo = $LicUser $UsrSpData = @{ ObjectID = $UserInfo.ObjectID UPN = $UserInfo.UserPrincipalName DisplayName = $UserInfo.DisplayName Department = $UserInfo.Department }

foreach ($EachPlan in $UserInfo.Licenses) { $EachPlan.ServiceStatus | % { $SpName = $_.ServicePlan.ServiceName $SpStat = $_.ProvisioningStatus $UsrSpData.$SpName = $SpStat $CsvHeaders += $SpName $CsvHeaders = $CsvHeaders | Select -Unique }

}

#Add $UsrSpData to existing CSV table for final output

$OutPut += New-Object PSObject -Property $UsrSpData

}

#Add User attributes to header list for CSV, and sort the license headers

$CsvHeaders = $PrimaryAttrs + ($CsvHeaders | Sort) $FinalOut = @() $OutPut | % { $UserInfo = $_ $UserOut = @{} $CsvHeaders | Select -Unique | % { $UserOut.$_ += $UserInfo.$_

}

$FinalOut += New-Object PSObject -Property $UserOut

}

$FinalOut | Select $CsvHeaders

You notice this very last line – this ensures that the UPN, display name, department, and object ID are at the start of the CSV. Makes it a bit simpler to work with, IMO. You can also remove the Select statement in the first command and replace it with either -All or -MaxResults to give you the info you need – you can even put a pipe to “where IsLicensed” to only capture your licensed users.

Thanks so very much Will! This was an excellent read an interesting challenge solved with PowerShell!

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Forum. See you tomorrow. Until then, peace.

Your good friend, Doctor Scripto

PowerShell, Doctor Scripto, Will Martin, Microsoft 365, Reporting

 

1 comment

Discussion is closed. Login to edit/delete existing comments.

  • Rappold, Ken (GE Digital) 0

    Nice work. Thanks for making this series easy for a beginner (like me) to follow. Reporting for 200,000+ users takes quite a bit of time using Powershell. How can that be sped up?

Feedback usabilla icon