Use PowerShell to Create Reports in Office 365

powerShell 300x225 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:

Import-Module MSOnline
$ O365Cred = Get-Credential
$ O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $ O365Cred -Authentication Basic -AllowRedirection
Import-PSSession $ O365Session -AllowClobber
Connect-MsolService –Credential $ O365Cred

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:

120816 1735 UsePowershe1 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe2 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe3 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe4 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe5 Use PowerShell to Create Reports in Office 365

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

120816 1735 UsePowershe6 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe7 Use PowerShell to Create Reports in Office 365

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:

120816 1735 UsePowershe8 Use PowerShell to Create Reports in Office 365

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

Let’s write a quick script!

$  a= Get-MsolUser |select UserPrincipalName,licenses

  foreach ($  member in $  a) {
     $  userName = $  member.UserPrincipalName
     $  output = new-object PSObject
     foreach ($  group in $  member.licenses){
     $  output | add-member NoteProperty "UserName" -value $  userName -Force
     $  output | add-member NoteProperty "AccountSkuId" -value $  group.AccountSkuId -Force
     $  output | export-csv .\licenses2.csv -Append -NoTypeInformation -Encoding ASCII
     }
  }

Hey? What’s going on here? Let’s take a look:

$  a= Get-MsolUser |select UserPrincipalName,licenses

This collects all users in your domain and their licenses.

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:

$  output | export-csv .\licenses2.csv -Append -NoTypeInformation -Encoding ASCII.

The switches ensure we exclude the type info and that the output is in ASCII format.

So, run your script:
120816 1735 UsePowershe9 Use PowerShell to Create Reports in Office 365

Open the file and see what you get.

120816 1735 UsePowershe10 Use PowerShell to Create Reports in Office 365

Now, with a little pivot table magic, you get this:

120816 1735 UsePowershe11 Use PowerShell to Create Reports in Office 365

It looks a lot like what you got when you went to the Microsoft Office 365 website, but you can now break it down to what user has what licensing. Your boss will love you and victory is yours!

Stay tuned for more PowerShell adventures and as always, happy CRM’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM