Data Connector Health - Push Notification Alerts

Published Dec 17 2020 12:57 AM 8,337 Views


This enhanced solution builds on the existing “Connector Health Workbook” described in this video. The Logic App leverages underlying KQL queries to provide you with an option to configure “Push notifications” to e-mail and/or a Microsoft Teams channel based on user defined anomaly scores as well as time since the last “Heartbeat” from Virtual Machines connected to the workspace. Below is a detailed description of how the rule and the logic app are put together. The solution is available for deployment from the official Azure Sentinel GitHub repo on this link .


Overview of the steps the Logic App works through



The Logic App is activated by a Recurrence trigger whose frequency of execution can be adjusted to your requirements



Since the Logic App is being deployed from an ARM template you will need to make connections to Azure Monitor, Office 365 and Teams before the Logic App can work in your environment. You can expect to see windows like the one below. Click “Add new” to create a connection for each of the three resources.




The KQL query below will be added to this step in the Logic App and will execute against your workspace. You can modify the threshold values to suit your needs

let UpperThreshold = 3.0; // Upper Anomaly threshold score

let LowerThreshold = -3.0; // Lower anomaly threshold score

let TableIgnoreList = dynamic(['SecurityAlert', 'BehaviorAnalytics', 'SecurityBaseline', 'ProtectionStatus']); // select tables you want to EXCLUDE from the results

union withsource=TableName1 *

| make-series count() on TimeGenerated from ago(14d) to now() step 1d by TableName1

| extend (anomalies, score, baseline) = series_decompose_anomalies(count_, 1.5, 7, 'linefit', 1, 'ctukey', 0.01)

| where anomalies[-1] == 1 or anomalies[-1] == -1

| extend Score = score[-1]

| where Score >= UpperThreshold or Score <= LowerThreshold

| where TableName1 !in (TableIgnoreList)

| project TableName=TableName1, ExpectedCount=round(todouble(baseline[-1]),1), ActualCount=round(todouble(count_[-1]),1), AnomalyScore = round(todouble(score[-1]),1)



Execute query against workspace to detect potential VM connectivity issues



To adjust the lookback period for the last heartbeat received from VMs in the workspace, change the “| where LastHeartbeat < ago(5h)” line in the query above


Send out the results of the query to the SOC team as a summarized HTML table




Note that while the two queries use two query outputs named “Body” they are different and care should be taken to select the correct output. Naming the Ingestion Anomaly and the Heart Beat query steps differently will help distinguish between the two “Body” variables.


Send the same message to a Microsoft Teams channel monitored by the SOC team



Below is a sample output of the push notification message




 This solution was built in close collaboration with @Javier Soriano , @Jeremy Tan and @BenjiSec 


This is not currently working in any of our Sentinel instances. Scheduled searches with union * fail to save with the error message:

Failed to save analytics rule 'Sentinel table missing logs'. Invalid data model. [Properties.Query: Scheduled alert rule query should not contain 'search' or 'union *']

Related Github issue:


You shouldn't be creating analytic rule, but only Logic App so that you can use above solution. 
You can also use Deploy to Azure template from Azure-Sentinel/Playbooks/Send-ConnectorHealthStatus at master · Azure/Azure-Sentinel (


I just done it again via template and it works with no issues on my Sentinel environment.


Thanks @BenjiSec just to add the reason why @pemontto  here's the explanation: Create custom analytics rules to detect threats with Azure Sentinel | Microsoft Docs

Senior Member

Can you expand a bit on the User Name variable used in the deployment? What kind of identity would be good practice here, and what roles or rights does the user require?


@Mikko_Koivunen  thanks for your query, the username variable is the user ID with permissions to deploy the solution within your Azure environment. In this case it has to have a minimum of "Logic App Contributor" role in the resource group where you want to deploy this logic app.  To run the two queries you need a minimum of "Reader" role for the Workspace. To make the Office 365 and Teams connections you just need a mailbox in the tenant where this messages will be sent from.

Senior Member

Is there a reason for not using Azure monitor alert rules for this?

Occasional Contributor

@Inwafula Would you be so kind to explain a part of kql query used for Tables with Data Ingestion Anomalies

Especially these below lines from the kql query,

extend (anomalies, score, baseline) = series_decompose_anomalies(count_, 1.57'linefit'1'ctukey'0.01)
where anomalies[-1] == 1 or anomalies[-1] == -1
extend Score = score[-1]

Hi @AlphaBetaGamma  thanks for raising this query. Please see below explanations. I will also make an update to the blog later on to include them in the main body for the benefit of others:


let UpperThreshold = 3 ; //+ or -3 is the suggested number and it indicates a strong anomaly though you can modify it : Outlier - Wikipedia

let LowerThreshold = -3 ; 

let TableIgnoreList = dynamic(['SecurityAlert''BehaviorAnalytics''SecurityBaseline''ProtectionStatus']); // select tables you want to EXCLUDE from the results

union withsource=TableName1 *

make-series count() on TimeGenerated from ago(14d) to now() step 1by TableName1 //create array based on ingested  data from all tables in the workspace across 14 days on a daily basis

extend (anomalies, score, baseline) = series_decompose_anomalies(count_, 1.57'linefit'1'ctukey'0.01//take the array of ingested data across the 14 days and extract anomalous points with scores based on predicted values using the linear regression concept. See for a detailed explanation of each argument. For an explanation of 'ctukey' read: Outlier - Wikipedia.

  in our case the seasonality argument (7) can be left at default i.e. -1 and the output would still be the same 

where anomalies[-1] == 1 or anomalies[-1] == -1 //the output of series_decompose_anomalies function is three things: A ternary (as opposed to binary) series containing (+1, -1, 0) marking up/down/no anomaly respectively, the Anomaly score and the predicted value or baseline. 

extend Score = score[-1//this picks up the anomaly state from the most recent run. -1 indicates a position in the array. To see exactly how it works I suggest you run parts of the query and look at the output. i.e. up to line 9. In my case one of the runs produced the below output

TableName1 anomalies score Score
AzureMetrics [0,0,0,0,0,0,0,0,0,0,0,0,0,-1] [0,0,0,0,0,0,0,0,0,0,0,0,0,-3.9998356305150646] -3.99984
Syslog [0,0,0,0,0,0,0,0,0,0,0,0,0,-1] [-0.00036792965707559,0,0,0,0,0,0,1.3549523289867058,0.09720032576946343,0,0,-0.7998683232928704,0,-4.7250625572418405] -4.72506
AzureActivity [0,0,0,0,0,-1,0,0,0,0,0,0,0,1] [0.255883471606481,0,0,0,0,-1.8238931029256342,0,-0.21675871267353206,0,0,0,0,1.3739583751967412,4.334262181116097] 4.334262
Operation [0,1,0,0,0,0,0,0,-1,0,0,0,0,1] [-0.17908658774875946,2.248291860653117,0,0,0,0,0,0,-2.6172533848101915,0,0,0,0.12298717471902801,8.68893601041275] 8.688936
UserAccessAnalytics [0,0,0,0,0,1,0,0,0,0,0,0,-1,-1] [-0.051628790842371826,0,0,0,0,5.2235777977951106,0,0,0,0,0,0.06069871355792385,-3.710993763386163,-10.209942232260403] -10.2099

Note: I filtered the columns above so as to be able to display the two score columns and anomalies in this limited space

the -1 is the position in the array counting from the extreme right. The result there will be either 1, 0 or -1

where Score >= UpperThreshold or Score <= LowerThreshold | where TableName1 !in (TableIgnoreList) //compare with strong anomaly indicator values

project TableName=TableName1, ExpectedCount=round(todouble(baseline[-1]),1), ActualCount=round(todouble(count_[-1]),1), AnomalyScore = round(todouble(score[-1]),1)


@niklasvolcz1 you can use Azure Monitor alerts. The idea of the blog is to showcase the concept of push notifications for important alerts. However, with Azure Monitor alerts the cost is higher-even though both logic apps and azure monitor alerts are both quite low in cost. Also, when it comes to presenting the output in a HTML table-it takes more work and expertise to do so with Azure Monitor alerts

Version history
Last update:
‎Nov 01 2021 04:24 PM
Updated by: