Logic Apps Run Report Generator

Requirements

The other day I stumbled across a requirement to be able to present a comprehensive report of our Azure Logic Apps’ statuses in a simple and organised format. Our client needs greater visibility into the performance and status of these workflows which transmit pricing information as extracts, that is critical to run their business being a fast-food chain.

At Bidone, we use Log Analytics extensively to monitor the overall status of the logic apps and replay messages as required.

The requirements for the solution are:

1. Easily accessible through different methods, from your preferred HTTP request tool such as Postman, or even directly from the web browser.
2. Because the extract runs on a daily basis, the report needs to contain information only for today.
3. In a format that is easy to open and able to be organised, filtered, and sorted.

To accomplish this, I decided to create a new reporting solution, which is a Logic App connecting to Log Analytics and is secured behind the Azure API Management. The reports are returned in CSV format which can be opened in Excel as a spreadsheet for further filtering and searching.

Solution Overview

• Logic Apps: Let’s first take a look at the Logic App, by using the Azure Monitor Logs connector, we are able to run the Kusto query to obtain the report data from Log Analytics

An if statement is used to determine which Timezone to convert from based on the client’s site.

The Azure Monitor Logs action is used to run the query in Log Analytics.

The query is then converted to a CSV file using Create CSV Table action.

The connection from Logic Apps is authenticated using the System-Assigned Managed Identity, which is the preferred method as this means we won’t have to store and manage any password.

• Azure API Management: The API in Azure APIM is a simple one where it accepts the relevant query parameters or headers, and then forwards the request to the aforementioned logic app, it is secured by Subscription Key.

The Run Report

Testing the APIM endpoint using Postman, we get a CSV file returned as the response:

The generated CSV report will include the following columns:

1. TrackingId: Currently this is mapped to the extract’s AccountCode which is the unique identifier of the individual record.
2. WorkflowStatus: The status of the logic app runs, the available values are “Running”, “Completed”, “Failed” and “Cancelled”.
3. StartTime (UTC): The time of when the logic app run kicks off.
4. EndTime (UTC): The time of when the logic app run finished running.
5. Duration (Seconds): The duration of how long did the logic app run.
6. RunId (GUID): Run ID in the logic apps which you can use to access the specific run from the logic apps overview page.

The Kusto query which is used to obtain reports from Log Analytics can be found below:

AzureDiagnostics
| where Category == "WorkflowRuntime"
| where OperationName == "Microsoft.Logic/workflows/workflowRunCompleted"
| join kind= rightouter (AzureDiagnostics
    | where Category == "WorkflowRuntime"
    | where OperationName == "Microsoft.Logic/workflows/workflowRunStarted"
    | join kind= inner(AzureDiagnostics
        | where Category == "WorkflowRuntime"
        | where OperationName == "Microsoft.Logic/workflows/workflowTriggerCompleted"
        | project TriggerName = Resource, resource_runId_s)
        on resource_runId_s
    | project
        WorkflowStartStatus=status_s,
        WorkflowNameFromInnerQuery=resource_workflowName_s,
        WorkflowIdFromInnerQuery=workflowId_s,
        ResourceGroupFromInnerQuery=resource_resourceGroupName_s,
        RunIdFromInnerQuery = resource_runId_s,
        OriginRunIdFromInnerQuery = resource_originRunId_s,
        TrackingIdFromInnerQuery = iff(isnotempty(correlation_clientTrackingId_s), correlation_clientTrackingId_s, correlation_clientTrackingId_g),
        StartTimeFromInnerQuery = startTime_t,
        resource_runId_s,
        TriggerName)
    on resource_runId_s
| extend WorkflowStatus=iff(isnotempty(status_s), status_s, WorkflowStartStatus)
| extend WorkflowName=iff(isnotempty(resource_workflowName_s), resource_workflowName_s, WorkflowNameFromInnerQuery)
| extend WorkflowId=iff(isnotempty(workflowId_s), workflowId_s, WorkflowIdFromInnerQuery)
| extend ResourceGroup=iff(isnotempty(resource_resourceGroupName_s), resource_resourceGroupName_s, ResourceGroupFromInnerQuery)
| extend RunId=iff(isnotempty(resource_runId_s), resource_runId_s, RunIdFromInnerQuery)
| extend OriginRunId=iff(isnotempty(resource_originRunId_s), resource_originRunId_s, OriginRunIdFromInnerQuery)
| extend TrackingIdFromOuterQuery = iff(isnotempty(correlation_clientTrackingId_s), correlation_clientTrackingId_s, correlation_clientTrackingId_g)
| extend TrackingId=iff(isnotempty(TrackingIdFromOuterQuery), TrackingIdFromOuterQuery, TrackingIdFromInnerQuery)
| extend StartTime=iff(isnotempty(startTime_t), startTime_t, StartTimeFromInnerQuery)
| extend EndTime=endTime_t
| extend Duration=iff(isnotempty(EndTime), todouble(EndTime - StartTime) / 10000000, double(null))
| where WorkflowId contains '{sitecode}-EDI-CATALOGUE-POST-{tradingpartner}-'
| project
    TrackingId,
    WorkflowStatus,   
    StartTime,
    EndTime,   
    Duration,
    RunId
| top 10001 by EndTime desc

Conclusion

In conclusion, we are happy with how the Logic App and Log Analytics integration turned out and how seamlessly the connector works. Where to go from here is I would like to make this solution a base for different logic apps for different business entities, and we’ll tweak the report file and query as we see fit.