Office 365. Assigned Licenses Report

Report

Our report is prepared by console application in Excel format (.xlsx). The report contains the following information about users:

• DisplayName
• Mail
• Department
• AccountEnabled
• UserType

So you can run this app and find out disabled users who have licenses and decrease cost of owning your Office 365 tenant by removing such assignments.

Nuget Packages

The application uses Microsoft Graph for data retrieving. So we need to add Microsoft.Graph package to the project. Run the following command in Package Manager Console:

PM> Install-Package Microsoft.Graph

Also we need Microsoft.IdentityModel.Clients.ActiveDirectory package for authentication in Microsoft Graph:

PM> Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

And the last package we need is ClosedXML for generation Excel file.

PM> Install-Package ClosedXML

Register application

To be able to interact with Microsoft Graph we have to register the application. You can use this guide to achieve this goal: Register your app with the Azure AD v2.0 endpoint.

There are three parameters we have to know about the app:

1. ClientId. You can find out it on you app property page (APPLICATION ID):

2. ClientSecret. Go to settings page of your app and create new secret key:

3. Tenant Name - string like .microsoftonline.com.

Configuration (app.config)

Open app.config file and add app parameters above into appSettings section:

App created, app parameters stored into the config file. So we can start to create a report application.

Microsoft Graph Provider

Create GraphProvider.cs file with the following content:

using System.Configuration;
using System.Net.Http;
using System.Threading.Tasks;
using Microsoft.Graph;
using Microsoft.IdentityModel.Clients.ActiveDirectory;

namespace Zhukov.Demo.GraphLicensesReport
{
    public class GraphProvider : IAuthenticationProvider
    {
        public async Task AuthenticateRequestAsync(HttpRequestMessage request)
        {

            var token = await GetToken();
            request.Headers.Add("Authorization", "Bearer " + token);
        }

        public static async Task<string> GetToken(string resource = @"https://graph.microsoft.com/")
        {
            var clientId = ConfigurationManager.AppSettings["ida:ClientId"];
            var clientKey = ConfigurationManager.AppSettings["ida:ClientSecret"];
            var azureDomain = ConfigurationManager.AppSettings["ida:Domain"];
            var authory = $@"https://login.microsoftonline.com/{azureDomain}";
            var creds = new ClientCredential(clientId, clientKey);
            var authContext = new AuthenticationContext(authory);
            var authResult = await authContext.AcquireTokenAsync(resource, creds);
            return authResult.AccessToken;
        }
    }
}

That's Microsoft Graph Provider which will be used for performing requests.

Application Code

The application will do the following:

1. Get all users page by page until they all are retrieved;
2. Create a table where rows are users and columns are licenses;

Getting users code:

// Microsoft Graph Provider
var provider = new GraphProvider();

// Microsoft Graph Client
var client = new GraphServiceClient(provider);

// List of users
var users = new List<User>();

// Paged Request
IGraphServiceUsersCollectionPage items = null;

// Fill up the list while next page is available
while (items == null || items.NextPageRequest != null)
{
    // Request to Microsoft Graph
    var itemsRequest = items?.NextPageRequest ?? client
                            .Users
                            .Request()
                            .OrderBy("displayName")
                            .Select("displayName, mail, department, assignedLicenses, accountEnabled, userType")
                            .Top(100);

    // Get Users
    items = itemsRequest
        .GetAsync()
        .GetAwaiter()
        .GetResult();
                
    // Add users to the list
    users.AddRange(items);
}

AssignedLicenses property contains information about the product. But there is no friendly data, but only SKU (Stock Keeping Unit).

SKU

The following table contains information about Office SKU:

Excel Report

The last part of the application - report generation:

// New Excel Workbook
using (var workbook = new XLWorkbook())
{
    // Add new sheet
    using (var worksheet = workbook.Worksheets.Add("Assigned licenses"))
    {
        // row number
        var r = 1;
        // Header -->
        worksheet.Cell(r, 1).Value = "User";
        worksheet.Cell(r, 2).Value = "User Type";
        worksheet.Cell(r, 3).Value = "Email";
        worksheet.Cell(r, 4).Value = "Department";
        worksheet.Cell(r, 5).Value = "Blocked";

        // column number
        var c = 6;
        foreach (var sku in SKUs)
        {
            worksheet.Cell(r, c).Value = sku.Value;
            c = c + 1;
        }
        // <-- Header


        // go to the next row
        r = r + 1;

        foreach (var user in users)
        {
            // User Information
            worksheet.Cell(r, 1).Value = user.DisplayName;
            worksheet.Cell(r, 2).Value = user.UserType;
            worksheet.Cell(r, 3).Value = user.Mail;
            worksheet.Cell(r, 4).Value = user.Department;
            worksheet.Cell(r, 5).Value = user.AccountEnabled == false ? "Blocked" : string.Empty;

            // go to 6th column (1-5 columns for user information)
            c = 6;

            foreach (var sku in SKUs)
            {
                // is current SKU is assigned to current user
                var assignedFlag = user
                    .AssignedLicenses
                    .Any(x => x.SkuId == sku.Key);

                worksheet.Cell(r, c).Value = assignedFlag ? 1 : 0;

                // go to the next column
                c = c + 1;
            }

            // go to the next row
            r = r + 1;
        }

        // create table -->
        var table = worksheet.Range(1, 1, r - 1, 5 + SKUs.Count).CreateTable();
        table.Theme = XLTableTheme.TableStyleMedium2;
        // <-- create table

        // adjust column width to content
        worksheet.Columns().AdjustToContents(5, 40);

        // freeze first 2 columns
        worksheet.SheetView.FreezeColumns(2);
    }

    // save the report
    workbook.SaveAs("AssignedLicesesReport.xlsx");
}

That's it!

Source Code

Source code is available here: https://code.msdn.microsoft.com/Office-365-Assigned-8c2eb22e.