Skip to content

Unlock Azure Logic Apps Insights: Must-Know KQL Queries

logic apps kql log analytics

Azure Logic Apps monitoring

In today’s digital landscape, applications are becoming more integrated and critical. Azure Logic Apps monitoring plays an important role in these integrations, making effective monitoring essential to ensure reliable performance and meet business requirements. Azure diagnostics and logging involve keeping an eye on both technical and functional aspects to ensure Logic Apps perform seamlessly.

This blog post looks into how Azure Monitor and Log Analytics can be used in Logic Apps Standard to provide comprehensive monitoring and data analysis. We will explore how to use KQL with Azure Logic Apps standard and analyse Logic Apps data with KQL to enhance your monitoring strategy.

  1. Technical Monitoring
  2. Functional Monitoring
  3. Getting Started with KQL for Azure Logic Apps
  4. Create a clickable link to view run history
    1. Essential KQL Queries for Monitoring Azure Logic Apps
      1. Identify Failed Azure Logic App Workflows
      2. Track Azure Logic App Workflows by Specific Property
      3. Analyzing Logic App Workflow Execution Totals
      4. Analyse Total Executions per Workflow Over the Last 7 Days
      5. Execution Time Analysis: 95th Percentile
      6. Identifying the Slowest 5% of Executions
      7. Detailed Analysis of Slowest 1% Executions
    2. Log Analytics Sampling
    3. References

    Technical Monitoring

    Technical monitoring ensures the underlying infrastructure and components of Logic Apps are running smoothly. It helps identify potential issues before they impact the business. Here are some key focus areas:

    • Unexpected Errors: Detect and troubleshoot errors in the Logic Apps workflow.
    • Service Level Agreements (SLA) Performance: Monitor SLAs to ensure services meet performance benchmarks.
    • Execution Latency: Identify and resolve slow execution issues.
    • Failure Rates & Error Handling: Analyse failure rates and implement robust error handling mechanisms.

    Functional Monitoring

    Functional monitoring ensures that the business logic executed by Logic Apps aligns with expected outcomes. It focuses on verifying business processes and data accuracy. Key areas include:

    • Business Key Analysis: Monitor processes using business keys like customer numbers or order IDs
    • Data Validation & Accuracy: Verify that the data processed by Logic Apps is accurate and meets business rules.

    Getting Started with KQL for Azure Logic Apps

    Requirements: A logic app (standard) & a Log Analytics workspace

    We’ll be using the workflow runtime logs, follow the below steps in the screenshots to connect your workflow with log analytics:

    logic app diagnostic settings
    logic app diagnostic settings runtime logs

    Using logs from the LogicAppWorkflowRuntime table in Log Analytics, you can create clickable links to directly access the run history of a Logic App workflow. This involves constructing a URL using specific keys and values from your logs, based on a KQL query.

    The URL structure you need to build is as follows:

    https://portal.azure.com/#view/Microsoft_Azure_EMA/DesignerEditor.ReactView/id/subscriptions/<SubscriptionID>/resourceGroups/<ResourceGroup>/providers/Microsoft.Web/sites/<LogicAppName>/workflows/<WorkflowName>/location/<Location>/isReadOnly~/true/isMonitoringView~/true/runId/<RunId>

    • <SubscriptionID>: Your Azure subscription ID
    • <ResourceGroup>: The resource group containing your Logic App
    • <LogicAppName>: The name of your Logic App
    • <WorkflowName>: The name of the workflow you want to view
    • <Location>: The region where your Logic App is hosted (e.g., “West-Europe”)
    • <RunId>: The unique identifier for the specific run

    Here’s an example KQL query to construct these URLs. With this, you can directly open the run history of this particular execution from log analytics or a workbook:

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| project TimeGenerated, WorkflowName, RunId, 
    url = strcat(
'https://portal.azure.com/#view/Microsoft_Azure_EMA/DesignerEditor.ReactView/id/%2Fsubscriptions%2F',
        tostring(split(_ResourceId, "/")[2]),
        '%2FresourceGroups%2F',
        tostring(split(_ResourceId, "/")[4]),
        '%2Fproviders%2FMicrosoft.Web%2Fsites%2F',
        tostring(split(_ResourceId, '/')[8]),
        '%2Fworkflows%2F',
        WorkflowName,
        '/location/West%20Europe/isReadOnly~/true/isMonitoringView~/true/runId/',
        RunId
    )
| order by TimeGenerated asc

    Remark: don’t forget to change the location from “West-Europe” to your specific location in the above query.

    Important Notes

    • Logs in the LogicAppWorkflowRuntime table may include triggers without executions. Run history links are only available for executed workflows.
    • Run history is only viewable for stateful executions.

    Making URLs Clickable in Workbooks

    To make URLs clickable within an Azure Workbook:

    1. Edit Column Settings: Navigate to the column settings where your URL is located.
    2. Select Data Type: Choose URL as the data type for that column
    kql column settings url clickable

    Essential KQL Queries for Monitoring Azure Logic Apps

    Monitoring Azure Logic Apps is crucial for ensuring optimal performance and reliability in your workflows. In this section, we will explore essential KQL queries to help you gain valuable insights into your Azure Logic Apps’ health and performance metrics. From identifying errors to tracking execution times, these must-know KQL queries empower you to unlock the full potential of your Azure Logic Apps with enhanced visibility and control.

    Identify Failed Azure Logic App Workflows

    Description: This KQL query retrieves all Azure Logic App workflows that have failed. It focuses on completed runs with errors to give you a quick overview of workflow failures.

    Insights: Use this query for a general health check of your Logic Apps. It helps you identify failed runs that may require manual investigation and troubleshooting, ensuring your workflows run smoothly and efficiently.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted" and Status == "Failed"
| project TimeGenerated, WorkflowName, RunId, parse_json(Error).code,parse_json(Error).message
| order by TimeGenerated
    logic app kql all failed runs

    Track Azure Logic App Workflows by Specific Property

    Description: This KQL query lists all completed Azure Logic App workflows that contain a specific tracked property, such as an OrderId. This query works across different Logic Apps and workflows, making it easier to identify and monitor specific processes.

    Insights: Use this query to verify workflow executions and trace back executions using business keys like OrderId. This helps in understanding the flow and status of each workflow.

    LogicAppWorkflowRuntime
| project TimeGenerated, WorkflowName, RunId, OrderId = parse_json(TrackedProperties).OrderId
| where OrderId <> ""
| order by TimeGenerated asc
    logic app kql tracked properties

    To configure custom-tracked properties, here is a sample:

    logic app custom tracking properties correlation

    Tracked properties can reference input from the trigger or the current shape. For example:

    KeyValue
    OrderId@{triggerOutputs()?[‘body’]?[‘OrderId’]}
    InvoiceNumber@{body(‘HTTP_Create_Order_In_ERP’)?[‘InvoiceNumber’]}

    Analyzing Logic App Workflow Execution Totals

    Description: This KQL query helps you visualize the total executions for all workflows, presenting the data in an easy-to-understand pie chart.

    Insights: By using this query, you can quickly identify which workflows are executed the most, allowing you to optimize resources and improve efficiency.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| summarize WorkflowNameCount=count() by WorkflowName
| sort by WorkflowNameCount
| render piechart with(title="Total Executions of Completed Logic App Workflows")
    logic ap kql total executions per workflow

    Analyse Total Executions per Workflow Over the Last 7 Days

    Description: This KQL query provides a time chart visualization of completed Azure Logic App executions for all workflows over the past week. By analyzing these metrics, you can identify patterns and understand peak load times for your system or specific workflows.

    Insights: Use this data to pinpoint peak usage periods and optimize performance. This information is important for identifying bottlenecks & planning scaling strategies for your Azure Logic Apps.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| summarize Count=count() by WorkflowName, bin(TimeGenerated, 1h)
| where TimeGenerated >= ago(7d)
| render timechart with(title="Total Executions per Workflow Over the Last 7 Days")
    logic app kql timechart of executions per workflow

    Execution Time Analysis: 95th Percentile

    Description: The 95th percentile execution time shows the upper bound of workflow completion times. This metric indicates that 95% of workflows are finished within this time or less.

    Insights: By analyzing the 95th percentile, you can identify workflows that take longer than expected. This helps pinpoint areas for performance improvement in your Azure Logic Apps.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| project WorkflowName, StartTime, EndTime, Duration = EndTime - StartTime
| join kind=inner (
    LogicAppWorkflowRuntime
    | where OperationName == "WorkflowRunCompleted"
    | project WorkflowName, Duration = EndTime - StartTime
    | summarize P95_Duration = percentiles(Duration, 95) by WorkflowName
) on WorkflowName
| where Duration > P95_Duration
| project WorkflowName, StartTime, EndTime, Duration, P95_ExpectedExecutionTime = P95_Duration
| summarize by WorkflowName, P95_ExpectedExecutionTime
| order by WorkflowName, P95_ExpectedExecutionTime desc
    logic app kql 95 percentile performance

    Identifying the Slowest 5% of Executions

    Description: This KQL query identifies the slowest 5% of executions for each workflow. It helps to understand which workflows are underperforming.

    Insights: Analyzing the slowest 5% of workflow executions provides insights into performance bottlenecks. This helps you address inefficiencies in your Logic Apps.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| project WorkflowName, Duration = EndTime - StartTime, _ResourceId, RunId
| join kind=inner (
    LogicAppWorkflowRuntime
    | where OperationName == "WorkflowRunCompleted"
    | project WorkflowName, Duration = EndTime - StartTime
    | summarize P5_Duration = percentiles(Duration, 95), P95_Duration = percentiles(Duration, 5) by WorkflowName
) on WorkflowName
| where Duration > P5_Duration
| project WorkflowName, RunId, Duration, P95_ExpectedExecutionTime = P95_Duration
| summarize Count=count() by WorkflowName
| render piechart with(title="Slowest 5% workflow executions")
    logic app kql slowest workflows in a piechart

    Detailed Analysis of Slowest 1% Executions

    Description: The query provides a list of execution IDs for the slowest 1% of workflows, offering deeper insights into performance issues.

    Insights: By examining execution IDs for the slowest workflows, you can conduct a detailed analysis to uncover specific causes of delays and optimize Logic Apps performance.

    LogicAppWorkflowRuntime
| where OperationName == "WorkflowRunCompleted"
| project WorkflowName, Duration = EndTime - StartTime, _ResourceId, RunId, TimeGenerated
| join kind=inner (
    LogicAppWorkflowRuntime
    | where OperationName == "WorkflowRunCompleted"
    | project WorkflowName, Duration = EndTime - StartTime
    | summarize P1_Duration = percentiles(Duration, 99), P95_Duration = percentiles(Duration, 95) by WorkflowName
) on WorkflowName
| where Duration > P1_Duration
| project TimeGenerated, WorkflowName, RunId, Duration, P95_ExpectedExecutionTime = P95_Duration
| order by TimeGenerated
    logic app kql slowest 1% workflow executions

    Log Analytics Sampling

    Log Analytics is not intended to be the main tool for functional reporting because it relies on sampling. Sampling is a technique that processes and stores only a subset of the complete dataset, reducing the overall data volume.

    While this approach improves performance and lowers costs, it can result in incomplete data for reporting purposes, meaning it cannot ensure 100% data retention. An application under high load will typically start to sample, which can lead to data loss in log analytics.

    References