{"id":86192,"date":"2019-08-14T03:00:03","date_gmt":"2019-08-14T11:00:03","guid":{"rendered":"http:\/\/devblogs.microsoft.com\/scripting\/?p=86192"},"modified":"2019-09-04T07:12:19","modified_gmt":"2019-09-04T15:12:19","slug":"reporting-on-microsoft-365-licensing-using-powershell-part-3","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/reporting-on-microsoft-365-licensing-using-powershell-part-3\/","title":{"rendered":"Reporting on Microsoft 365 Licensing using PowerShell \u2013 Part 3"},"content":{"rendered":"

Summary<\/strong>: Will Martin finalizes 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

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

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\u2019s watch for the exciting conclusion!<\/p>\n

From last week, we placed our data in a hash table. Now, let\u2019s swap it from a hash table to CSV format by changing our last active line from above. It will now be:<\/p>\n

$OutPut += New-Object PSObject -Property $UsrSpData<\/strong><\/p>\n

And we\u2019ll create our $OutPut variable as an array so we don\u2019t lose something in the process:<\/p>\n

<\/p>\n

Now, what data do we have? I\u2019ve taken the liberty of selecting only a few fields from the list:<\/p>\n

<\/p>\n

Yay!! Data!! \ud83d\ude00 But wait \u2013 what is that \u201cFORMS*\u201d in the second two accounts? It appears these accounts didn\u2019t have anything in their licenses that mapped to the word \u201cFORMS*\u201d. Well, no matter, we can put them into the CSV anyway, can\u2019t we? Well, yes, and no. First the yes:<\/p>\n

<\/p>\n

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\u2019s see what happens if the first account (which has the most licensed products) isn\u2019t first anymore, by sorting on the display name:<\/p>\n

<\/p>\n

Uh, oh \u2026 You can tell just by looking at the output that there\u2019s 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 \u2013 the data about our friend Irwin\u2019s account having several other licenses we need to consider is lost to the world. So, what to do?<\/p>\n

It took me a lot of soul searching, but I figured out that I\u2019d need to do several things to ensure some important information didn\u2019t get lost. First, I\u2019d have to track all of the headers I was adding to any of the users. That\u2019s easy \u2013 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\u2019ve saved the header information for use later:<\/p>\n

$CsvHeaders += $SpName<\/strong><\/p>\n

Now, to reduce confusion, I am saving this into a script that I\u2019ll name Get-O365LicenseInfo.ps1. I\u2019ll save it to my Scripts directory, then run it:<\/p>\n

<\/p>\n

Hmm, forgot something, didn\u2019t I? Previously, I\u2019d been writing the data to the screen, since I only had to worry about a single variable. But now I have two \u2013 $Output and $CsvHeaders \u2013 that I need to keep track of, and my code saves them \u2013 but doesn\u2019t display them. I have headers, and I have an array of hash tables \u2013 and I need to figure out how to get them into a single list. What to do \u2026<\/p>\n

I\u2019ll 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 \u2013 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 \u2013 but now my headers would be static and supplied by an array. Long story short, here\u2019s the final code that fixed this process:<\/p>\n

$FinalOut = @()<\/strong><\/p>\n

$OutPut | % {<\/strong><\/p>\n

$UserInfo = $_<\/strong><\/p>\n

$UserOut = @{}<\/strong><\/p>\n

$CsvHeaders | Select -Unique | % {<\/strong><\/p>\n

$UserOut.$_ += $UserInfo.$_<\/strong><\/p>\n

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

$FinalOut += New-Object PSObject -Property $UserOut<\/strong><\/p>\n

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

This gave me output where everything mapped to either empty cells or cells with data, regardless of my input. But I still needed something \u2013 notice the line above that says $CsvHeaders | Select -Unique \u2013 well, that ensures I only have individual headers for my data. Earlier, I\u2019d 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 \u2013 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\u2019t an issue. But with 10,000 users, this variable gets to be a bit huge \u2013 OK, more like gigantic. So, I added the following line right after I added the header to my list:<\/p>\n

$CsvHeaders = $CsvHeaders | Select -Unique<\/strong><\/p>\n

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\u2019s the final script:<\/p>\n

$AllUsers = Get-MSOlUser -All\n<\/strong>$OutPut = @()\n<\/strong>[Array]$CsvHeaders = @()\n<\/strong>$PrimaryAttrs = “ObjectID”,”UPN”,”DisplayName”,”Department”\n<\/strong>forEach ($LicUser in $AllUsers) {\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\n<\/strong>}<\/strong><\/p>\n

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

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

#Add $UsrSpData to existing CSV table for final output<\/strong><\/p>\n

$OutPut += New-Object PSObject -Property $UsrSpData<\/strong><\/p>\n

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

#Add User attributes to header list for CSV, and sort the license headers<\/strong><\/p>\n

$CsvHeaders = $PrimaryAttrs + ($CsvHeaders | Sort)\n<\/strong>$FinalOut = @()\n<\/strong>$OutPut | % {\n<\/strong>$UserInfo = $_\n<\/strong>$UserOut = @{}\n<\/strong>$CsvHeaders | Select -Unique | % {\n<\/strong>$UserOut.$_ += $UserInfo.$_<\/strong><\/p>\n

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

$FinalOut += New-Object PSObject -Property $UserOut<\/strong><\/p>\n

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

$FinalOut | Select $CsvHeaders<\/strong><\/p>\n

You notice this very last line \u2013 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 \u2013 you can even put a pipe to \u201cwhere IsLicensed\u201d to only capture your licensed users.<\/p>\n

Thanks so very much Will! This was an excellent read an interesting challenge solved with PowerShell!<\/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 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 \u2013 Part 1 Reporting on Microsoft 365 Licensing using PowerShell \u2013 Part 2 Dr. Scripto here! When last we saw […]<\/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,685],"tags":[1740,1777,377,1778,1834],"class_list":["post-86192","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-doctor-scripto","category-microsoft-365","category-powershell","category-scripting-techniques","tag-doctor-scripto","tag-microsoft-365","tag-powershell","tag-reporting","tag-will-martin"],"acf":[],"blog_post_summary":"

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 \u2013 Part 1 Reporting on Microsoft 365 Licensing using PowerShell \u2013 Part 2 Dr. Scripto here! When last we saw […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86192","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=86192"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/86192\/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=86192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=86192"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=86192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}