SHARE THIS POST:

Use PowerShell to Create Reports in Office 365

Let’s say your boss asks you to put together a licensing breakdown for Office 365. Would you know what steps to follow to compete this task, with all of the necessary details, to knock it out of the park? Today’s blog will show you how and include tons of troubleshooting tips along the way.

Before we start, check out our previous blog about PowerShell, where we talked about using PowerShell to help manage your Office 365 environment with these commands:

Now, let’s get started on your adventure to figure out and complete this request.

You head out and start digging around. You find this:

That is all well and good, but your boss also wants a breakdown of who has what.

You notice there can be views under Active Users:

This list proves to be a little disheartening. None of these gives you what you need and you really can’t do anything with a view other than look at it, and the boss did say he wanted a detailed view. What are you going to do? Well, we have good news for you! You can start up PowerShell and log onto Office365 as detailed here.

You start looking for commands to complete this task. You find ‘Get-MsolAccountSku‘ which returns this:

You say to yourself, “that looks a lot like the information I got when I went to the Office 365 website.” Still, it’s not very helpful in breaking that information down, so you keep looking.

Then it hits you. The information you need should be held with the user. You do ‘Get-MsolUser‘ and it returns this:

It says that this account ‘isLicensed.’ Now, the question is “Licensed with what?” You remember the ‘format-list‘ command, or fl for short, which is designed to bring back everything it knows about a given object.

You run ‘Get-MsolUser –UserPrincipalName someuser|fl *‘ and start looking through the returned fields. You find this:

To get only that piece of information, you run ‘Get-MsolUser -UserPrincipalName someuser|select UserPrincipalName,licenses‘:

Great, you finally have the information you were seeking! But there’s more…

You need to get that information into something you can work with. You think to yourself “I should just be able to export to CSV, right?” You run ‘Get-MsolUser -UserPrincipalName someuser|select UserPrincipalName,licenses|Export-Csv .\licenses.csv.‘

You take a look at the output and see this:

Well, that didn’t work very well.

First of all, you really don’t need ‘#TYPE Selected.Microsoft.Online.Administration.User‘. That is easy enough to get rid of. Using the ‘–NoTypeInformation‘ switch on the ‘Export-Csv‘ command does the trick. But the ‘Licenses’ information wasn’t what you expected. It gave a System.Collections name instead of the actual list of names. Now what?

HINT: The explanation for that is beyond the scope of this article. Suffice it to say the field that was returned is of MemberType ‘NoteProperty‘.

To help figure it out, you run ‘Get-MsolUser -UserPrincipalName someuser|select UserPrincipalName,licenses|Get-Member‘ and see:

So, Licenses is of MemberType ‘NoteProperty.’ Now, the question is, “how do I get the actual list?”

Now that we have that, let’s get the information into a readable format. We loop through each member in the “$a” array using the “foreach” methodology. We get the user name by extracting it with “$userName = $member.UserPrincipalName.”

Now, because “$member. licenses” is really an array, we loop through it to extract all licenses. Within each loop, we append each piece of information to the csv file using this:

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

USD is a great for managing multiple customer sessions. In this blog, we walk through how to prevent USD from closing a session with unsaved changes.

PowerObjects, an HCL Technologies Company, is a leader in delivering Microsoft Dynamics 365 solutions through unparalleled offerings of service, support, education and add-ons. Winner of the 2017 Microsoft Worldwide Partner of the Year Award for Dynamics 365 Consulting and Systems Integration, PowerObjects has built an unmatched team of Dynamics 365 experts that help organizations increase productivity, streamline business processes, and build better relationships.