Configure Microsoft Power Automate On Hold Notifications

This example describes how you can use Microsoft Power Automate to automatically send notifications whenever a set on hold time limit has been reached.

Preview:

Prerequisites

Before creating the Microsoft Power Automate solution, run the following script on the database:

Copy

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[onholdtimeFlow] @Holdtime INT
AS
SELECT correlationid, 
message1 AS agent, 
cd.time, 
ISNULL(cd2.time, ss.endtime) AS endtime, 
DATEDIFF(s, cd.time, GETDATE()) AS duration
FROM UCC_CallDetail AS cd WITH(NOLOCK)
OUTER APPLY
(
SELECT TOP (1) time
FROM UCC_CallDetail AS cd2 WITH(NOLOCK)
WHERE cd.correlationid = cd2.correlationid
AND type = 'StopOnHoldMusic'
AND cd2.time > cd.time
ORDER BY time
) AS cd2
OUTER APPLY
(
SELECT endtime
FROM UCC_SessionSummary AS ss WITH(NOLOCK)
WHERE cd.correlationid = ss.correlationid
) AS ss
WHERE type = 'StartOnHoldMusic'
AND ISNULL(cd2.time, ss.endtime) IS NULL
AND CONVERT(DATE, cd.time) = CONVERT(DATE, GETDATE())
AND DATEDIFF(s, cd.time, GETDATE()) > @Holdtime;
GO

Configuration

From your Office Portal, go to your Power Automate App:
Click "My Flows":
Click "New" and "Create from Blank":
Type "Recurrence" and provide the recurrence rate in seconds.

Note: Microsoft Flow limits the recurrence rate to a minimum of 60 seconds
Add an Action and type Initialize Variable. This variable will be used as an Array off all the calls on Hold. Name the variable, change its type to "Array" and leave the value field empty
Add an Action and type Initialize Variable. This variable will be used as KPI value, the amount of seconds you want to receive notifications for. Name the variable, change its type to "Integer" and fill the value slot with your KPI minimum (in seconds)
Add an Action and type Initialize Variable. This variable will be used as the text for the notification. Name the variable, change its type to "String" and leave the value field empty
Add an Action and type Execute stored procedure. This action will run the stored procedure on your SQL database; if you have installed this procedure, please navigate to the top of this page. If you started using Microsoft Power Automate for the first time, or you have never connected to your SQL environment, provide your connection details. If you are using a on-premises database, please use the Microsoft Gateway.
Select your stored procedure and under the "Hold Time" parameter select the variable from step 6
Add an Action and select "Control" and then "Condition". In the left slot under the Expression tab, enter:
Copy
Expression
```
"not(empty(body('Execute_stored_procedure')?['ResultSets']))"
```
As operator select "Is not equal to" and in the right slot, under the Expression tab, enter: "true"
The following steps should be filled under the "If No" side of the control action. The "If Yes" side will be left empty

Add an Action and select "Apply to Each". As Output from the previous steps enter under the expressions tab:
Copy
Expression
```
body('Execute_stored_procedure')?['ResultSets']?['Table1'] 
```
Add an Action within the Apply to Each action; type "Compose". Under the expressions tab enter:
Copy
Expression
```
concat(item()?['agent'], ' on hold for ',item()?['duration'], ' seconds')
```
Add an Action within the Apply to Each action; type "Compose". Under the "Dynamic Content " tab, select Output and the variable you created at "Step 7". Seperate them with a line break.
Add an Action within the Apply to Each action; type "Set Variable ". Select the variable from the previous step (created at step 7) and under value select the output from the previous step
Add an Action within the Apply to Each action; type "Append to array variable". Select the variable from step 5 and under value select "Current Item"
Add an Action outside the Apply to Each action; type "Compose". As value, select the variable you created at step 7
Add an Action and type Notification or Email and select the type of notification you want to receive. For this guide we choose an email from outlook

For the topic enter a value such as "Warning you currently have @{length(variables('Variable of step 5'))} agent(s) with high hold times!" If the expression section does not copy correctly, remove it and copy the expression in the field under the Expression tab. This will place the value at the selected spot in the string.

In the body select the variable you created at step 7 and filled in the for each section. Fill the body with more information for the receiver if necessary. If you are using an Outlook email action in MS Power Automate, you are able to configure a sender. If you choose not to configure a sender, please note that the notification will be sent under your name.