{"id":86170,"date":"2019-08-07T03:00:08","date_gmt":"2019-08-07T11:00:08","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/scripting\/?p=86170"},"modified":"2019-08-01T03:36:55","modified_gmt":"2019-08-01T11:36:55","slug":"reporting-on-microsoft-365-licensing-using-powershell-part-2","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/reporting-on-microsoft-365-licensing-using-powershell-part-2\/","title":{"rendered":"Reporting on Microsoft 365 Licensing using PowerShell \u2013 Part 2"},"content":{"rendered":"

Summary<\/strong>: Will Martin continues his discussion on how to report on Microsoft 365 licensing in the cloud.<\/p>\n

Previous Posts on this article can be found here for continuity<\/p>\n

Reporting on Microsoft 365 Licensing using PowerShell \u2013 Part 1<\/a><\/p>\n

Remember from last week we showed the results of what a user licensed in Microsoft 365 looked like in the web portal?<\/p>\n

<\/p>\n

So, what does this look like if we try to access it in PowerShell?<\/p>\n

Well, we have the user, and the service plan. Can we get this into a usable format? Well, let\u2019s see what we can do \u2013 let\u2019s try pulling this info for our last three users and see what we get:<\/p>\n

<\/p>\n

Hmm, nothing in the service status field \u2013 what gives? Oh, that\u2019s right, service status is a property of the Licenses. Let\u2019s see what we get that way:<\/p>\n

<\/p>\n

OK, still nothing \u2013 wait, wasn\u2019t 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\u2019s see \u2013 what if we loop through the licenses, and write data for each SKU? We\u2019re getting close to needing a script, but let\u2019s see if we can still do this in a single line command. OK, the command we finally came up with looks like this:<\/p>\n

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’}<\/strong><\/p>\n

Broken into a script, it runs as follows:<\/p>\n

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

And running it gives us:<\/p>\n

<\/p>\n

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

<\/p>\n

So if we\u2019re putting things into a CSV, this isn\u2019t quite as useful as we\u2019d want it to be \u2013 we would probably want each service and its status, but we can\u2019t have a list of services without first asking the system for them.<\/p>\n

Well, we\u2019re 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. \u201cHow the heck can we add something we don\u2019t know the name of?\u201d you ask \u2026 Well, an interesting thing about hash tables is that you can do things with them that I bet you haven\u2019t thought of before. For instance, if I run the following command, I create a hash table with nothing in it.<\/p>\n

<\/p>\n

And we can add items to it very simply \u2013 by just telling the system we\u2019re using them \u2013 no, not all the declaration stuff, but just by referencing them. To borrow from Bill Nye, \u201cConsider the following \u2026\u201d<\/p>\n

<\/p>\n

In this little session, we see the hash table being created, and we see proof it\u2019s not \u201cnothing\u201d (it doesn\u2019t equal $Null). Then, we set the $HashTable.MyField field equal to a string of text. And before that time, that field didn\u2019t exist. This will come in handy, but now we\u2019re 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:<\/p>\n

<\/p>\n

We started by setting up a variable named $NumFld. We gave it a text value of \u2018MyNumber\u2019. 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\u2019s go back to our problem and see how we can use this information.<\/p>\n

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\u2019s go back to our $DemoUser and see where this leads. Now, I\u2019m going to tell you up front that the status comes through fine, but if we look at the name, it\u2019s not quite as simple. Looking just at our first service, we see this:<\/p>\n

<\/p>\n

But if we go a step further and ask for the service plan, we get:<\/p>\n

<\/p>\n

So, it looks like the name is actually the ServiceName of the ServicePlan. Easy enough \u2013 let\u2019s see what we can do with this. I\u2019ll create an empty hash table, $DemoHash, then try to add the service plan information to it:<\/p>\n

<\/p>\n

OK. No errors, that\u2019s good. But what\u2019s in the hash table?<\/p>\n

<\/p>\n

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\u2019ll need a script:<\/p>\n

forEach ($LicUser in $AllUsers)\n{\n<\/strong>$UserInfo = $LicUser;\n<\/strong>$UsrSpData = @{\n<\/strong>ObjectID = $UserInfo.ObjectID\n<\/strong>UPN = $UserInfo.UserPrincipalName\n<\/strong>DisplayName = $UserInfo.DisplayName\n<\/strong>Department = $UserInfo.Department<\/strong>}<\/strong><\/p>\n

foreach ($EachPlan in $UserInfo.Licenses)\n{\n<\/strong>$EachPlan.ServiceStatus | % {\n<\/strong>$SpName = $_.ServicePlan.ServiceName\n<\/strong>$SpStat = $_.ProvisioningStatus\n<\/strong>$UsrSpData.$SpName = $SpStat<\/strong>}\n<\/strong>}<\/strong><\/p>\n

$UsrSpData<\/strong><\/p>\n

}<\/strong><\/p>\n

First, I\u2019ve loaded our last three accounts into a variable, $AllUsers:<\/p>\n

<\/p>\n

Now, we run our command \u2013 again, it\u2019s a single-line command, since that\u2019s what we\u2019d done up to now. Ignore the line wrapping, as before \u2013 I promise it\u2019s about the same commands as the listing above:<\/p>\n

<\/p>\n

Come back next Wednesday as Clive shows us the next level of work with the Hash Table produced!<\/p>\n

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

Your good friend, Doctor Scripto<\/p>\n

PowerShell, Doctor Scripto, Will Martin, Microsoft 365, Reporting<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u2013 Part 1 Remember from last week we showed the results of what a user licensed in Microsoft 365 looked […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1739,1775,1738],"tags":[1740,1777,377,1778,1834],"class_list":["post-86170","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-doctor-scripto","category-microsoft-365","category-powershell","tag-doctor-scripto","tag-microsoft-365","tag-powershell","tag-reporting","tag-will-martin"],"acf":[],"blog_post_summary":"

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 \u2013 Part 1 Remember from last week we showed the results of what a user licensed in Microsoft 365 looked […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=86170"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86170\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=86170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=86170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=86170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}