Call Failure Notification

This scenario sends a message to teams users who have made a call which has failed in the last week.

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetCallFailureCount
IsSentOnce	False

SQL Query

You can change the date range of the Where clause in the Segments CTE to anything up to the retention period of the raw call data. Making sure to then update the card template to reflect the date range.

CREATE PROCEDURE [automation].[SpGetCallFailureCount]
AS
BEGIN

WITH Segments
AS
(
    SELECT
        C.[CallId]
        ,CASE WHEN MD.[StreamDirection] = 0 THEN S.[_HashCallerId] ELSE S.[_HashCalleeId] END AS [HashUserId]
        ,CASE WHEN MD.[StreamDirection] = 0 THEN S.[CallerUserType] ELSE S.[CalleeUserType] END AS [UserType]
        ,CASE WHEN FI.[CallId] IS NULL THEN 0 ELSE 1 END AS [Failed]
   FROM 
        [dbo].[Calls] C
        JOIN [dbo].[Segments] S ON S.[CallId] = C.[CallId]
        JOIN [dbo].[MediaDevices] MD ON MD.[CallId] = C.[CallId] AND MD.[SegmentId] = S.[SegmentId]
        LEFT JOIN [dbo].[FailureInfo] FI ON FI.[CallId] = C.[CallId] AND FI.[SegmentId] = S.[SegmentId]
        WHERE
            CONVERT(DATE, C.[StartDateTime]) >= CONVERT(DATE, DATEADD(DAY, -7, GETUTCDATE()))
            AND C.[Type] = 1
),
Calls
AS
(
    SELECT	
        S.[CallId]
        ,S.[HashUserId]
        ,CASE WHEN SUM(S.[Failed]) > 0 THEN 0 ELSE 1 END AS [Sucessful]
        ,CASE WHEN SUM(S.[Failed]) > 0 THEN 1 ELSE 0 END AS [Failed]
    FROM
        Segments S
        WHERE
            S.[UserType] = 1
	GROUP BY
        S.[CallId]
        ,S.[HashUserId]
)

SELECT	
    U.[Id] AS [UserId]
    ,U.[Mail] AS [UserMailAddress]
    ,COUNT(C.[CallId]) AS [CallCount]
    ,SUM(C.[Sucessful]) AS [SucessfulCount]
    ,ROUND(SUM(C.[Sucessful]) / CONVERT(FLOAT, COUNT(C.[CallId])) * 100, 2) AS [SuccessPercentage]
    ,SUM(C.[Failed]) AS [FailedCount]
    ,ROUND(SUM(C.[Failed]) / CONVERT(FLOAT, COUNT(C.[CallId])) * 100, 2) AS [FailedPercentage]	
FROM 
    Calls C
JOIN [dbo].[Users] U ON U.[_HashId] = C.[HashUserId]
WHERE
    U.[_Deleted] = 0
    AND U.[_Anonymised] = 0
GROUP BY
    U.[Id]
    ,U.[Mail]
HAVING SUM(C.[Failed]) > 0

END
GO

Example Data Model

{
  "RowsWithAdditionalProperties": [
    {
      "Value": {
        "CallCount": 16,
        "SucessfulCount": 14,
        "SuccessPercentage": 87.5,
        "FailedCount": 2,
        "FailedPercentage": 12.5
      }
    }
  ]
}

Example Card Template

Basic format to be customised

{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "version": "1.0",
    "type": "AdaptiveCard",
    "originator": "OriginatorIdPlaceholder",
    "body": [
        {
            "type": "Container",
            "backgroundImage": {
                "url": "https://cdn.modalitysystems.com/TeamworkAnalytics/Automation/Backgrounds/header_lightblue.png"
            },
            "items": [
                {
                    "type": "ColumnSet",
                    "columns": [
                        {
                            "type": "Column",
                            "items": [
                                {
                                    "type": "Image",
                                    "url": "https://cdn.modalitysystems.com/Icons/ic_fluent_call_filled.png",
                                    "size": "Small"
                                }
                            ],
                            "width": 30
                        },
                        {
                            "type": "Column",
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Last Week's Activity",
                                    "horizontalAlignment": "Right",
                                    "wrap": true,
                                    "color": "Dark",
                                    "size": "Small"
                                },
                                {
                                    "type": "TextBlock",
                                    "text": "IMPROVE YOUR CALLS RELIABILITY",
                                    "horizontalAlignment": "Right",
                                    "size": "Large",
                                    "color": "Attention",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "spacing": "None"
                                }
                            ],
                            "width": 70,
                            "horizontalAlignment": "Right"
                        }
                    ]
                }
            ]
        },
        {
            "type": "TextBlock",
            "text": "Your association to call failures this week is ${RowsWithAdditionalProperties[0].Value.FailedPercentage}%.",
            "wrap": true
        },
        {
            "type": "Image",
            "url": "https://quickchart.io/chart?c={type:'pie',data:{datasets:[{data: [${RowsWithAdditionalProperties[0].Value.SuccessPercentage}, ${RowsWithAdditionalProperties[0].Value.FailedPercentage}],backgroundColor:['rgb(1,250,168)','rgb(246,115,157)']},],labels:['Successful','Failed'],},}",
            "altText": "Number of successful/failed calls",
            "horizontalAlignment": "Center",
            "separator": true,
            "spacing": "Medium"
        },
        {
            "type": "TextBlock",
            "text": "To limit any potential call failures and improve the calls & meetings experience, typical general recommendations can include:",
            "wrap": true
        },
        {
            "type": "TextBlock",
            "text": "Connection",
            "wrap": true,
            "size": "Large",
            "color": "Accent"
        },
        {
            "type": "TextBlock",
            "text": "Check your connection status and connection type (WIRED or WIFI), ensure consistent & reliable. \n\n  *Suggestion*: Simply try changing to another connection type to see if any differences e.g. Wired and validating any connection settings.",
            "wrap": true,
            "separator": true
        },
        {
            "type": "TextBlock",
            "text": "Network",
            "wrap": true,
            "size": "Large",
            "color": "Accent"
        },
        {
            "type": "TextBlock",
            "text": "Ensure a stable network and the coverage is adequate if using WIFI. \n\n  *Suggestion*: Review if you have shared bandwidth or contention with other devices or users and check your service provider status for any issues. If remote working then a simple check of internet speed and latency by using [Cloudflare](https://speed.cloudflare.com/).",
            "wrap": true,
            "separator": true
        },
        {
            "type": "TextBlock",
            "text": "Equipment",
            "wrap": true,
            "size": "Large",
            "color": "Accent"
        },
        {
            "type": "TextBlock",
            "text": "Assess your equipment, ensure up to date drivers and software. \n\n  *Suggestion*: Review installation, hardware and any legacy equipment or components.",
            "wrap": true,
            "separator": true
        },
        {
            "type": "TextBlock",
            "text": "User",
            "wrap": true,
            "size": "Large",
            "color": "Accent"
        },
        {
            "type": "TextBlock",
            "text": "Produce a better experience for all participants, for example a user who puts their laptop into sleep mode (by closing the lid) without exiting the meeting will be classified as a failed call. \n\n  *Suggestion*: Pre-check your join of the meeting and your Teams client settings, under settings > devices > \"make a test call\" and ensure its an optimal experience.",
            "wrap": true,
            "separator": true
        }
    ]
}