Reporting on Microsoft 365 Licensing using PowerShell – Part 2

Doctor Scripto

Summary: Will Martin continues 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

Remember from last week we showed the results of what a user licensed in Microsoft 365 looked like in the web portal?

So, what does this look like if we try to access it in PowerShell?

Well, we have the user, and the service plan. Can we get this into a usable format? Well, let’s see what we can do – let’s try pulling this info for our last three users and see what we get:

Hmm, nothing in the service status field – what gives? Oh, that’s right, service status is a property of the Licenses. Let’s see what we get that way:

OK, still nothing – wait, wasn’t Licenses an array? How do we pull the array info? And what if we have more than one SKU we might need to pull from? Let’s see – what if we loop through the licenses, and write data for each SKU? We’re getting close to needing a script, but let’s see if we can still do this in a single line command. OK, the command we finally came up with looks like this:

Get-MsolUser | Select -last 3 | % { $DName = $_.DisplayName ; $UPName = $_.UserPrincipalName; $Lic = $_.Licenses; $Lic | % { $_ } } | Select @{E={ $DName };l=’DisplayName’}, @{E={ $UPName };l=’UserPrincipleName’}, @{E={ $_.ServiceStatus };l=’ServiceStatus’}

Broken into a script, it runs as follows:

Get-MsolUser | Select -last 3 | % { $DName = $_.DisplayName $UPName = $_.UserPrincipalName $Lic = $_.Licenses $Lic | % { $_ } } | Select @{E={ $DName };l=’DisplayName’}, @{E={ $UPName };l=’UserPrincipleName’}, @{E={ $_.ServiceStatus };l=’ServiceStatus’}

And running it gives us:

Sorry, the print is smaller, but I had to open the window a bit so you could see the bad news – our service status is also multi-valued, giving us the result of “Microsoft.Online.Administration.ServiceStatus”, which is useful to no one I can think of. And something else we see – our first user is listed twice. This is because he has licenses from two SKUs:

So if we’re putting things into a CSV, this isn’t quite as useful as we’d want it to be – we would probably want each service and its status, but we can’t have a list of services without first asking the system for them.

Well, we’re in luck. PowerShell on my Windows 10 system (5.1.nnnn) allows us to put things into a hash table without knowing the names of the fields we are adding. “How the heck can we add something we don’t know the name of?” you ask … Well, an interesting thing about hash tables is that you can do things with them that I bet you haven’t thought of before. For instance, if I run the following command, I create a hash table with nothing in it.

And we can add items to it very simply – by just telling the system we’re using them – no, not all the declaration stuff, but just by referencing them. To borrow from Bill Nye, “Consider the following …”

In this little session, we see the hash table being created, and we see proof it’s not “nothing” (it doesn’t equal $Null). Then, we set the $HashTable.MyField field equal to a string of text. And before that time, that field didn’t exist. This will come in handy, but now we’re going to make it even more useful. What if we use a variable for the name of our field? See if you can follow this thread:

We started by setting up a variable named $NumFld. We gave it a text value of ‘MyNumber’. Then we set $HashTable.$NumFld equal to a number and show the full hashtable to prove we got what we expected. Well, how nifty can that be!? So, let’s go back to our problem and see how we can use this information.

We know that each Office 365 service or application has a name and a status. What if we plop this name and status into a variable that we then add to a hash table? Let’s go back to our $DemoUser and see where this leads. Now, I’m going to tell you up front that the status comes through fine, but if we look at the name, it’s not quite as simple. Looking just at our first service, we see this:

But if we go a step further and ask for the service plan, we get:

So, it looks like the name is actually the ServiceName of the ServicePlan. Easy enough – let’s see what we can do with this. I’ll create an empty hash table, $DemoHash, then try to add the service plan information to it:

OK. No errors, that’s good. But what’s in the hash table?

Bingo! Just what the doctor ordered. Now, what do I get if I run this for my last three accounts, like before? And for this, we’ll need a script:

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} }



First, I’ve loaded our last three accounts into a variable, $AllUsers:

Now, we run our command – again, it’s a single-line command, since that’s what we’d done up to now. Ignore the line wrapping, as before – I promise it’s about the same commands as the listing above:

Come back next Wednesday as Clive shows us the next level of work with the Hash Table produced!

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at, 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



Discussion is closed.

Feedback usabilla icon