Office 365: Script to get detailed report of assigned licenses

BLOG UPDATE: April 1st 2021

Hi everyone. I wrote this blog post many years ago, and throughout the years this has been a blockbuster in terms of visits. Thank you for that.

I think that highlights the growing need companies have, to assess their Microsoft 365 portfolio. That of course comes from a gap in the native portal and the native reporting capabilities.

I decided to update this blog post and let you all know about the BitTitan Voleer IT Automation Toolbox. Those who know me, know that I work for BitTitan, but this is not a “sponsored” blog post. You can register for free and it literally takes you 5 seconds to fill in the below, which is all the information you need to give, to register and trial it for 30 days.

1-trialreg

Once you register you can login and either search in the library for “Microsoft 365 License Usage And Optimization Assessment” or go directly to it, by clicking here.

You can then follow these simple steps to execute the assessment:

  1. Read the instructions and click on “Launch” on the right hand side, once you select the Workspace (Sandbox is the default)
  2. Authorize Microsoft Graph access to your tenant, by clicking the available links and entering the code provided. Click “Validate” when done.
  3. In “Configure” you can configure filters to assess just part of your Organization. The filters can be done by dropdowns, string or using User Principal Names. Common filters you can use include country, account log in state or Department, amongst others. Leave blank to assess the entire tenant.
  4. Also in “Configure” you can select to email the report from a Voleer email address, or from one that you would put username and password for. You then select to which address is the report being sent to and password protect it.
  5. Once all of the above is completed, click “Execute”

Once the execution is complete you will get an email, with an attached CSV with multiple tabs, with all the information you need. See below an example of the assessment Dashboard.

MicrosoftTeams-image (3)

You can save the template configuration, to make future executions easier and you can also schedule it. Those are 2 huge benefits that add to the fact that you have the awesome feature of filtering the assessment (i.e assess licensing for login disabled users only). Top that with not having to handle PowerShell scripting and I think you have enough reasons to go check Voleer out.

Voleer has many other templates in the library, that you can check out and run during your free trial.

Any questions let me know and, if you want to run it the “old way”, keep reading. I have the PowerShell “one liners” below that will help you create the reports of your licensed users.

BLOG UPDATE END

It’s very common to see Office 365 administrators asking in the community “How can I get a detailed report of the licenses i have assigned on Office 365?

Well it will depend on how detailed you want the report. I’ll detail here two solutions.

1 – Get a report of all licensed users and the AccountSKUId name

To run this report you need to open the Windows Azure Active Directory Module for Windows Powershell, and connect to Office 365. Once connected run the following cmdlet:

Get-MSOLUser -All | select userprincipalname,islicensed,{$_.Licenses.AccountSkuId}| Export-CSV c:\userlist.csv -NoTypeInformation

The above command lists ALL users and not just the ones that have a license. See the output CSV file below. There are ways of filtering the output (i.e export only licensed users), but i will keep this post simple. Let me know if you need something more elaborated.

1

2 – Get a detailed report of the licenses enabled for each user

One of the other requirements, is to know in detail, how many licenses per product do you have enabled, and which users have that license. If you want a detailed list with the users that have Lync Online, Exchange Online, Office Pro Plus (just to give three examples), or any other product that you have on your subscription, enabled or disabled, all you need to do is use the “Export a Licence reconciliation report from Office 365 using Powershell” script available on the Microsoft Gallery.

Again to run this report you need to open the Windows Azure Active Directory Module for Windows Powershell, and connect to Office 365.

Once connected to Office 365 browse into the directory where you saved the script, and run it.

2

The script will prompt you for the Office 365 administrator credentials, and run against all licensed users. By default the script creates a file named “Office_365_Licenses.csv” that will be created on the same directory where the script is. If you want, you can change it by editing the script. There’s also some other things you can change on the script, such as export all users and not just the licensed users, or use the existing credentials cached on your powershell session, instead of prompting you for credentials each time you run it. But again I will keep it simple for now, and if you want to change something on how the script works, let me know.

Now let’s have a look at the detailed output of the script.

3

Let’s now take the user Antonio Vargas as an example. He has all licenses assigned. Let’s see the view from the portal.

4

As you can see the Yammer licenses are assigned by default (hence the “PendingInput” state on the property exported to csv), and all other licenses are assigned, which matches with the success property on the csv. Now below let’s have a look at the user Calvin, which only has the Exchange Online license enabled (and the Yammer by default). All the other licenses are disabled.

5

Again when looking to the licenses that the user Calvin has assigned, via the Office 365 portal, it matches the csv file.

If you want, and because usually the output you will get is a very large csv file, you can use filtering at the csv level to get smaller lists depending on the license type you want the report on.

Any questions let me know, and happy reporting! 🙂

Google Suite: Use GAM to get a list of all users forwarding addresses

A few days ago I wrote a blog post on what domain names to use in Google, to forward email to Office 365. In that article I explained the differences between the user-level forwarding, set by the Google administrator, and the forwarding set at the account settings level, by each user or by you with an automation tool (such as GAM or the BitTitan Management Console).

If you read the article and you agree with me, that setting the forwarding address at the account settings level through automation, is the best option, then read on this one because I am about to explain you how do you, as a Google Administrator, can extract a report to have visibility across the entire Google tenant, on all forwarding addresses that are set per user.

Step 1: If not already, download, install and configure the GAM tool

The GAM tool is a command line tool that allows you, Google Suite administrator, to manage your tenant.

On the GAM tool main page you find instructions on how to download it, install it and configure it, with all the appropriate permissions into your Google tenant.

Step 2: Export the forwarding addresses

Once you have the GAM tool installed you can use it to print the forwarding addresses.

With the command prompt open (and the GAM tool installed and configured of course), do the following.

You can print one user by running:

gam user <Username> print forwardingaddresses

GAM1

Or you can print for all users by running:

gam all users print forwardingaddresses

GAM2

As you can see, it’s a simple process. It will export the user, the forwarding email and the verification status of that forwarding.

To get the results exported you have two options.

Export directly to CSV from the command prompt:

gam all users print forwardingaddresses > C:\GAM\MyUsers.csv

GAM3

Export the result to Google Drive from where you can download as an Excel file:

gam all users print forwardingaddresses todrive

GAM4

Note: Follow the link to the Google Drive, provided on the command line. Once you have the document open, go to File > Download > Excel (xlsx)

Step 3: Export the forward configurations

Once you have all of the addresses, you should also think about exporting the forward configurations, which are the options you can select when you set the forward, to what happens to the message on Google (keep|archive|delete|markread).

I won’t go over the export options again, as they are the same as in step 2 of this post. Check below the commands to export for one or all of the users.

You can print one user by running:

gam user <Username> print forward

GAM5

Or you can print for all users by running:

gam all users print forward

GAM6

Note: As you can see above the option names don’t match (i.e keep=leaveInInbox and trash=delete) but they are very self explanatory. 

And that’s it, you now know exactly which per user setting, in terms of forwarding, each one of your users has configured.

Thank you for visiting my blog!