One of the challenges I’ve faced recently was the need to report monthly on subscription details per user across multiple modules. This task, while seemingly straightforward, turned out to be more complicated due to the limitations of the out-of-the-box reporting features in ServiceNow. This limitation is partly because ServiceNow’s standard reporting tools are not designed for complex, cross-table queries that involve multiple modules.

Developing a Custom Solution with Python

To overcome this hurdle, I turned to Python, a versatile programming language known for its simplicity and efficiency. By leveraging Python, I was able to interact with the ServiceNow API, which is a gateway to the platform’s vast data resources.

Step 1: Accessing License Tables

The first step in my approach was to use the ServiceNow API to gain access to the relevant license tables. This step is crucial as it allows the extraction of raw data needed for detailed reporting.

Step 2: Extracting and Grouping Data

Once I had access to the data, the next step involved extracting the relevant details and then grouping them by users. This process was made easier with Python’s robust data manipulation libraries, like Pandas, which enable efficient data processing and grouping.

Key Requirement: Proper Group Assignment

For this script to work effectively, it’s imperative to properly assign groups to your licenses in the ServiceNow platform. This organizational step ensures that the script can accurately group and report subscription details per user.

import requests
import json
import pandas as pd
import datetime

# Set the API endpoint URLs
subscription_url = "https://<instance>.service-now.com/api/now/v2/table/license_details"
user_url = "https://<instance>.service-now.com/api/now/table/sys_user_has_license"

# Set the username and password for authentication
username = ""
password = ""

# Set the request headers
headers = {
    "Accept": "application/json"
}

# Set the request parameters for the subscription overview API
params = {
    "sysparm_query": "license_type=0",
    "sysparm_limit": "100"
}

# Send the API request and retrieve the subscription data
response = requests.get(subscription_url, auth=(username, password), headers=headers, params=params)
subscription_data = json.loads(response.text)

# Create a dictionary to store the subscription information for each user
user_dict = {}

# Loop through the subscription data and retrieve the user data for each subscription
for subscription in subscription_data['result']:

    # Set the request parameters for the subscription user API
    user_params = {
        "sysparm_query": f"license={subscription['sys_id']}^user.active=true^active=true",
        "sysparm_fields": "user.user_name,user.name,product_name,user.manager.name,user.department.name,user.title,user.email,user.last_login"
    }

    # Send the API request and retrieve the user data
    user_response = requests.get(user_url, auth=(username, password), headers=headers, params=user_params)
    user_json = json.loads(user_response.text)

    # Loop through the user data and add the subscription information to the user_dict
    for user_data in user_json['result']:
        user_id = user_data['user.user_name']
        subscription_name = subscription['name']
        if user_id not in user_dict:
            user_dict[user_id] = {'email': user_data['user.email'], 'name': user_data['user.name'],
                                  'manager': user_data['user.manager.name'], 'title': user_data['user.title'],
                                  'department': user_data['user.department.name'], 'last_login': user_data['user.last_login'],
                                  subscription_name: 1}
        else:
            user_dict[user_id][subscription_name] = 1

import openpyxl

# Create a Pandas Excel writer using openpyxl engine
writer = pd.ExcelWriter('user_subscriptions.xlsx', engine='openpyxl')

# Convert user_dict to a Pandas dataframe
df = pd.DataFrame.from_dict(user_dict, orient='index')

# Add subtotals row to the bottom of the dataframe
subtotals = df.iloc[:, 6:].agg(['sum'], axis=0)
subtotals.index = ['subtotals']
df = pd.concat([df, subtotals])

# Write the dataframe to the Excel file and set formatting
df.to_excel(writer, sheet_name='Sheet1', index=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Set column width for email and name columns
worksheet.column_dimensions['A'].width = 25
worksheet.column_dimensions['B'].width = 25
worksheet.column_dimensions['C'].width = 20
worksheet.column_dimensions['D'].width = 30
worksheet.column_dimensions['E'].width = 20
worksheet.column_dimensions['F'].width = 10
worksheet.column_dimensions['G'].width = 15
worksheet.column_dimensions['H'].width = 15
worksheet.column_dimensions['I'].width = 15
worksheet.column_dimensions['J'].width = 15
worksheet.column_dimensions['K'].width = 15


# Set word wrap and center alignment for all columns
for row in worksheet.iter_rows():
    for cell in row:
        cell.alignment = openpyxl.styles.Alignment(wrap_text=True, vertical='center')

# Save the Excel file
month = datetime.datetime.now().month
year = datetime.datetime.now().year
day = datetime.datetime.now().day
workbook.save(f'{month}-{day}-{year}_user_subscriptions.xlsx')