From Office 365 to Azure Event Grid, the events must Flow

Photo by  Archana More  on  Unsplash

In this blog post, we capture all the events across an Office 365 Tenant from multiple event sources, gather them, and send them through an Azure Event Grid.

We then listen, filter and handle our events in a central, unified way.

The events must Flow.


This is also the full write up of this microblog posted to Twitter #FlowNinja earlier this month.


Plan

  1. What benefit do we get from this?

  2. Listen to every event across an Office 365 Tenant

  3. Construct a uniform event message

  4. Send them into a Serverless Event Solution - Azure Event Grid

  5. Filter and catch our events

We want to build 3 Flows

flow-event-grid-1.jpg

What benefit do we get from this?

First, we see the increasing availability of event hooks - we have subscriptions, delta queries, or webhooks, across various different products in Office 365. Some products like SharePoint is getting a SocketIO webhook. There will be more events, and our event handling design must evolve.

Second, we see the cost-effective solutions to handle these ever increasing flood of events in the form of Serverless compute. This is true with Azure Functions, Microsoft Flow or Azure Logic Apps.

We end up with a lot of individual event sources and a lot of individual event receivers. This is a common event handling problem. As number of events we handle increases, the worse the event management problem becomes.

Consider you have a “handle a document uploaded to a library” event - a very typical SharePoint Workflow. Now consider this library is cloned to a hundred project sites.

If we clone the event handler a hundred times, we have a problem.


If you have already done this with Flow, then try https://FlowStudio.app to help you manage them.
Ooh inline product placement!


If you are a developer, then consider this scenario.
Consider a typical event handling in the browser. A decade ago we used jQuery like this:

// 2008
$('button').click(handler);

// 2018
$(global).on('click', 'button', handler);

And gradually we find that unacceptable, because we have buttons, events everywhere, and managing individual event hooks was tedious and error prone. Eventually, we moved to a global handler model, and we filter just prior to event being raised.

The headache-less way to handle events is to set the hooks all at the global root level, and then filter by the event source and event type.

That is the exact reason we need Azure Event Grid.

  • Centrally manage our events

  • Decouple the event source from event handlers

  • Stay sane, with a hard problem

Listen to every event across an Office 365 Tenant

I had previously wrote about listening to Office 365 Management API via the HTTP action and app-only permissions. What I did not realize was that the Office 365 Management API also have fantastic webhooks.

I read about the webhooks from Kent Weare’s post, where he uses this event to get a trigger when Flows are created in the tenant.

https://flow.microsoft.com/en-us/blog/automate-flow-governance/

These are grouped into several categories: AzureAD, Exchange, SharePoint and General (other).

This is the subscriber. One picture of 4 blocks. I’m subscribing to three webhooks at once.



The Office 365 Management API is a fantastic general event source. The downside is that it’s not instant - event handler is called between 10-20minutes after the actual event. So it is great as an audit webhook, or for scheduling files or search or to signal for a bot to re-scan a document. But it’s not an instant webhook.

Of course, if our goal is to send events into an Event Grid - we can work with multiple event sources at the same time. We can add Microsoft Graph events or subscribe to SharePoint list webhooks directly.

This is the top of the Listener

Construct a uniform event message

The event grid has a event JSON structure, it also supports a CloudEvent structure.

When I built my implementation, the Event Grid connector is still in preview and I had troubles publishing a Cloud Event structure. I assume this wouldn’t be a problem anymore as the connector evolves.

This is the final loop design - all done.

Remember, we are running Serverless so abuse/utilize every opportunity to use as many Azure Servers as you can - if you can fan-out to parallelism you must.

Don’t talk to each individual HTTP action one at a time. Do (up to 50) all at the same time.


Send them into a Serverless Event solution - Azure Event Grid

The Azure Event Grid is a serverless event processing pipeline. It decouples our event source(s) from our event handlers.

Here is our first handler.

This catches every event on the Event Grid - in Event Grid, we see we have our first webhook attached - it appears as a LogicApps webhook.

Here are three examples of what it caught:

  • Flow created Event

  • Site Collection created Event

  • File uploaded Event

flow-event-grid-3.jpg

Filter and catch our events

We see the very specific webhook now registered on the event grid - and the filters are also listed

PPTX filter only runs when the file I’ve uploaded is a PowerPoint file.


Summary

I have been talking a lot about Serverless and how our tools and design must evolve. Having a unique Office 365 to Event Grid solution is something I talked about as far back as 2017. I’m glad a year later I’ve finally got a great prototype going.

  • Office 365 Management API is a great webhook source that catches all sorts of events. The downside is that it is an audit webhook, so the delay may not be acceptable to your needs.

  • Using Azure Event Grid to perform filtering and subscription gives us the unique ability to see EVERYTHING that’s going on in our tenant. That has tremendous value.

  • Because event source and event handling is now decoupled - we can add new event sources to push to the same Azure Event Grid. We can do this from Microsoft Graph, we can do this from SharePoint, or we can do this from a whole myriad of triggers available in Microsoft Flow

  • We can write Azure Functions to trigger off the Event Grid, and it would be visible as well.

  • I was reading and appreciating sending events to the new Azure SignalR service from Azure Functions - that would be pretty amazing to convert an event grid message into a websocket event.
    https://twitter.com/nthonyChu/status/1044427579460145152

The possibilities are endless. Our tools and our design must evolve.

Gaps between PowerBI streaming tiles and SharePoint

So I spend an evening playing (I actually have a lot of fun exploring these things) and figuring out how the pieces of SharePoint, PowerBI and Flow are supposed to work together.

In my head - they already connect.  But I have never seen anyone blog them.  So I decided to give it a stab.

Turns out there are some gaps.

The Idea

The Idea is simple.  We can create a PowerBI that uses SharePoint List as a datasource.  But instead of configuring scheduled refresh, we want to use the PowerBI Rest Dataset to push data in a streaming way.  And since Microsoft Flow has an action to do this, as well as the triggers to listen to SharePoint List.  We can get SP List-push-to PowerBI without needing schedule refresh.  That is a crazy fun idea.

The Reality

The reality is that there are several gaps.  These are probably solvable, but I just want to list them first, and we'll tackle them in the future.

Gap 1.  SharePoint List dataset != Push-enabled REST dataset

PowerBI makes a distinction between what's a REST/Pushable Dataset vs normal datasets like external lists.  In fact, Flow can not connect to a non-REST dataset.

So we need to create a REST dataset in PowerBI Service (it is not a feature of PowerBI Desktop), and then use the REST dataset as a live connection in a PowerBI Report.

Gap 2.  The only way to create a PowerBI REST dataset is via the REST API. 

There is no UI.  Ouch.  That pretty much makes this a developer task.  OK that's fine, we create a REST dataset via REST endpoint and a JSON schema (double ouch).  

Now we can build our PowerBI report, connect the REST dataset from PowerBI Service.  We save and publish this report to PowerBI Service and then insert the PowerBI Report in an SPFx webpart (PRO license needed for embed) into a SharePoint modern page.

This part is actually really seamless.  Don't worry, we have more gaps.

Gap 3. PowerBI report does not livestream REST dataset results.  

So I'm staring at my PowerBI visual in a SharePoint modern page.  In a separate window, I update the source SharePoint List.  In yet another separate window, I can see the Flow ran and push the new list item into the streaming dataset.

Excellent.  Except, the SPFx PowerBI Report Visual isn't updating.  It doesn't update.  I waited 15mins for it to do nothing!

If I F5, then I immediately see the new value.  But it doesn't do live streaming refresh :-(

It turns out, to see live stream results we need a PowerBI Dashboard or PowerBI Tile (streaming tile).

PowerBI Dashboard can only be created in the PowerBI Service.  We take an existing report and pin the visual.  This asks us to add the visual as a tile in a PowerBI Dashboard.

Gap 4. SPFx PowerBI report webpart does not show PowerBI dashboard embed.

So I create a PowerBI Dashboard and I go back to the SPFx PowerBI Preview webpart.  Only to find it doesn't do dashboard embed.

It only does Report embed.  So we will need to build our own SPFx that let us do dashboard embed.  This requires a embed token from MSGraph - but we should be able to piggyback the graph-util helper in SPFx to do our token exchange.

There's potentially one more issue.

Gap 5.  Does embed PowerBI Dashboard or Tile actually connect to streaming datasets?

I don't know the answer to this yet.

Gap 6. PowerBI REST Dataset endpoint can only add rows, not update them

The REST API lets us add rows to a REST dataset easily, or clear the table.  But there's no way to update an existing row.

The use case for the streaming REST dataset is like a ongoing stock ticker or temperature meter.  You don't update a record that's streamed past.  You only care about new records.

Flow only has an action to add row to PowerBI Dataset.

Gap 7. Flow does not have an action to Clear the dataset

The REST API lets us clear the dataset table, so technically, I could clear the table each time and repopulate it with the entire list again.

But unfortunately, Flow only has one PowerBI Action - add row to a REST dataset.  It does not have a clear rows action.

More work to do, more exploration to be had

Parts of the puzzle works really well.  Flow pushes data freely from SharePoint list changes into the streaming dataset.  If the dashboard or tile is shown on a webpage by itself, we immediately see it update like magic.

But if we want the dashboard/tile embedded within a SharePoint modern page.  There's still work to be done.

Ultimately, if we want live streaming data capability, it might be easier to use PowerBI LiveQuery against Azure SQL, and have Flow push data into that, instead of PowerBI REST streaming Dataset.

 

 

 

Working with SharePoint WebHooks with JavaScript using an Azure Function

This blog post covers additional explanation on how to subscribe to SharePoint WebHooks and have it running with only JavaScript in Azure Functions.  The entire code is in one single JavaScript file.

The SharePoint team delivered on the promise to ship SharePoint WebHooks, and made an reference example in C#.  Be sure to watch the PnP webcast as well.

What is Azure Functions?

Azure Functions, in the simplest sense, is a Azure WebJob that lets you run a JavaScript function in a file (it can do C# too), and it will run it for you when you trigger it.  Because it is a Serverless platform - you don't pay for the WebJob unless your function is running.  This makes it really economical (you also get like a million free runs per month...)

Azure Function comes with a super user-friendly UI and you can paste or upload your JavaScript directly in the browser.  You don't need any tools installed.

Running in the browser

 

Of course, this is server-JavaScript.  So think NodeJS.  We can talk to lots of REST APIs (Graph, SPO) but there will be no browser DOM.

What can you do with this?

You can trigger it on a timer.   Hey that sounds like a SharePoint Timer Job.

You can trigger it on a web request.  This is super useful if you have a client-side UX and you need a button to do some high level elevated permission action.  You call the Azure Function to do it for you, using an App-Only permission elevation.

Why is SharePoint WebHooks important?

A SharePoint WebHook is a REST endpoint that you can attach a remote End Point to.  Right now, there is only a List endpoint.  So any updates to the list items will trigger an event, and SharePoint will call your function.

Hey.  Wait that sounds like a SharePoint Remote Event Receiver.  You are right!

Design

So the idea of our function is this - when triggered:

It will Auth and then talk to SharePoint REST and do one of the following.

  • Check if it has a request parameter "subs" - then it will list the current subscriptions on our target list
  • Check if it has a request parameter "sub" - it will try to attach itself to the target list
    SharePoint will immediately call the function with a validationtoken parameter so…
  • Check if the request has a validationtoken parameter - it will immediately reply with that token as text/plain.
  • Skipping all three conditions, it will run the default action
    The default action is that it will add an list item in a different destination list.  Because the function is running on its own App-Only permission, it can update a list that the original user doesn't have access to.

 

GET Subscriptions

options = {
    method: 'GET',
    uri: "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')/subscriptions",
    headers: headers
};
request(options, function (error, res, body) {
    context.log(error);
    context.log(body);
    context.res = { body: body || '' };
    context.done();
});

GET subscriptions.  Array result is [] empty by default.  It has subscriptions after you attach hooks successfully.

 

POST Subscription (to add itself)

options = {
    method: 'POST',
    uri: "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')/subscriptions",
    body: JSON.stringify({
        "resource": "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')",
        "notificationUrl": "https://johnno-funks.azurewebsites.net/api/poke-spo2?code=q8sq9wxm62asd-YOURTRIGGERCODE",
        "expirationDateTime": "2017-01-01T16:17:57+00:00",
        "clientState": "jonnofunks"
    }),
    headers: headers
};
request(options, function (error, res, body) {
    context.log(error);
    context.log(body);
    context.res = { body: body || '' };
    context.done();
});

Sending POST subscription without handling validation token.  The request fails.

 

Handle Validation Token

// if validationtoken is specified in query
// immediately return token as text/plain
context.log(req.query);
context.log(req.query.validationtoken);
context.res = { "content-type": "text/plain", body: req.query.validationtoken };
context.done();

The function is called twice.  Second time by SharePoint to validate the subscription.

Result Video

The Poked list item was created by "SharePoint App" not "John Liu" the user.

Notes

This demo builds on top of the code from Azure Functions, JS and App-Only Updates to SharePoint Online that covers authentication with certificate, and running AppOnly permissions. 

Additionally, I've moved ClientID and Certifate ThumbPrint to Azure Function App Settings.  This means they are no longer part of the code.

App Settings

var clientId = process.env['MyClientId'];
var thumbprint = process.env['MyThumbPrint'];
Function App Settings > Configure App Settings

Function App Settings > Configure App Settings

 

Source Code

I'm making an effort to put all my demo source code on GitHub going forward.  This is part of the "Upgrade Your JS" demos.

https://github.com/johnnliu/demo-upgrade-your-js/tree/master/azure-function-web-hook

Let me know what you think about SharePoint WebHooks, Azure Functions and JavaScript that will rule everything ;-)

If you spot a bug or want to update the code - send me a Pull Request.

 

All Demo Downloads will be on Github - blog housekeeping

I've taken a short break from writing blog posts - I haven't been idle, I have been writing something.  Hopefully to be able to share it with everyone soon.  Anticipation is killing me.

As we roll into a new month September!  There are a few planned updates I'm doing for the rest of the year...

  1. Several old blog posts that was in Draft will be merged and published.  These are summary posts from the Office 365 Saturday events I've been going to.
  2. I've been in several SharePoint Sydney user group sessions and that needs summaries too.
  3. SPFx is announced, and now Developer Preview.  Posts there too.
  4. I'm looking around to see what's the best way to record some video sessions as I retire them to the archives.

First big announcement.

All future demo downloads will be on Github!

The download files for my demos on Upskill Your Javascript - from building JS WebParts for SharePoint to Office Add-ins and Azure Functions is up first.

https://github.com/johnnliu/demo-upgrade-your-js

The main driver for this is that the files are updated overtime, and Github really provides a much better place for me to point people to and say the latest files are over here.  Check it out, and if you have Issues - tag them directly on the lines.

So that's the first of the big news.

Seems obvious now...

Seems obvious now...

 

 

Update on SharePoint and Office Development - 2016 Feb edition

We rotate through different topics and presenters in the Sydney SharePoint user group.  But I finally got my turn to present a developer topic, and I wanted to do a quick primer on all things Office Developer related.

This turned out to be a REALLY complex talk.  I wanted to cover ALL the cool new stuff in Office Dev.  But as it turns out every month (and now, every week) new things come out. 

I ended up with:

  • Brief Introduction to dev.office.com
  • Brief Introduction to PnP
  • Where we have been with SharePoint Add-Ins
  • Where we are going with Office Addins: 
  • NEW API: Microsoft Graph API
  • NEW AUTH: OAuth 2.0 and ADAL(js)
  • NEW TOOLS: New tools with Node, NPM, and Yeoman Generator (YO OFFICE)
  • Build a demo Office Addin that talks to both SharePoint Online and Microsoft Graph
  • Run on web and desktop

The presentation clocked in at 1 hour and 20mins - I started a bit earlier.  And kind of flopped off at the end as I run out of steam after the demo :-O

Then we gave out all the swag from dev.office.com so all is happy faces.

The PowerPoint presentation is here:

2015 February Presentation - Update on SharePoint and Office Development

 

Demo Fail

So at the end, the Office Add-In didn't load from the App Catalog on my desktop.  I went home and got it to work, here are screenshots to proof it.

Oh NO - no apps

Add SharePoint App catalog to trust center

 

Here it is.  The same addin working on desktop - talking to Microsoft Graph and showing Group Conversations