Accessing Office 365 Activity Reports using Power BI or Excel – the OAuth Question

I hope you’re ready for another in-depth article about getting secure access to Office 365 stuff!

In this blogpost, I’d like to take you through the process of authentication and usage of the Office 365 Usage API’s in Power BI or your preferred BI tool.

 

What are these API’s?

So, you’ve seen the Office 365 Usage Reports in the Admin center, pretty nice, right?

Office 365 Usage Reports (Admin Center)

 

They’re okay if you like to see those exact numbers. If you want to go further, you could opt for the still-in-beta Office 365 Usage Adoption Pack for Power BI.

That’s all cool, but those reports are both overwhelming and maybe not what your power users want to see.

Well good news, the Microsoft Graph API’s provide Office 365 Usage Reports! The reference is also available together with all operations.

You can query data like SharePoint total storage, site storage, user activity, OneDrive total storage over time, …

Note that most of these operations return global KPI’s rather than in-detail usage. If you want detailed usage, check out the Office 365 Audit Logs through the Office 365 Management Activity API (stuff for another blogpost 😊).

 

Let’s Auth!

In the old days, you could just use your username/password combination to Basic Auth to these services. Then came OAuth, telling you you need a token to access it.

Nowadays, access to these kind of API’s is best done through service applications (not service accounts like a decade ago).

Azure Active Directory lets you register your ‘Application’ to access certain API’s.

Before you think: oh that’s going to be too complex! (as I used to think), please continue reading, it’s not that hard and there is no other way of getting access to these API’s.

You need to be an Azure AD administrator to be able to set up your app, or ask your Azure AD administrator to do this for you.

 

Creating an App Registration

To do that, go to Azure AD and go to App Registrations:

App Registrations Link

 

From there, you can create a new App Registration:

App Registrations overview screen

 

Next, you give it a name and return URL (for our purposes, a simple localhost will do):

New App Registration screen

 

By creating the app registration, we got our first piece of the puzzle, the Application Client ID, needed in many of the authorization operations. Note it:

Client ID for the new App Registration

 

Creating a Client Secret

Next, we need to create a client secret. You can also work with certificates, but we’ll just use a client secret for simplicity.

Click ‘Certificates & secrets’ on the left navigation of your newly created App Registration, the click ‘New Client Secret’:

Client secret overview screen

 

You can give your secret a name, for example, if you’re planning on giving several people or teams access to this API, you may create a different secret for each one, so you can revoke them individually afterwards.

Best practice is to not allow client secrets to never expire, but you can

Create new client secret

 

⚠ Now comes a crucial step: write down your client secret as it was just generated. You won’t be able to retrieve it anymore after you leave this page.

Client secret recently generated

PS: my client ID and secret were generated on a demo tenant.

 

Assigning Permissions to your App

Ok great, now what? Well, the app still has no permissions except reading your user profile from AD (which any AD user can do anyway), so we need to assign permissions.

According to the Microsoft documentation, we need Reports.Read.All permissions.

Click on ‘API permissions’ in your App Registrations ‘Manage’ section and click ‘Add Permission’:

App Registration Permissions

 

Select ‘Microsoft Graph’.

Request API Permissions #1

 

Now choose ‘Application Permissions’. We don’t want to use the user’s context as we’ll only be reading data.

Request API Permissions #2

 

Now scroll down to the Reports section and select ‘Reports.Read.All’:

Request API Permission #3

 

Now you need to explicitly consent these permissions:

Consent API Permissions #1

 

Success!

Consent API Permissions #2

 

Some OAuth Basics

Now that we have a Client ID and Client Secret and consented permissions, we can start calling the API. But how?

We need to get an authorization code first, and with that one-time authorization, we can get the access token. As this token expires after about an hour, we’ll also need the refresh token(valid for 1 year, 2 years of forever – remember?) to generate a new access token.

Getting the authorization token is a one-time thing done by an administrator, so that we can do once ourselves. Getting the refresh token? Same thing. But when the access token expires, we need to get a new one using the refresh token.

And this is where the issue arises in most current BI tools. Most of them are not capable of performing this OAuth flow. But read on for a good workaround 🙂

You can skip the next few sections if you’re familiar with OAuth already.

 

Getting the Authorization Code

This is typically done only once in a web browser where you’re already logged in as an admin.

Go to:

 

https://login.microsoftonline.com/<strong><tenant_id></strong>/oauth2/authorize?
client_id=<strong><your_app_registration_client_id></strong>
&response_type=code
&redirect_uri=http://localhost
&response_mode=query
&resource=https://graph.microsoft.com/

 

Some background here. We’re asking the OAuth Authorize endpoint for a code (reponse_type) for our app with the given Client ID. We also need to provide the redirect URI and resource to which we’re authorizing.

Note that you will also need to specify the tenant ID you are targeting, in case your app registration supports multiple Azure AD instances. You can find your Tenant ID (aka ‘Directory ID’) on the overview page of your app registration:

 

When you go to the URL, you will be prompted with a grant screen and once granted, your browser will probably freak out that localhost is not responding. That’s fine, as we need to take a look at the exact localhost URL:

 

localhost/
?code=<strong>AQABAAIAAADCoMpjJXrxTq9VG9te-7FXmC-hGtgqkFKN9YhC-sUb0eR8DwOmUGFTc7Dxel_1jvAudlXwxkkme5B-ZbRzNunhpui26J7uvpUC2GZ5Wb_93ApUO7BJVn8iSdZd9cLyWvQVjRGZRYbywI9QpN1QtJAk7i2AQ-KiIAzHVJV1mf7nND9npQxEA0_K1ebZTRmPRJKAxqULhKWIdEmNJWwE-6NIgdZDwiNc7U4Oviw0HgOMAxP3QH1OHcR3PP5n2pB7wHrrn97AmeREOWJlnnro0hl6EpqYauh1nKrqPjZHmIPVPJD7ATeCJdnGLHXoklcRhwR36uQU9iI_SqS0RcegHp_lS0WnmKnXhgVmVA0WAuu4l5XaoclGtN4GpBk2hQRPa_tg56Zwy5A58CrjZIW76ztZMBCX6LqCjpaw4reQNMDioOuEtxD9_FQh1tJO5fcbrSANdE3kdd-BuvdZfyPBgg9EoY9o_AnUmvVc3FPf8CE_BygfRUBV_XV4Sb_Neixu_h7dZ10cWrghKRZ94cDfxWsP-gVH8GEWJO3dpVwR8Kmgw0qNUHBjyi7nxLBU2-IvCB0gAA
</strong>&session_state=2239627c-28d2-4fed-87e7-d67e25b59435

 

Note that it has the ‘code’ query string parameter. This is the one-time authorization code we need to get our first access token and refresh token so copy/paste it.

 

Getting the Refresh Token

So far, so good, we now need to issue the one-time authorization code to get our first access token and refresh token.

Fire up Postman (or RESTED in Firefox or ACR in Chrome, whatever :-)) and start building a POST request to:

 

https://login.microsoftonline.com/<tenant_id>/oauth2/token

Add the application/x-www-form-urlencoded content-type and add the following body to the request:

 

grant_type=authorization_code
&amp;amp;client_id=&lt;strong&gt;&amp;lt;client_id&amp;gt;&lt;/strong&gt;
&amp;amp;code=&lt;strong&gt;&amp;lt;one_time_authorization_code&amp;gt;&lt;/strong&gt;
&amp;amp;redirect_uri=http://localhost
&amp;amp;resource=https://graph.microsoft.com/
&amp;amp;client_secret=&lt;strong&gt;&amp;lt;client_secret&amp;gt;&lt;/strong&gt;

 

The result you’ll get back will look like this (JSON):

 

{
(...)
"access_token": "&lt;strong&gt;eyJ0eXAiOiJKV1QiLCJub25jZSI6IkFRQUJBQUFBQUFEQ29NcGpKWHJ4VHE5Vkc5dGUtN0ZYSmVKY0x1Uk5iSnc3dnp6SkYxS3htRlZBWVJlajBfLWExZmNaRm5QVDRCSUtnQjFNb0xIcXNVbkloM0pKcnFIQVQ0bGxvVWpPSjc3NDNnSHoxQ3dla1NBQSIsImFsZyI6IlJTMjU2IiwieDV0IjoiQ3RmUUM4TGUtOE5zQzdvQzJ6UWtacGNyZk9jIiwia2lkIjoiQ3RmUUM4TGUtOE5zQzdvQzJ6UWtacGNyZk9jIn0.eyJhdWQiOiJodHRwczovL2dyYXBoLm1pY3Jvc29mdC5jb20vIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvMjI2ZjgxNWEtNmEwNC00MTI1LTg4NTctM2ZmNmEwNGNmZDFhLyIsImlhdCI6MTU2MTYyNDcyOSwibmJmIjoxNTYxNjI0NzI5LCJleHAiOjE1NjE2Mjg2MjksImFjY3QiOjAsImFjciI6IjEiLCJhaW8iOiI0MlpnWUJCTmM0L2VPRFZGTVBIUWovNWxrMUtmSnB6VnNWLzZTUEhhVkxhR1ZwVWpKUzhBIiwiYW1yIjpbInB3ZCJdLCJhcHBfZGlzcGxheW5hbWUiOiJBY3Rpdml0eUFQSSIsImFwcGlkIjoiYWMyNDkzYjEtZDFlNS00MmU5LTkzMDUtZTRlNWE5YTkzMzY5IiwiYXBwaWRhY3IiOiIxIiwiZmFtaWx5X25hbWUiOiJBZG1pbmlzdHJhdG9yIiwiZ2l2ZW5fbmFtZSI6Ik1PRCIsImlwYWRkciI6IjgxLjI0Ni4xMTguMjI2IiwibmFtZSI6Ik1PRCBBZG1pbmlzdHJhdG9yIiwib2lkIjoiZGRjNTFmMTQtMjMwNC00OWYyLTg2M2ItNjAyNzZjMDU4ZjJjIiwicGxhdGYiOiIzIiwicHVpZCI6IjEwMDMyMDAwNDkxQjFDNjYiLCJzY3AiOiJSZXBvcnRzLlJlYWQuQWxsIFVzZXIuUmVhZCIsInNpZ25pbl9zdGF0ZSI6WyJrbXNpIl0sInN1YiI6InRoX1NuQXNWSUI4T2RLUDBjek5vMTB4Yi0zd2Zhc0pOeURXRmRBTmFONkEiLCJ0aWQiOiIyMjZmODE1YS02YTA0LTQxMjUtODg1Ny0zZmY2YTA0Y2ZkMWEiLCJ1bmlxdWVfbmFtZSI6ImFkbWluQE0zNjV4MjU2MjU1Lm9ubWljcm9zb2Z0LmNvbSIsInVwbiI6ImFkbWluQE0zNjV4MjU2MjU1Lm9ubWljcm9zb2Z0LmNvbSIsInV0aSI6IllScTlJakpPdGtHZ000aV9kMHM4QUEiLCJ2ZXIiOiIxLjAiLCJ3aWRzIjpbIjYyZTkwMzk0LTY5ZjUtNDIzNy05MTkwLTAxMjE3NzE0NWUxMCJdLCJ4bXNfdGNkdCI6MTU1ODM1NTQxNn0.YPAHE4BF5ETvzOqr57XfYVeWWinBIefdHbmeL07uA1VbhfrHDMDZDfDm0SDOFCkycTif61NkVvgTiOktnPZ_dCQ9bewIwmjA4UuqkkVqd1tnAFE_51USygRmHzxq7A6l3_vV7Gt4cXhpVRIHCVoeYfZd9tlPnFF6btYEMshYSwO34jwypBj18aRdym_rUDTyR0VSB7T_-5GP-Q-CuAbAQUzST_jBVJxZBMWSE2oa279whXMW2vOT0mNxWghkonAyWu3xM7wbF-RxuoWQIGsvrKoG8EjBghiOURvC8cgQCTfENMyjwCI51lAtPUT0WRMefyqSgybeS5vNQAtDyqJTRA&lt;/strong&gt;",
"refresh_token": "&lt;strong&gt;AQABAAAAAADCoMpjJXrxTq9VG9te-7FXrnU4WCA_JuDz_VXPLe01goWmMsJRhRMunWfKtXj3Lmz815Uikusp6KsoQbaY9830i9CHGAO61KRZ_UPlXj-jSS2-AO84bEXAMieeyjLuHExTaJc2lvYXrmge29rpOj9eUCyVuOfDIPG0KU8vx8n0YDGRThkeXIW45Ig8ezgCzNNapah23B6JO2-Hf_sl34BIA4PayRfUZPCXVQESKlg5HS5ajs-mAlfpAO-iT_-o5xBzpKdhL__ZiNS6SNPI3j1mClrXOK_v73vj9tZQhQzBZIfTWLVFTyndxpWAj2oBdcA10YfABzrehKctYSqju1sIrLCKR4jsGHpyNdKr9Pt9INlyTEGeUi51-npcFbZiTvjMTZjHNLKHqMyyjCX7AsohxanuTKmEAxAoC_xXOO74r3gvEE5Rj1bnKMVbvlDNAs9bV8tGpkoB6hPok9L7tnWlMXC7r6HLmH2duzT9cnW9PlY5XTj0A4QZkcq-1plb4x88ICCkPLmq2KQRBRyG0UFcUy7f_LjX04pWOywdVwlMgFu_w9xgCcAXjRCHys_U0CSRLlC_iJRSm4pPBBqH8HsIiTn5CjQl5THi8ArnLw2cyYu5xKK_JaaiNv_pPTfZLIA5nspxewfVtdJiFyRYf2NKmjzHLhkX_nwTXnO_6bHEdrlri8prD3h0Cp5KiW8f7-UbFD3PN9LW2iAy4BnYG5tlUUUoArd11jPo2dtjx4CJY3GUQoPfkOftO6zrni01OmYiGMZajEHxmWuU8dwUNRgKERDX_A04QNq8I2cowZvB2RV_2vPd7-utEcXTKKB5AGogAA&lt;/strong&gt;",
(...)
}

 

Once you have the access token, you can start calling Microsoft Graph resources for an hour. After that, the token becomes invalid. Getting a new access token using the refresh token is almost the same procedure as getting your first access token: make a POST request to the “token” endpoint using the following body:

 

grant_type=&lt;strong&gt;refresh_token&lt;/strong&gt;
&amp;amp;client_id=&amp;lt;client_id&amp;gt;
&amp;amp;&lt;strong&gt;refresh_token&lt;/strong&gt;=&amp;lt;refresh_token&amp;gt;
&amp;amp;redirect_uri=http://localhost
&amp;amp;resource=https://graph.microsoft.com/
&amp;amp;client_secret=&amp;lt;client_secret&amp;gt;

 

Phew, that was quite a ride 🙂

 

Pulling in the Reports

Ok, now that we have the right access level and access token, we can finally (!) start getting our report data.

The REST API for the Microsoft Graph Reports API is rather simple luckily. For example, if we want to poll SharePoint Storage metrics for the past 30 days, send a GET request to

 

https://graph.microsoft.com/v1.0/reports/&lt;strong&gt;getSharePointSiteUsageStorage&lt;/strong&gt;(period='&lt;strong&gt;D30&lt;/strong&gt;')

 

This will only work if you give it a valid access token. Providing the token in the request is done using HTTP headers. Add the ‘Authorization’ header and set it to the following string:

 

Bearer &amp;lt;access_token&amp;gt;

 

Note that there is no colon ‘:’ between Bearer and the access token (I’ve made that mistake too often).

When the access token expires, you’ll get this error:

 

{
"error": {
"code": "InvalidAuthenticationToken",
"message": "&lt;strong&gt;Access token has expired.&lt;/strong&gt;",
"innerError": {
"request-id": "41f62a4a-fc3d-4632-81bf-da01bc134219",
"date": "2019-07-03T09:15:06"
}
}
}

 

When you do pass an active access token, you’ll get the report data in CSV format. There is an HTTP redirect going on in the background, but most web clients handle this fine, so don’t worry (too much) about it (just yet):

 

Report Refresh Date,Site Id,Site URL,Owner Display Name,Is Deleted,Last Activity Date,File Count,Active File Count,Page View Count,Visited Page Count,Storage Used (Byte),Storage Allocated (Byte),Root Web Template,Report Period
2019-07-01,12be4953-39cd-44ee-abca-e6bf7ef6abe4,https://m365x256255.sharepoint.com/sites/Retail,Retail Owners,False,2019-06-27,36,6,2,2,38416844,27487790694400,Group,30
2019-07-01,14dcfbfb-0fff-4b4b-9da4-930e909f8af6,https://m365x256255.sharepoint.com/sites/Mark8ProjectTeam,Mark 8 Project Team Owners,False,2019-05-21,58,0,0,0,258760387,27487790694400,Group,30
2019-07-01,95742ad5-159a-426c-92e9-af14eef9be1e,https://m365x256255.sharepoint.com/sites/operations,Operations Owners,False,,28,0,0,0,25115628,27487790694400,Group,30

 

That looks a lot like CSV data we can use in any reporting application!

 

Enter Power BI

(or Excel using Power Query or your own BI tool)

Getting the data in our report is easier than ever. Just Get Data from Web:

Get Data in Power BI

 

Click Advanced and enter the URL for the report you want:

Configure the Web data source

 

Choose anonymous authentication (as we’re already authenticated using OAuth):

Select Anonymous authentication

 

And there’s our data, right in Power BI!

Data Import Wizard in Power BI

 

But wait a minute! In just an hour, that access token is going to expire, and I won’t be able to refresh my report anymore!

 

Supporting Automated OAuth Flow

Of course we can’t expect users to do this every hour to refresh a report.

There are a few options you can go from here:

  • Build an Azure function that handles the authorization flow for you
  • Create a custom Power BI connector that supports OAuth (Microsoft Docs tutorial)

 

My next blog post in this series will cover the Azure function building that supports all reporting tools (yes, that’s you Tableau lover!).

For the Power BI fanatic, you can read up on a tutorial from Microsoft Docs detailing how you can achieve this with a custom connector and some ninja M code.

 

Wrapping up

I hope you’ve learned a thing or two about OAuth after diving through this article and that you’ve connected to the activity API’s successfully by now.

 

Written by Sebastiaan Mindreau, Office 365 expert at LoQutus.

 

Get in touch if you have any questions or to see how we can help you!