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

/
Photo by Archana More on Unsplash

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.

Make a wishing wand - why we need a MicrosoftFlow Custom Connector for MSGraph $Batch

/

A question that has been in the back of my mind since my late night demo of building a Microsoft Flow Custom Connector for Microsoft Graph is this.

I focused on the "how" - because I wanted to show that the whole thing can be done in about 10 minutes.  And I didn't want to skip steps like "oh here's how you register an Azure AD app".

 

But (and this is my fault, I did only ask for 10 minutes) I should have spent some time to talk about the "why"

Why do we need this?  What problem does this solve?

Why does John think this is super special?

This is special because, a $Batch connector is a "Send HTTP Request to MS Graph" as me.

It is a wishing wand that grants wishes. Creating MSTeam is a wish. We didn't build a wish. We built a wishing wand. Then we waved it around.  MS Teams API currently is delegate permission only, so we call it through a $batch connector.

Planner API is delegate only.  Any time you see a delegate permission MSGraph you remember you have a wishing wand.

The Ingredients:

Microsoft Flow and Logic Apps has concept of connectors (these are essentially API definitions) and connections (these are instances of connectors bound to a resource as well as cached automatic authentication)

A Flow is a JSON description of how actions and triggers on the connection are chained together to perform automation.

We do have a general purpose HTTP action, and it works very well for app-only permissions that needs client-id/client-secret or client-cert.  But HTTP action is not backed by a connectors framework so it can't remember credentials.

To build a general purpose tool and remember credentials for Microsoft Graph, we have to build a custom connection.  This is basically a swagger API file that tells Flow and LogicApps what APIs exist on the resource.  In the case of MSGraph, that list is enormous.

The Recipe:

The crazy part though, is MSGraph provides a $batch endpoint, so the recipe for building the magic wand is simply this:

  1. Build a custom connection with one method - $batch - this is a POST
  2. Pre-authenticate it with a delegate permission
  3. Wrap requests (1 or many) as a JSON array and call them via the $batch connection.
  4. We have a magic wand

Agenda for the September community call included Microsoft Graph updates, Microsoft Graph and Microsoft Teams, and custom connectors for Microsoft Graph $Batch

Where do we go from here?

I demoed this with a Manual button trigger.  Jeremy mentioned (in the video) that this can easily be a SharePoint trigger.

This can also be a HTTP Request trigger, turning your Teams Provisioning into one simple HTTP Webservice.  You can hook that up to external systems, PowerApp or a bot.

A technical note

Sometimes you discover you need extra permissions on that Azure AD app.  We do this by going back to Azure AD portal and add the extra permissions.  You can "grant permission" through the AAD portal.

If the cached connection in Flow refuses to work, I find that we may need to right-click on the custom connection and re-authenticate.  Sometimes this requires us to switch to a different user then switch back.  Because Azure AD dynamic consent is just so hard to do.

But it will work.  Just need to toggle a few times.  I do this a lot.

Resources

 

Microsoft Graph Community Call September 2018 - $Batch and Flow

/

I wanted to write a quick blog post letting everyone know I've put my hands up to present a 10 minute demo on using a Custom Connection for Microsoft Graph $Batch in Microsoft Flow.

Custom Connection = Swagger = WSDL

In Microsoft Flow, Power Apps or Logic Apps, a Custom Connection is a Swagger/Open API definition that allows these low-code solutions access to a well defined API, so it knows what to call and how to call them.

For old API guys that knew what a SOAP Webservice looked like, this is the WSDL that tells other systems how to use this service.

In general, calling Microsoft Graph with App-Only Permissions requires only a client-id and client-secret pair, and we can do it directly via the HTTP action without needing a custom connection.

When we do need to create a custom connection, that's usually because we need to call something with Delegate permissions

Why Flow - Connectors Framework

One of the hidden supper power of Flow and Logic Apps is the connectors framework, which cache, renew and manages connections across various different APIs and auth methods.  

This means - you actually don't need to write any of this code.  Repeat - as a developer, you don't have to write code to manage auth.  Auth is encapsulated away and auth code is no longer your concern.

Why $Batch

You'll have to see the demo (I'll update this later).  Hey, I can't spill all the beans in one blog post.  This is a tease.

The result

If you want to automate any delegate-permission API calls on Microsoft Graph, then Microsoft Flow is the easiest way to do it - far easier than writing code, and this is considered a low-code solution, it's crazy nuts.

Resource

Link to the recording will be updated here once presentation is done and uploaded.

Do bulk insert/update in Microsoft Flow with two simple elegant filter-arrays

/

This is a blog post of a pattern that I've brooded over for months and finally came to an elegant solution.

Plan

  • Scenario: Bulk Insert / Update
  • Doing filter-arrays elegantly
  • Build it in two parts
  • Run it super quickly
  • Bonus: extensions

The style of this blog post mirrors my previous post on Do Group By in Microsoft Flow with two simple elegant loops.  I presented this during the June 2018 Microsoft Graph Community call.  This blog post is the text, searchable, extended version.

Scenario: Bulk Insert / Update

Bulk insert and update is a very common problem with scheduled Microsoft Flow.  Basically, we have a list of resources - it could be a SharePoint list, an Excel file, a CSV import file, a SQL table, result of a Microsoft Graph call... etc, and we want to either update or insert the entire table into a destination - another SharePoint list etc.

In the example, we will do this with Microsoft Graph's List Groups call.

 

First, call Microsoft Graph groups with a GET request - this gets back a JSON array of Office 365 Groups.

 

We need a SharePoint list to write this to - I made a list with a few columns: groupId is important, the rest is just useful: description, visibility, createdDateTime and renewedDateTime.  I also suggest deletedDateTime, since now groups have a recycle period before true deletion.

 

The plan is this: when this Flow runs, we call Microsoft Graph for list of groups, then perform fast-bulk update/insert into SharePoint list.

 

Doing elegantly with filter-arrays

The first step is to select a list of existing groupIDs from the current SharePoint list.  We do this with two actions.

What is this magic trick?  Select action has the ability to map array of objects into... anything we want.  A bit of code may explain this better:

var results = array.map( (item) => { "id": item.groupId } );
// results = [ {"id":"xxx1"}, {"id":"xxx2"} ]

var results = array.map( (item) => item.groupId );
// results = [ "xxx1", "xxx2" ]

The first UI with key/value lets us create result arrays of objects that has key/value properties.  The second UI form with just "value" lets us create flattened array of string, or number, or really complex nested objects.

Anyway, the result of the Select action is now an string array of groupIds that I've already got in my SharePoint list.

Then we follow with two Filter-Array on the JSON result from the source list: the Microsoft Graph array.

So we have two Filter Array actions.

"New FilterArray" contains rows from MS Graph Groups whose "id" does not exist in the array of "groupIDs" from SharePoint.

"Update FilterArray" contains rows from MS Graph Groups whose "id" already exists in the array of "groupIDs" from SharePoint.

 

Build it in two parts

The hard part is already done, the rest is simply wrapping it up.

Hew done!

The benefits:

  • Compose, Filter, Select are native Flow/LogicApps engine expressions and they run super fast.  Use them to do all the filters and sort and array.map
  • Do not check SharePoint for each row - so the SharePoint connector speed is much faster

For-Each is still slow, but we can do more

Run it super quickly

Now, we have two arrays and two loops through them, one does only create and the other does only updates.  They don't need to wait for each other.  In fact, they can all run in parallel.

This dramatically drops the duration

 

Extensions

We can add a really simple extension

  • When there are "new" records, we want an email about this.
  • Connect Flow Management's "List Flows as Admin" and be notified when new Flows are added to your environment by anyone.
  • Listen to Office 365 Service Admin Center messages and be notified when new events happen.  Push them into Microsoft Teams channel for discussion.

 

Send mail as anyone - #MicrosoftGraph and #MicrosoftFlow (bonus: inline image attachments)

/

Sometimes while browsing MS Graph permissions, you come across something like this:

This is an Application Permission, it says "Send mail as any user"
The correct next step is of course we drop whatever we've been working on and immediately play with this.

Plan

  • Explore what is Send mail as any user
  • Do attachments
  • Do inline attachments

First, while still in the Azure Portal - grant this permission to the current service app.

Next, we head over to Microsoft Graph Explorer and grab a sample of the Exchange SendMail 

sendMail is usually on: /v1.0/me/sendMail for delegate permissions, but since we are testing sending email as another user, I replaced the URL to:

/v1.0/users/{user-id-guid}/sendMail

This is really as simple as it looks.

Gandalf is now asking me to try the new cafeteria.

What can you do with this?  Lots of people ask the Flow team how they can send email on behalf of the current user onto their managers.  This will do it easily, without having to worry about delegate permissions.

Extra notes: the service app does not have permission to read the user's inbox or login as that user.

And if it's so easy to send emails via MS Graph, let's try attachments.

Do Attachment

We add the JSON for attachments.  For this we need the $content-type and $content

flow-sendmail-json-attachment.jpg

Result:

 

Now, do inline attachments

Why do we do inline attachments?  There are few options to embed images as part of your rich HTML email.  You can use background image via CSS - this is mostly ignored by everybody.  You can use inline img with dataUri - this pretty much only works in iOS.

The oldest way and still the most supported way is to do inline attachment.  The trick is that your attachment must have an additional content-id (cid:) header with a unique name.  Then the HTML mail body can refer to that image wtih <img src="cid:xxxyyy" />

I left the original attachment as a comparison.  See the second attachment is marked inline and has a contentId - boromir12345.  The contentId is used in the HTML content as a embed reference: cid:boromir12345

Result:

Summary

MSGraph lets you send email as anyone.  
It also gives you much better controls over attachments and inline images.  

Please watch the recent MS-Flow Webinar I did with MS-Flow team focused on working with Binary Data.  That will explain how I take binary values and split them into $content and $content-type.