I need to use some query results in an alert email notification in Azure ApplicationInsights
Ok I have an Application Insights resource where I am able to monitor the logs with this query:
exceptions
| where severityLevel >= 4
| where timestamp > ago(30m)
| project timestamp, type, assembly, method, outerMessage, severityLevel, customDimensions
| order by timestamp desc
It's simple and it returns results.
What I need is to create an Alert based on the provided query, so I created a new Alert Rule and configured like this:
Condition Signal Name: Custom Log Search Search Query: Measure: Table Rows Aggregation type: Count Aggregation Granularity: 30 minutes Alert Logic: Greather or Equal to Threshold Value: 1 Frequency of Evaluaton: 1 minute
Actions
Created an Action Group named Bg Queues Action Group -- Display Name: BgQueueAct
Notification Type: Blank
Action type: WebHook
Name: BgQueuesSendMailWebHook
Webhook: URI and paste the URI from the Logic App I created called CriticalExceptionAlertEmail
Details
No big stuff, just the proper Subscription and Resource and Severity: Critical
Here is my Logic App Design:
The problem is that the payload for Request Body JSON Schema in the "When an HTTP request is received" trigger is an Azure Alert JSON, which goes like this and has nothing of the query results, just the query itself:
{
"essentials": {
"alertId": "/subscriptions/eb9b4a96-9631-4054-8c26-c6351a5f09c0/providers/Microsoft.AlertsManagement/alerts/2b822480-3c1b-b129-99ae-f613f8c4000f",
"alertRule": "BgQueues Alert Rule",
"targetResourceType": "microsoft.insights/components",
"alertRuleID": "/subscriptions/eb9b4a96-9631-4054-8c26-c6351a5f09c0/resourceGroups/BgQueuesResourceGroup/providers/microsoft.insights/scheduledqueryrules/BgQueues Alert Rule",
"severity": "Sev0",
"signalType": "Log",
"monitorCondition": "Fired",
"targetResourceGroup": "bgqueuesresourcegroup",
"monitoringService": "Log Alerts V2",
"alertTargetIDs": [
"/subscriptions/eb9b4a96-9631-4054-8c26-c6351a5f09c0/resourcegroups/bgqueuesresourcegroup/providers/microsoft.insights/components/bgqueuesapplicationinsights"
],
"configurationItems": [
"/subscriptions/eb9b4a96-9631-4054-8c26-c6351a5f09c0/resourceGroups/BgQueuesResourceGroup/providers/microsoft.insights/components/BgQueuesApplicationInsights"
],
"originAlertId": "f9007319-2fd1-4e36-948d-b2e44ac035af",
"firedDateTime": "2025-04-04T07:11:09.5043349Z",
"description": "",
"essentialsVersion": "1.0",
"alertContextVersion": "1.0",
"investigationLink": "https://portal.azure.com/#view/Microsoft_Azure_Monitoring_Alerts/Investigation.ReactView/alertId/%2fsubscriptions%2feb9b4a96-9631-4054-8c26-c6351a5f09c0%2fresourceGroups%2fbgqueuesresourcegroup%2fproviders%2fMicrosoft.AlertsManagement%2falerts%2f2b822480-3c1b-b129-99ae-f613f8c4000f"
},
"alertContext": {
"properties": {},
"conditionType": "LogQueryCriteria",
"condition": {
"windowSize": "PT30M",
"allOf": [
{
"searchQuery": "exceptions\n| where timestamp > ago(6m)\n| where severityLevel >= 4\n| extend customDimensionsParsed = parse_json(customDimensions)\n| extend queueTypeName = customDimensionsParsed[\"queueTypeName\"]\n| project \n // timestamp, \n queueTypeName\n// type, \n// assembly, \n// method, \n// outerMessage, \n// severityLevel,\n// customDimensionsParsed\n// | order by timestamp desc\n\n",
"metricMeasureColumn": null,
"targetResourceTypes": "['microsoft.insights/components']",
"operator": "GreaterThanOrEqual",
"threshold": "1",
"timeAggregation": "Count",
"dimensions": [],
"metricValue": 1,
"failingPeriods": {
"numberOfEvaluationPeriods": 1,
"minFailingPeriodsToAlert": 1
},
"linkToSearchResultsUI": "https://portal.azure.com#@0b1f9da7-f454-4aa2-8f17-76ac71e230fb/blade/Microsoft_Azure_Monitoring_Logs/LogsBlade/source/Alerts.EmailLinks/scope/%7B%22resources%22%3A%5B%7B%22resourceId%22%3A%22%2Fsubscriptions%2Feb9b4a96-9631-4054-8c26-c6351a5f09c0%2FresourceGroups%2FBgQueuesResourceGroup%2Fproviders%2Fmicrosoft.insights%2Fcomponents%2FBgQueuesApplicationInsights%22%7D%5D%7D/q/eJxtUMFOAjEQve9XTDgtCciKiAkJnDyq8cBJY0zZvsgSuq2dWWUTPt5plqhLmDTp65v3XjvFoUSQytecHel7iwiSyoHFuEAryq0RJCKfFtPbcTHTtS7uFtfF4mZ%2BVXT1MqQxzd3wN4HxhVhJ%2B6D7nlZLmmkLB0FtqWxYvLvXyJrTtc8mMiwtKSTwvmNf5%2BealHyyfzZosG4DnoyDui7HvQ56usGbBoTodyiFMtKaTP7GHHVUz5ElgWLtKTLMcJt9250cZOtth30jiI9gNh8nbW%2F2UWIuPzF1juSjRaRN%2B%2B%2FTLbj8AQ%3D%3D/prettify/1/timespan/2025-04-02T07%3a10%3a36.0000000Z%2f2025-04-04T07%3a10%3a36.0000000Z",
"linkToFilteredSearchResultsUI": "https://portal.azure.com#@0b1f9da7-f454-4aa2-8f17-76ac71e230fb/blade/Microsoft_Azure_Monitoring_Logs/LogsBlade/source/Alerts.EmailLinks/scope/%7B%22resources%22%3A%5B%7B%22resourceId%22%3A%22%2Fsubscriptions%2Feb9b4a96-9631-4054-8c26-c6351a5f09c0%2FresourceGroups%2FBgQueuesResourceGroup%2Fproviders%2Fmicrosoft.insights%2Fcomponents%2FBgQueuesApplicationInsights%22%7D%5D%7D/q/eJxtUMFOAjEQve9XTDgtCciKiAkJnDyq8cBJY0zZvsgSuq2dWWUTPt5plqhLmDTp65v3XjvFoUSQytecHel7iwiSyoHFuEAryq0RJCKfFtPbcTHTtS7uFtfF4mZ%2BVXT1MqQxzd3wN4HxhVhJ%2B6D7nlZLmmkLB0FtqWxYvLvXyJrTtc8mMiwtKSTwvmNf5%2BealHyyfzZosG4DnoyDui7HvQ56usGbBoTodyiFMtKaTP7GHHVUz5ElgWLtKTLMcJt9250cZOtth30jiI9gNh8nbW%2F2UWIuPzF1juSjRaRN%2B%2B%2FTLbj8AQ%3D%3D/prettify/1/timespan/2025-04-02T07%3a10%3a36.0000000Z%2f2025-04-04T07%3a10%3a36.0000000Z",
"linkToSearchResultsAPI": "https://api.applicationinsights.io/v1/apps/01ee4a07-4c3f-4160-9e1f-d84dcd64bcda/query?query=exceptions%0A%7C%20where%20timestamp%20%3E%20%28datetime%282025-04-04T07%3A10%3A36.0000000Z%29%20-%206m%29%0A%7C%20where%20severityLevel%20%3E%3D%204%0A%7C%20extend%20customDimensionsParsed%20%3D%20parse_json%28customDimensions%29%0A%7C%20extend%20queueTypeName%20%3D%20customDimensionsParsed%5B%22queueTypeName%22%5D%0A%7C%20project%20%0A%20%20%20%20%2F%2F%20timestamp%2C%20%0A%20%20%20%20queueTypeName%0A%2F%2F%20type%2C%20%0A%2F%2F%20assembly%2C%20%0A%2F%2F%20method%2C%20%0A%2F%2F%20outerMessage%2C%20%0A%2F%2F%20severityLevel%2C%0A%2F%2F%20customDimensionsParsed%0A%2F%2F%20%7C%20order%20by%20timestamp%20desc×pan=2025-04-02T07%3a10%3a36.0000000Z%2f2025-04-04T07%3a10%3a36.0000000Z",
"linkToFilteredSearchResultsAPI": "https://api.applicationinsights.io/v1/apps/01ee4a07-4c3f-4160-9e1f-d84dcd64bcda/query?query=exceptions%0A%7C%20where%20timestamp%20%3E%20%28datetime%282025-04-04T07%3A10%3A36.0000000Z%29%20-%206m%29%0A%7C%20where%20severityLevel%20%3E%3D%204%0A%7C%20extend%20customDimensionsParsed%20%3D%20parse_json%28customDimensions%29%0A%7C%20extend%20queueTypeName%20%3D%20customDimensionsParsed%5B%22queueTypeName%22%5D%0A%7C%20project%20%0A%20%20%20%20%2F%2F%20timestamp%2C%20%0A%20%20%20%20queueTypeName%0A%2F%2F%20type%2C%20%0A%2F%2F%20assembly%2C%20%0A%2F%2F%20method%2C%20%0A%2F%2F%20outerMessage%2C%20%0A%2F%2F%20severityLevel%2C%0A%2F%2F%20customDimensionsParsed%0A%2F%2F%20%7C%20order%20by%20timestamp%20desc×pan=2025-04-02T07%3a10%3a36.0000000Z%2f2025-04-04T07%3a10%3a36.0000000Z",
"event": null
}
],
"windowStartTime": "2025-04-02T07:10:36Z",
"windowEndTime": "2025-04-04T07:10:36Z"
}
},
"customProperties": {}
}
So, here's the big challenge: How to parse the results what were generated by the query that triggered the Logic App trigger into the Azure Alert JSON?
I think my intentions are very clear here. So, any suggestions? Whats wrong with my approach?
Thanks to you all in advance.
I am trying to create an alert when an exception occurs and parse the KQL projected fields values into the email sent for that alert.
Solution
I have used below design which works fine:
To use the json properties, one needs to Prase it.
Input json to Logic App:
{
"properties": {
"severityLevel": "1",
"timestamp": "2025-04-03T10:30:00Z",
"type": "TestException",
"assembly": "RithAssembly",
"method": "ProcessRequest",
"outerMessage": "An error occurred while processing the request.",
"customDimensions": "{\"Key1\":\"Rithwik\"}"
}
}
body('Parse_JSON')?['properties']?['timestamp']
body('Parse_JSON')?['properties']?['outerMessage']
body('Parse_JSON')?['properties']?['severityLevel']
Output:
If you are getting correct input, then the above design will work. To use the json properties, one needs to Prase it.
Edit:
How to parse the results what were generated
One cannot and will not get the results of query from alerts. It will only send an alert, that it has happened.
For the input you have given the parse json schema will be :
{
"type": "object",
"properties": {
"essentials": {
"type": "object",
"properties": {
"alertId": {
"type": "string"
},
"alertRule": {
"type": "string"
},
"targetResourceType": {
"type": "string"
},
"alertRuleID": {
"type": "string"
},
"severity": {
"type": "string"
},
"signalType": {
"type": "string"
},
"monitorCondition": {
"type": "string"
},
"targetResourceGroup": {
"type": "string"
},
"monitoringService": {
"type": "string"
},
"alertTargetIDs": {
"type": "array",
"items": {
"type": "string"
}
},
"configurationItems": {
"type": "array",
"items": {
"type": "string"
}
},
"originAlertId": {
"type": "string"
},
"firedDateTime": {
"type": "string"
},
"description": {
"type": "string"
},
"essentialsVersion": {
"type": "string"
},
"alertContextVersion": {
"type": "string"
},
"investigationLink": {
"type": "string"
}
}
},
"alertContext": {
"type": "object",
"properties": {
"properties": {
"type": "object",
"properties": {}
},
"conditionType": {
"type": "string"
},
"condition": {
"type": "object",
"properties": {
"windowSize": {
"type": "string"
},
"allOf": {
"type": "array",
"items": {
"type": "object",
"properties": {
"searchQuery": {
"type": "string"
},
"metricMeasureColumn": {},
"targetResourceTypes": {
"type": "string"
},
"operator": {
"type": "string"
},
"threshold": {
"type": "string"
},
"timeAggregation": {
"type": "string"
},
"dimensions": {
"type": "array"
},
"metricValue": {
"type": "integer"
},
"failingPeriods": {
"type": "object",
"properties": {
"numberOfEvaluationPeriods": {
"type": "integer"
},
"minFailingPeriodsToAlert": {
"type": "integer"
}
}
},
"linkToSearchResultsUI": {
"type": "string"
},
"linkToFilteredSearchResultsUI": {
"type": "string"
},
"linkToSearchResultsAPI": {
"type": "string"
},
"linkToFilteredSearchResultsAPI": {
"type": "string"
},
"event": {}
},
"required": [
"searchQuery",
"metricMeasureColumn",
"targetResourceTypes",
"operator",
"threshold",
"timeAggregation",
"dimensions",
"metricValue",
"failingPeriods",
"linkToSearchResultsUI",
"linkToFilteredSearchResultsUI",
"linkToSearchResultsAPI",
"linkToFilteredSearchResultsAPI",
"event"
]
}
},
"windowStartTime": {
"type": "string"
},
"windowEndTime": {
"type": "string"
}
}
}
}
},
"customProperties": {
"type": "object",
"properties": {}
}
}
}
Body of Outlook will be:
body('Parse_JSON')?['essentials']?['firedDateTime']
ALERT: Critical Exception Detected 2!
Out: body('Parse_JSON')?['essentials']?['description']
body('Parse_JSON')?['essentials']?['severity']
Output:
- "Schema" property not found in Postgres Connector
- Downloading file from Azure blob storage via Memory Stream
- Reading env variable from template.yaml
- HTTP Error 500.30 - ASP.NET Core app failed to start - Azure
- Azure DevOps Pipelines: TerraformTaskV4: init with different subscription and Workflow Identity Federation
- Enabling HTTPS for a Azure blob static website
- How to search across many Azure Devops Git project and find all files which contain specific text AND which filename contains "Resource"
- Hosting SPA with Static Website option on Azure Blob Storage (clean URLs and Deep links)
- Using Managed Identity to Access Azure OpenAI Service
- Azure data factory not loading
- Transfer encrypted dataprotection keys from a windows vm to azure keyvault
- Refresh powerBI data with additional column
- Azure Application Insights AKS - How to Create 1 custom alert rule which will trigger multiple alerts, but with different names (per pod name)?
- gRPC and REST side by side in Azure App Service, Linux Container
- Connecting to a SignalR WSS stream but not getting the response i am looking for
- SignalR prevent user from opening multiple sessions in different tabs
- Application Insights -_logger.LogInformation
- CosmosDB is not allowing serverless capabalities to NoSQL, it says I must use CapacityMode
- Azure Webapp / Website Swap : HTTP Ping Error
- Microsoft Graph API - Update phoneMethods is no longer working
- Use Azure Key Vault secret in Header section of Web Activity
- Azure Storage blob getting the io.netty.channel.StacklessClosedChannelException while generating /downloading the from SasUrl
- How do I save and later load Azure AnalyzeResult object in python?
- Office 365 Exchange Online permission is not visible for registered application in azure active directory
- Azure ADF - HTTP : Table from list
- Self hosted azure devops build agent not getting tool from $PATH
- Azure devops pipeline stages template nesting
- Azure APIM is returning the incorrect HTTP response error when call is missing mandatory querystring parameter
- Azure DevOps build pipeline logid for a specific task - dynamically
- PS script to fetch the list non-compliance resource list with respect to policy from multiple subscriptions