Microsoft 365 - License Reporting

Looking for a Microsoft 365 License Reporting Tool for Microsoft 365 Services?
Nice that you came across our blog post.

Today we present a Microsoft 365 license reporting tool (Python based), which can create a license report for your Azure Tenant or Microsoft 365 Tenant. The tool uses the Microsoft Graph API and generates two CSV files each time it is run:

• Licenses – per tenant
  All available licenses in the Microsoft tenant, including unassigned ones
• Licenses – per user
  List of all users and their assigned licenses

The reporting tool can therefore be run as often as you like, on a Linux system, Windows or in a Docker container. The reporting tool is chargeable (perpetual / one-off) and can be run as often as you like.

You need the freely available “openssl” tool to create a one-off certificate for authentication and communication with Azure.

Good and regular license reporting helps you keep the costs for Microsoft licenses under control.opensight.ch – roman hüsler

Contents

• Sample Output
• licenses_pertenant.csv
• licenses_peruser.csv
• Installation
• Create certificate for authentication and encrypted communication
• Create Azure Service Principal
• Execute Microsoft 365 Lizenz-Reporting

Sample Output

licenses_pertenant.csv

This report lists all licenses that are available in your tenant – including those that are not assigned to any user. In the report you can see the SKU (stock keeping unit), the number (prepaidUnits), the number of assigned licenses (consumedUnits) and the product name for the SKU (skuName)

skuId;prepaidUnits;consumedUnits;skuName
f30db892-07e9-47e9-837c-80727f46fd3d;10000;1;MICROSOFT FLOW FREE
cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46;1;1;MICROSOFT 365 BUSINESS PREMIUM

licenses_peruser.csv

In this report you can see which licenses are assigned to which user. The report lists all your users and accordingly the “userSKUs” (comma separated), as well as the product names (userSKUsDetails – comma separated).

userPrincipalName;userId;officeLocation;userSKUs;userSKUsDetails
...
roman.huesler@opensight.ch;xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx;;f30db892-07e9-47e9-837c-80727f46fd3d,cbdc14ab-d96c-4c30-b9f4-6ada7cdc1d46;MICROSOFT FLOW FREE,MICROSOFT 365 BUSINESS PREMIUM
....

Installation

Create certificate for authentication and encrypted communication

The tool authenticates itself certificate-based in your Azure Tenant. For this purpose we first create a certificate (incl. private key). The “req.cnf” is also included in the download.

# generate private key and certificate
openssl req -x509 -newkey rsa:4096 -sha256 -days 3650 -nodes -keyout src/certificate/license-reporting.key -out src/certificate/license-reporting.crt -config src/certificate/req.cnf

# output certificate thumbprint
openssl x509 -in src/certificate/license-reporting.crt -noout -fingerprint | sed -r 's/SHA1 Fingerprint=//g' | sed -r 's/://g' > src/certificate/thumbprint.txt

Make a note of the certificate thumbprint that is returned by the second command.

Create Azure Service Principal

In order for the tool to be able to read the information from your Microsoft Tenant, a Service Principal (app registration) must be created. Add an App Registration and note the ID of your Azure Tenant Tenant ID (see image), as well as the App ID (see image)

At “certificates and secrets” you can upload the previously created certificate.

Now you have to give the Service Principal the necessary rights – under “API Permissions” so that the reporting tool can read the license information (see image)

Execute Microsoft 365 Lizenz-Reporting

Now all the preparations have been made so that the tool can generate License Reports.

# python installation
apt install python3
apt install python3-pip

# install dependencies (specified in requirements.txt)
python3 -m pip install -r requirements.txt

# run the reporting tool
python3 src/report.py -t <tenant id> -c <client id> -s certificate/license-reporting.key -b <cert thumbprint>

A final note. The Microsoft 365 License Reporting Tool contains a list of all Microsoft SKUs (skus.csv). This should be updated regularly. Or it can simply be downloaded from Microsoft.