Overview
This article covers five practical techniques for writing KQL queries for Business Activity Monitoring (BAM) data queries. Each tip addresses a common challenge in query design — from required fields and status mapping to portal deep-links and log delivery guarantees.
Business value
Well-structured KQL queries produce accurate, actionable transaction data in the BAM portal. Applying these patterns from the start reduces the effort needed to debug incorrect status indicators, missing duration values, and broken correlation links.
How it works
Include the time field
Every BAM data query must include one of the following time fields, depending on the data source:
TimeStamp— Application InsightsTimeGenerated— Log Analytics
These are default fields on their respective data sources. BAM uses them to apply time filters when querying for transaction records. Omitting the time field causes queries to return no results.
Map status fields
You can map a column from your query to the transaction or stage status field in Turbo360. BAM uses this value to display a coloured status indicator in the portal. Status mapping is configured at the shape or transaction level.
Use a KQL case statement to derive a status field from your existing data. This gives you control over the mapping logic when multiple source fields contribute to the final status. The status values BAM recognises are Success, Failure, Inprogress, and Cancelled — your case statement must map to one of these exact strings.
| extend OverallStatus = case(
EndSuccess == 'True', 'Success',
EndSuccess == 'False', 'Failure',
'Inprogress')
Set start, end, and duration
BAM supports optional Start, End, and Duration fields at both the transaction and stage level. When you include these columns in your query, BAM displays them in the transaction details view.
For short-lived stages, you may choose to skip start/end tracking and focus on matching a log event to the stage and its status. At the transaction level, including start and end times is strongly recommended.
A common pattern is to capture the timestamp on the start event and the timestamp on the end event, then use the extend operator to calculate duration.
| extend Duration = EndTime - StartTime
Add links to Azure
You can include an Azure portal URL as a column in your query. BAM renders this as a clickable link on the transaction details panel, letting you jump directly to the relevant resource in the Azure portal.
Build a base URL from the portal and use strcat or replace_strings to inject the run ID dynamically.
Logic App Consumption
Construct the base portal URL for the Logic App run history and append the run ID at query time.
//Base URL for the Logic App run history — run ID will be appended dynamically
let inputLogicAppRunPortalUrl = "https://portal.azure.com/#view/Microsoft_Azure_EMA/DesignerEditorConsumption.ReactView/id/%2Fsubscriptions%2F08a281b8-3b07-4219-a517-b11230e9b34f%2FresourceGroups%2FEAI_App_EmployeeBenefitsFiles%2Fproviders%2FMicrosoft.Logic%2Fworkflows%2FEmployeeBenefits-To-BenefitsManagement-Partner/location/northeurope/showGoBackButton~/true/isReadOnly~/true/isMonitoringView~/true/runId/%2Fsubscriptions%2F08a281b8-3b07-4219-a517-b11230e9b34f%2FresourceGroups%2FEAI_App_EmployeeBenefitsFiles%2Fproviders%2FMicrosoft.Logic%2Fworkflows%2FEmployeeBenefits-To-BenefitsManagement-Partner%2Fruns%2F";
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where ResourceGroup == "EAI_APP_EMPLOYEEBENEFITSFILES"
| where resource_workflowName_s == "EmployeeBenefits-To-BenefitsManagement-Partner"
| where ResourceType == "WORKFLOWS/RUNS/ACTIONS"
| where OperationName == "Microsoft.Logic/workflows/workflowActionCompleted"
| where Resource == "HTTP_-_GET_EMPLOYEE_BENEFITS_DATASET"
| extend FileName = trackedProperties_fileName_s
| extend WorkFlowName = resource_workflowName_s
| extend WorkFlowRunID = resource_runId_s
| extend PortalUrl = strcat(inputLogicAppRunPortalUrl, resource_runId_s)
Data Factory
Use the strcat function to inject the run ID and factory parameters into the Azure Data Factory pipeline runs URL.
| extend PortalUrl = strcat("https://adf.azure.com/en/monitoring/pipelineruns/", runId_g, "?factory=%2Fsubscriptions%2F", inputSubscriptionId, "%2FresourceGroups%2F", inputResourceGroupName, "%2Fproviders%2FMicrosoft.DataFactory%2Ffactories%2F", inputDataFactory)
Refer to the Data Factory sample to see this pattern in a complete query.
API Management
To create a clickable portal link that opens Application Insights and displays the call tree for an API Management request, promote the ItemId field on the parent query for correlation. Then, on the child stage query, build the portal URL using the replace_string function.
Replace the placeholder values in the template below with your subscription ID, resource group, and Application Insights name.
let input_item_id = {itemId};
let resourceGroup = "Platform";
let subscription = "08a281b8-3b07-4219-a517-b11230e9b34f";
let appInsightsName = "kv-eai-apim-appinsights";
let portalUrlTemplate = "https://portal.azure.com/#blade/AppInsightsExtension/DetailsV2Blade/DataModel/%7B%22eventId%22:%22[RequestID]%22,%22timestamp%22:%22[startTime]%22%7D/ComponentId/%7B%22Name%22:%22[appInsightsName]%22,%22ResourceGroup%22:%22[resourceGroup]%22,%22SubscriptionId%22:%22[subscription]%22%7D";
let url_replace_subscription = replace_string(portalUrlTemplate, "[subscription]", subscription);
let url_replace_resource_group = replace_string(url_replace_subscription, "[resourceGroup]", resourceGroup);
let url_replace_app_insights_name = replace_string(url_replace_resource_group, "[appInsightsName]", appInsightsName);
requests
| where itemId == input_item_id
| extend azure_portal_url = replace_string(replace_string(url_replace_app_insights_name, "[RequestID]", itemId), "[startTime]", tostring(datetime_add('hour', -1, timestamp)))
Understand log delivery guarantees
Some Azure logging approaches do not guarantee delivery under all conditions:
- Application Insights sampling may be enabled, which means not every event is logged.
- Log Analytics and Application Insights may not guarantee delivery under high load.
If your scenario requires guaranteed log delivery, use the push model with the Turbo360 BAM API instead. If you are reusing existing diagnostic logs and can accept eventual delivery, the pull model is the appropriate choice.