Getting alerts when an email isn't received

[elliotmunro]

There are plenty of times that you'd like to be notified when a specific email arrives in an inbox, but what about those times when an email doesn't come through? In this post we're monitoring status emails sent each day by our client's backup programs. If a backup program doesn't send another email within 30 hours, an alert is sent to a backup technician who can acknowledge it using Email Options in Microsoft Flow.

This solution consists of the following three Flows: 

• Flow 1: Update a SharePoint list when an email arrives with the name of the company sending the backup notification
• Flow 2: Run a flow every hour, checking the SharePoint list for overdue backup status emails. If an email hasn't arrived for a particular company for over 30 hours, trigger Flow 3
• Flow 3: Alert a backup technician and get their acknowledgement.

Note: This solution fits the structure of the backup status emails sent by our backup tools. You may need to modify this where appropriate for your own requirements. 

Scenario

Each day, some of our backup tools are supposed to send an email with the format 'ClientName - BackupStatus'. If the backup status is 'Failed', the tool will log a support ticket, however if the email isn't received at all, nothing happens. This solution is designed to address this issue.

Set up a SharePoint list to keep track of your emails

Before we create our Flows, we need to set up a SharePoint list to store info about the emails. 

1. Log onto your SharePoint site and visit the Site Contents page
2. Use the + icon on the top left to create a new SharePoint list called Backup Notifications
3. Open the list and add a column of type Yes/No called Overdue
4. Add another column of type Yes/No called Addressed
5. If you like, you can edit the All Items view to include the Date Modified field.

Building Flow 1: Backup Notification Monitoring

We're creating a new Microsoft Flow that triggers whenever a new email arrives a specific folder. In our case, we're creating the flow using the account that receives the notifications. Whenever an email arrives in the backups inbox, the flow will trigger. You could also set it to trigger whenever an email arrives in a subfolder or inbox of a Shared Mailbox.

Sign into https://flow.microsoft.com using an account that has access to the mailbox that receives the notifictions. 

Click My Flows

Click Create from Blank

Name your flow Backup Notification Monitoring

Search for Outlook in the input field, and choose Office 365 Outlook - When a new email arrives

Extract the company name from the subject

In our case, our subject name varies depending on the status of the backup, however the company name at the start of the subject is always the same. We want to extract the company name from each email subject before we update the SharePoint list. You can skip this section if your subject never changes, and just use the Subject value wherever this post mentions CompanyName.

Add a Data Operations - Compose action and rename it to Array. We want to split the subject up by the space character to extract the company name from the subject. To do this, enter the following into your Compose action, including the quotes. Note that the quotes will disappear once you save the flow.

"@split(triggerBody()?['Subject'],' ')"

Create another Data Operations - Compose action and rename it to CompanyName. In this action, we'll take the first value from the array we created earlier, which for us is the company name. Copy the following into your Compose action, including the quotes.

"@first(outputs('Array'))"

Before we add the company to the Backup Notifications list, we want to check whether it already exists. To do this, we'll add a SharePoint - Get items action. Enter your site address, and select your Backup Notifications list from the dropdown.

Click Show advanced options, and in the Filter Query field enter Title eq '<Output>'. Replace <Output> with the dynamic value from the CompanyName step.

Next we want to add a condition to check whether the SharePoint - Get items action returned any items. Create a Condition step, then click Edit in advanced mode.

Replace the existing value with the following.

"@empty(body('Get_items')?['value'])"

 

If the SharePoint - Get Items action returns an empty array, we'll create a new SharePoint item, if it's not, we'll update the existing item.

In the Yes branch of the Condition step, add a SharePoint - Create item action. Select your SharePoint site and Backup Notifications list. In the Title field, enter the value from the CompanyName step. Change both Overdue and Addressed to No.

In the No branch, add a SharePoint - Update Item step. Choose your site name and list name, then add in the ID value from the earlier SharePoint - Get items step. This will automatically create an Apply to each action, since the value is technically an array.

Add in the Title from the same SharePoint - Get items step, and set both Overdue and Addressed to No.

Now we're done with the Backup Notification Monitoring flow. To summarise, this flow checks an inbox for new emails, retrieves the first word from the email subject (CompanyName), checks whether it already exists in a SharePoint list, and creates or updates a SharePoint item.

Building Flow 2: Check Missing Backup Notifications

Create a new blank flow called Check Missing Backup Notifications and set it to trigger by a Recurrence step. I've set this one to run every hour.

Add a new SharePoint - Get items action and select your SharePoint site and Backup Notifications list.

Create a new Data Operations - Compose action and rename it to DateModified. We'll use this action to retrieve the Date Modified value of each of the items within the SharePoint list. If we drag the dynamic value Modified from the Sharepoint - Get items values, an Apply to each action will be created for us.

Create a new Data Operations - Compose action and rename it to ConvertToDate. We'll use this to convert the Modified value to a date value that we can work with within Microsoft Flow. Enter the following value in the Inputs field:

"@formatDateTime(outputs('DateModified'), 'o')"

Create a new Data Operations - Compose action and rename it to 30HoursLater. We'll use this value to add 30 hours to the current Modified value. This is the value that we'll compare the current time against. If the current time is greater than 30 hours after the time that this item was modified, the backup notification would be overdue. In the 30HoursLater action, enter the following value:

"@addhours(outputs('ConvertToDate'),30)"

 Create a new Data Operations - Compose action and rename it to CurrentTime. Enter the following value:

"@utcnow()"

Create a new Condition step. In the first field, enter the output of the CurrentTime action. Choose 'is greater than' from the dropdown, then enter the Output of the 30HoursLater value. This will check whether the item hasn't been updated in the last 30 hours.

 Alternatively, you can click Edit in advanced mode, then enter:

"@greater(outputs('CurrentTime'), outputs('30HoursLater'))"

If the item is overdue, we want to notify someone, though we don't want to notify them every hour that the flow runs. To prevent this, we'll add another Condition within the Yes branch and rename it to AlreadySent. In this condition we'll check make sure the Overdue value for the current item is not set to Yes. If it's not, we'll set it to Yes, then notify someone.

In the new condition, click Edit in advanced mode and enter the following:

"@not(equals(item()?['Overdue'], bool('true')))"

In the Yes branch, add a SharePoint - Update item action. Add in the ID and Title from the previous Get items action, then set Overdue to Yes and Addressed to No.

We want to make sure that overdue email notifications are acknowledged, and to do this we'd typically use an approval or 'Send email with options' action.  Unfortunately, we can't run approval steps within a foreach loop.

We could end the flow after updating the Overdue status, then trigger the next flow whenever an item is updated - having it check for the Overdue Status. However, we want to cut down on unnecessary flow runs. In this case, we're going to trigger the next flow using a HTTP call so that it only runs when required. The next flow will start with the HTTP Request trigger containing the ID and Title of the relevant SharePoint item.

Create a HTTP - HTTP Action and add in the following Body, replacing the dynamic content values

{ "ID" : <ID from dynamic content>, "Title" : <Title from dynamic content> }

Building Flow 3: Overdue Backup Notifications

Keep the Check Missing Backup Notifications flow open, then open a new Tab, navigate to https://flow.microsoft.com and create a new Flow called Overdue Backup Notifications.

Add in HTTP Request as a trigger:

Click Use payload to generate a sample schema and enter the following as a sample payload:

{ "ID" : 1, "Title" : "Title" }

Click Done and it will generate the following Request Body JSON Schema:

Enter another sample action so that we can save the flow and retrieve the HTTP Post URL from our HTTP Request trigger. In this case I'm going to enter an email action, though it doesn't really matter since we're going to delete it anyway.

Save the flow and copy the HTTP Request URL.

 

Return to your 'Check Missing Backup Notifications' flow and enter the URL into the URI field of your HTTP action. Then save your flow.

 Switch back to your new 'Overdue Backup Notifications' flow and delete the unnecessary sample step we created earlier.

I'm not too sure why this is required, but in order to use the ID we sent to this flow in a Sharepoint - Update item action later, I needed to add it to a Data Operations - Compose action first.

Create a new Data Operations - Compose action and rename it to SharePointID. Drag in the ID value from the HTTP Request trigger.

Next, add in a new action of type Office 365 Outlook - Send email with Options. In the email field, enter an address for the person or group who should be receiving the notification. Under User Options, add in an option so they can confirm they're addressing the issue.

Even though there's only one option, we'll still create a condition. We want to make sure we're only marking the backup notifications as Addressed when the user has acknowledged the email.

Create a Condition step, add in the SelectedOption value in the first field and choose 'is equal to' from the dropdown. In the third field, enter the value you entered in the User Options field from the Send email with options step.

In the Yes branch, add in a SharePoint - Update item action and select your site and Backup Notifications list. Enter the output of the SharePointID action into the Id field. Add in the Title from the HTTP Request action. Update Overdue and Addressed to Yes.

 
Now we're done! The SharePoint list will grow and update on it's own as backup notification emails arrive. If more than 30 hours elapses between emails, a notification email is sent to a technician with the following content:

When the technician clicks 'I'm on it', the SharePoint item is updated to reflect that the item is being addressed.

The SharePoint list will also give you an overview of which items are currently overdue, and whether they've been addressed.

Elliot Munro is an Office 365 MCSA and Partner at GCITS - See the GCITS knowledge base for scripts and articles on administering Office 365 for managed service providers.