I faced the small problem that I must create an inventory for an O365 Tenant. The problem is that it must be aggregated by the department of the user. So that the cost centre can pay for the licences.

So the target goal is to generate an accumulated list that contains the number of licences by the licence type and also export a plain list with the user and its assigned licence.

Dev Workspace

Actually, I am fascinated by python, so why not use it for local development? For generating and testing the scripts I use a jupyter notebook. This extension is also part of Visual Studio code. So It's easy to start with this.

To get the data I use the Office Graph. With this, I will get the complete information about every user and its assigned licences. So let's create a new Jupyter Notebook in vscode. Just create the filename Inventory.ipynb. It automatically opens the notebook mode.

At first, we need some packages for this you will do a pip to install them

pip install azure-identity azure-mgmt-costmanagement azure-mgmt-billing azure-mgmt-subscription azure-mgmt-resource msal plotly nbformat

This will install the requirements to work with the graph api and do some authentication stuff and so on.

Then create a new code section insert the variables for referencing your tenant and use the authentication

AZURE_TENANT_ID="........"
AZURE_CLIENT_ID="........""
AZURE_CLIENT_SECRET="........"

For this, you must create a new app registration in your Entra ID, you can read about this here.

You will need the following permissions (exclude the first one for ReadWrite, it's used fo another use case for me):

Now we need a small helper function to obtain a JWT - Token from the Entra ID. This will be needed to get the required access to the Graph data.

import os
import time
from azure.mgmt.consumption import ConsumptionManagementClient
from azure.mgmt.resource import ResourceManagementClient
from azure.mgmt.billing import BillingManagementClient
from azure.identity import ClientSecretCredential
from azure.mgmt.subscription import SubscriptionClient

import msal
def GetAccessToken():
    authority = f"https://login.microsoftonline.com/{AZURE_TENANT_ID}"
    scopes = ['https://graph.microsoft.com/.default']

    app = msal.ConfidentialClientApplication( AZURE_CLIENT_ID,  AZURE_CLIENT_SECRET, authority=authority)

    result = app.acquire_token_for_client(scopes)
    access_token = result['access_token']
    return access_token

Now the basics are set. Next, I will obtain all licenses from the complete organization by using the /subscribdesSkus endpoint. This endpoint will give us information about which licenses is available in the tenant and how many licenses they have (no matter if they are assigned or not).

import requests

access_token = GetAccessToken()
url = "https://graph.microsoft.com/v1.0/subscribedSkus"
headers = {
    "Authorization": "Bearer " + access_token
}

response = requests.get(url, headers=headers)
data = response.json()
licences=[]
for sku in data['value']:
    licences.append( {
        'SKU': sku['skuPartNumber'],
        'ID': sku["skuId"],
        'consumed': sku['consumedUnits'],
        'totalEnabled':sku["prepaidUnits"]["enabled"]
        # Fügen Sie hier weitere gewünschte Eigenschaften hinzu
    })

The result looks like this

[
    {
        "SKU": "VISIOCLIENT",
        "ID": "c5928f49-12ba-48f7-ada3-0d743a3601d5",
        "consumed": 16,
        "totalEnabled": 16
    },
    {
        "SKU": "STREAM",
        "ID": "1f2f344a-700d-42c9-9427-5cea1d5d7ba6",
        "consumed": 33,
        "totalEnabled": 10000
    }.....
]

you see that I acquired the total enabled units and the assigned licenses in this organization. Next I must get all users to gather the information about the department

def GetAllUsers():
    url = "https://graph.microsoft.com/v1.0/users?$top=999"
    headers = {
        "Authorization": "Bearer " +GetAccessToken()

    }

    response = requests.get(url, headers=headers)
    userdata = response.json()
    print(userdata)
    users=[]
    for sku in userdata['value']:
        users.append( {
            'name': sku['displayName'],
            'ID': sku["id"]
        })
    return users

When I execute this, the result looks like this

[
    {
        "name": ".....@emea.teams.ms",
        "ID": "e3baf8b7-......."
    },
    {
        "name": "demo, users",
        "ID": "424378f3-......"
    }...
]

So you see that's simple, but uhh? Where is the department? So a little bit of investigation later.. I figured out that the department must be fetched for each user separately with the /department route. So I must iterate through each user to get this data. But while I iterate each user, I wanna see which license is assigned to each user. So I fetch the assigned license with the /license details route. The complete code looks like this:

users= GetAllUsers()
assignedlicences=[]

for user in users:
    id = user["ID"]
    url = f"https://graph.microsoft.com/v1.0/users/{id}/department"
    headers = {
        "Authorization": "Bearer " + GetAccessToken()
    }
    response = requests.get(url, headers=headers)
    departmentData = response.json()
    department="N/A"
    if 'value' in departmentData:
        department=departmentData['value'] 
    else:
        department="N/A"
    url = f"https://graph.microsoft.com/v1.0/users/{id}/licenseDetails?$select=skuPartNumber"
    headers = {
        "Authorization": "Bearer " + GetAccessToken()
    }
    response = requests.get(url, headers=headers)
    data = response.json()
    for sku in data['value']:
        assignedlicences.append( {
            'skuPartNumber': sku['skuPartNumber'],
            'userid': user["ID"],
            'Name': user["name"],
            'Department': department
        })

The result looks then like this:

[
    {
        "skuPartNumber": "FLOW_FREE",
        "userid": "XXXXX-29ff-4475-a9f9-0eb0ecf6ea90",
        "Name": "Dummy, 1",
        "Department": "Department 1"
    },
    {
        "skuPartNumber": "ENTERPRISEPACK",
        "userid": "YYYYYY-29ff-4475-a9f9-0eb0ecf6ea90",
        "Name": "Dummy, 2",
        "Department": "Department 2"
    }...

]

Nice, now I have the license information for each user and also the department for this. So I can now export this plain list into a csv file to enable the back office to segmenting the bills into departments. To export the data into a file, the code looks like this

import csv
csv_file = "export.csv"
fields = ['skuPartNumber', 'userid', 'Name','Department']

# CSV-Datei schreiben
with open(csv_file, mode='w', newline='',encoding='utf-8') as file:
    writer = csv.DictWriter(file, fieldnames=fields)
    
    # Header schreiben
    writer.writeheader()
    # Datensätze schreiben
    for license in assignedlicences:
        writer.writerow(license)

After that, the export is done into the file export.csv and the content looks likt this

skuPartNumber,userid,Name,Department
FLOW_FREE,XXXXXXX-29ff-4475-a9f9-0eb0ecf6ea90,"Dummy, 1",Department 1
ENTERPRISEPACK,XXXXXXX-29ff-4475-a9f9-0eb0ecf6ea90,"Dummy, 1",Department 1
EMS,YYYYYYYYY-5dd2-4a08-9583-098147279e55,"Dummy, 2",Department 2

Final Words

In the quest to streamline O365 Tenant inventory management, I confronted the challenge of aggregating data by user departments for efficient license allocation. Harnessing the versatility of Python and the Office Graph API, I embarked on a journey of data retrieval and integration. With Jupyter notebooks as my trusty companion, I meticulously retrieved subscribed SKUs, delved into user profiles, and seamlessly linked license details with departmental affiliations. Armed with comprehensive insights, I culminated the endeavor by exporting structured data for actionable insights, turning a seemingly small problem into a testament to the power of innovation and technology.