Office 365 Licensing Report (with On Premise Active Directory)

One thing that drives me insane is the thought of over paying for O365 licensing when it’s so easy to get right. In my opinion, the reason it gets out of whack is mostly poor communication on user status. Once we see where the hole is though, we can plug it. Since I run a hybrid environment with an On Premise Active Directory I have a few extra steps to validate accounts but nothing crazy.  Standard scripting disclaimer here.

Office 365 Info

First thing to do is gather some info from Office 365. You can do this for whatever licensing you want to check, in this article I am going to pull info for O365 Kiosk licenses.  

I am going to assume you have already run the commands and connected to O365. Before constructing your query I highly recommend to run the command to list your licenses. The order of this list shouldn’t change, unless you add something new but you will need to reference the list item number of the license you are querying for. Notice below there is no “Kiosk” license, that’s because in this list it’s referred to as “EXCHANGEDESKLESS”.  To get this info we use the command: Get-MsolAccountSku 

Once we know the position of the license we want we are ready to query. The query is pretty straightforward with the exception of the license information, remember that the list starts at 0 not 1.

Notice that I only pulled minimal information, only a couple fields. The reason for this is that all we need is the UPN so we can query AD directly. Anything that is added to this list we already know the license of so already achieved the hard part.

Active Directory

Now that we have a proper list of users that have kiosk licenses we can query AD and find their particulars. The way I prefer to do this is to step through the list we just for from O365 and query one at a time. Each time we query we are only looking for some specific info.

• Name
• Enabled
• Created
• PasswordExpired
• PasswordLastSet
• LastLogonDate

That’s it, those 6 fields are all we need for this validation. We are going to apply a little logic to those date fields and see if we have license attached to accounts that aren’t in use. Just a word of warning here…your results will not be perfect. This is a first run, most likely you will come up with service accounts and things like that. As you develop this script more to can isolate those out but for now just keep an eye open.

Let’s go over real quick what’s going on there. We have created a loop to step through that that O365 list one at a time. Each time we turn that UPN around and query AD for that users info. Once we have the info we add it into a separate array that we have created. Info in the array, we turn around and do it over again a few hundred times until complete.

The List

There is a ton more you can do, I personally have data validation going on with the dates and an HTML report. I have an article about making an HTML report here. I’m not going to take you through all that in detail because it will not match your environment but I will tell you my criteria.

• Accounts that have no login for 45 days
• Accounts with passwords expired 45 days
• Accounts that have NO login date
• Accounts that have no password set date
• Accounts that are “enabled” false

These are the basics for my environment. What you can do on the first run is simply just output your list you created to a CSV or GridView and review it that way to get some quick results.