Wrapping Office 365 Activity Reporting – the Azure Function

This post continues on my previous post about Accessing Office 365 Activity Reports using Power BI or Excel – the OAuth Question.

In this post I’ll talk about a solution to wrap OAuth madness into an Azure function, so your reporting tool doesn’t have to think about refresh or access tokens, mostly because they don’t support this (yet).



The problem we’re trying to solve here is the fact that most reporting tools don’t support refreshing your access token when accessing OAuth secured resources like the Microsoft Graph Reports API.

This makes it hard, almost impossible to refresh these reports in an efficient and user-friendly way.


A Possible Solution using Azure Functions

The following schema shows how this solution can work (there are possible alterations for your taste):



    1. In Power BI, the user constructs the Azure Function URL (not the report URL) and adds the client secret as a parameter, so this function can’t be used by unauthorized users who don’t have the client secret.
      The Report URL is also specified, so the user can easily change the report to be used.
      This URL looks like this:
    2. The Function calls Azure AD to get a new access token. Note that the refresh token is stored in the Azure Function settings (this requires a one-time authorization code flow to get the first refresh token).
    3. Azure AD returns the access token (that expires within one hour).
    4. The Function can now use the access token in the Authorization HTTP header when calling Microsoft Graph.
    5. The API returns with the requested report in CSV format
      (that’s just how they roll 😉).
    6. The report is returned to Power BI in the exact format it was received.

I really hope that was easy to follow 🙂

The Code

Ok, now let’s dive into the code that corresponds to these actions.

This defines the function itself and registers it with an HTTP trigger accepting GET and POST (although GET would have been enough here). No specific routing options are required.

public static class WrapAPI
  public static async Task Run([HttpTrigger(AuthorizationLevel.Anonymous,
     "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)

Next comes some basic stuff I won’t share, getting the query string parameters url and client_secret. There’s nothing special about that but it needs to be secured as to not throw null reference exceptions etc…

We then read five importants settings that we need for the authentication flow:

var clientId = GetSetting("ClientId");
var redirectUrl = GetSetting("RedirectUrl");
var resource = GetSetting("Resource");
var tenantId = GetSetting("TenantId");
var refreshToken = GetSetting("RefreshToken");

We need the Client ID to be able to identify which app registration we’re talking about (and hence the correpsonding permissions). We also need to redirect URI as Azure AD wants to know that. The resource parameter is required to tell Azure AD (Kerberos) which service you want a token for. The tenant ID is required to direct the authentication to the correct tenant. And of course we need the refresh token that was retrieved manually before (once).

Next, you have a few options. You can either use an Azure AD authentication framework like ADAL but I’d rather just use basic code with my favorite REST client – RestSharp:

var client = new RestClient("https://login.microsoftonline.com/");
var request = new RestRequest(tenantId + "/oauth2/token", Method.POST);
request.AddHeader("ContentType", "application/x-www-form-urlencoded");
request.AddParameter("grant_type", "refresh_token");
request.AddParameter("refresh_token", refreshToken);
request.AddParameter("client_id", clientId);
request.AddParameter("client_secret", clientSecret);
var response = client.Execute(request);

We’re just building a simple POST request to retrieve a token, based on the ‘refresh_token’ grant.

The response is then decoded (JSON object) to retrieve the access token inside:

var o = JObject.Parse(response.Content);
var accessToken = o.SelectToken("access_token").Value();

Now that we have the access token, we can make the call to the Microsoft Graph endpoint:

client = new RestClient(url);
request = new RestRequest("", Method.GET);
request.AddHeader("Authorization", "Bearer "+ accessToken);
response = client.Execute(request);

We’re using the url that we’ve received in the call, so that’s the URL that the user specifies in Power BI. We don’t need to add extra parameters, except for the Authorization header and the access token.

We want to make sure we return the exact same response as we’ve received from Microsoft Graph, so we’re creating a simple HttpResponseMessage:

var r = new HttpResponseMessage(HttpStatusCode.OK);
r.Content = new StringContent(response.Content);
r.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue(response.Headers.Single(h => h.Name.Equals("content-type", StringComparison.OrdinalIgnoreCase)).Value.ToString());
r.Content.Headers.TryAddWithoutValidation("Content-Disposition", response.Headers.Single(h => h.Name.Equals("content-disposition", StringComparison.OrdinalIgnoreCase)).Value.ToString());
return r;

We have some funky stuff going on here (agreed). First, we’re setting the content from the Microsoft Graph’s response. Next, we’re setting the ContentType and ContentDisposition headers just as we’ve received from Microsoft Graph. The ContentType will probably just be octet-stream, but the ContentDisposition contains the name of the report and most importantly the .csv extension. This is an important and powerful hint to Power BI to invoke CSV transformation on the incoming string.

Then, we only need to return the response we’ve just built. Not that hard at all 🙂



We’ve faced a few challenges along the way, and I’d like to take the time to elaborate on them a bit.

      1. The typical response creation in Azure Functions is to use  req.CreateResponse(status, content). The only problem is that when you return a string, it is encapsulated with extra leading and trailing double quotes (“). This makes Power BI and most reporting tools not understand the response.
      2. Security: We’ve now secured the Function calls with the client secret. This is obviously not the best idea because if the report is compromised, the secret needs to be regenerated and replaced in the report everywhere. My friends from the BI team here told me it can be parametrized in Power BI, but still makes for a weak solution.
        A better way would be to secure the Azure Function itself to authenticate over Azure AD, requiring users to authenticate using their organization ID before you can call the Azure Function. In the end you could maybe even think about pass-through authentication then, but that’s a great idea .. for another blog post 😄
      3. Keeping your settings in Azure: When you keep your settings in Visual Studio, they come from a file called local.settings.json. These don’t get deployed automatically to Azure. I’ve created them manually in the Azure portal, but I’m sure the guys from our Microsoft team have better suggestions for that 🙂
      4. When your service requires a new set of permissions or should get more permissions, your refresh token needs to be recreated. This is so because both refresh and access tokens also contain the permission levels/scopes that they hold. So that’s a thing to consider as well when changing permissions.
      5. Better use for your access tokens: In this solution we’re not re-using access tokens at all, we’re simply requesting a new access token for each and every report. A better way would be to store the access token and only refresh it once it’s expired (or close to expiry). This would complicate the Azure function (adding a storage solution), but also makes it stateful, which feels like a bad idea.



We’ve walked through the process of setting up an Azure function to wrap the OAuth authentication flow and how this interacts with Microsoft Graph, Azure AD and Power BI.


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!