Logic App Consumption + APIM

In this sample we have an integration solution which uses Azure AI to process documents and leverages Azure Integration Services to process the documents.

You can see below the data about the invoices which are processed by the AI in the BAM data queries view which is easy for the users to see.

If I open one of the transactions I can see the below view showing the steps that the process has been through.

In order to get this outcome I am combining log data from Log Analytics and Application Insights into a business friendly view which helps the L2 support users and business users see the transactions being processed.

Below are the KQL queries that I am using to achieve this.

KQL Queries

Parent Query

This query will link the workflow start event and workflow end event so we know when it started and the outcome, but we will also link to an action in the workflow so that we can get the tracked properties from the action that logs them so we can show in the tracking grid.

let resourceGroup = "RND_T360_AI_DOCUMENTINTELLIGENCE";
let subscriptionId = "TBC";
let workflowName = "t360-poc-ai-invoice-process-one-invoice";
let actionWithPropertiesName = "PARSE_JSON_-_LOG_FINISH";
let runStartedEvent = AzureDiagnostics
    | where SubscriptionId == subscriptionId
    | where ResourceGroup == resourceGroup
    | where resource_workflowName_s == workflowName
    | where OperationName == "Microsoft.Logic/workflows/workflowRunStarted"
    | extend WorkFlowName = resource_workflowName_s
    | extend WorkFlowRunID = resource_runId_s
    | extend Start = startTime_t
    | order by TimeGenerated desc
    | project TimeGenerated,  WorkFlowName, WorkFlowRunID, Start;  
let actionWithPropertiesEvent = AzureDiagnostics
    | where SubscriptionId == subscriptionId
    | where ResourceGroup == resourceGroup
    | where  resource_workflowName_s == workflowName
    | where  OperationName == "Microsoft.Logic/workflows/workflowActionCompleted"
    | where  Resource == actionWithPropertiesName
    | extend WorkFlowName = resource_workflowName_s
    | extend WorkFlowRunID = resource_runId_s
    | extend InvoiceId = trackedProperties_LogProperties_InvoiceId_s
    | extend Vendor = trackedProperties_LogProperties_VendorName_s
    | extend InvoiceDate = trackedProperties_LogProperties_InvoiceDate_s
    | extend InvoiceTotal = trackedProperties_LogProperties_InvoiceTotal_s    
    | order by TimeGenerated desc
    | project TimeGenerated,  WorkFlowName, WorkFlowRunID, InvoiceId, Vendor, InvoiceDate, InvoiceTotal;      
let workflowCompetedEvents = AzureDiagnostics
    | where SubscriptionId == subscriptionId
    | where ResourceGroup == resourceGroup
    | where resource_workflowName_s == workflowName
    | where OperationName == "Microsoft.Logic/workflows/workflowRunCompleted"
    | extend EndResult = coalesce(status_s, "In Progress")
    | extend End = endTime_t
    | extend WorkFlowName = resource_workflowName_s
    | extend WorkFlowRunID = resource_runId_s
    | order by TimeGenerated desc
    | project WorkFlowRunID, EndResult, End;
runStartedEvent 
    | join kind=leftouter workflowCompetedEvents on WorkFlowRunID
    | join kind=leftouter actionWithPropertiesEvent on WorkFlowRunID
| extend Duration = End - Start
| extend Result = coalesce(EndResult, "In Progress")
| project TimeGenerated, WorkFlowName, WorkFlowRunID,Result, Start, End, Duration, InvoiceId, Vendor, InvoiceDate, InvoiceTotal
| order by TimeGenerated desc

Logic App Actions

This query is used against the log analytics workspace used by the Logic App Consumption workflows.

In this case the action shapes for all of the actions I want reuse the below query.  I just modify the name of the action in it and reuse the query for other actions I want in the designer.

Note I have also included the portal url pattern so I can click through the the Azure Portal.

let input_RunId = {WorkFlowRunID};
let resourceGroup = "RND_T360_AI_DOCUMENTINTELLIGENCE";
let subscriptionId = "TBC";
let logicAppName = "t360-poc-ai-invoice-process-one-invoice";
let actionName = "WHEN_A_HTTP_REQUEST_IS_RECEIVED";
let azurePortalUrlTemplate = "https://portal.azure.com/#view/Microsoft_Azure_EMA/DesignerEditorConsumption.ReactView/id/%2Fsubscriptions%2F[subscriptionId]f%2FresourceGroups%2F[resourceGroup]%2Fproviders%2FMicrosoft.Logic%2Fworkflows%2F[logicAppName]/location/northeurope/showGoBackButton~/true/isReadOnly~/true/isMonitoringView~/true/runId/%2Fsubscriptions%2F[subscriptionId]f%2FresourceGroups%2F[resourceGroup]%2Fproviders%2FMicrosoft.Logic%2Fworkflows%2F[logicAppName]%2Fruns%2F[RunId]";
let lookups = dynamic([ '[subscriptionId]', '[resourceGroup]', '[logicAppName]', '[RunId]' ]);
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where OperationName == "Microsoft.Logic/workflows/workflowTriggerCompleted"
| where SubscriptionId == subscriptionId
| where ResourceGroup == resourceGroup
| where resource_workflowName_s == logicAppName
| where resource_runId_s == input_RunId
| where Resource == actionName
| extend PortalUrl = replace_strings(azurePortalUrlTemplate, lookups, pack_array(subscriptionId, resourceGroup, logicAppName, input_RunId))
| project TimeGenerated, PortalUrl, actionName, ActionStatus=status_s, RunID=input_RunId, LogicApp=logicAppName, correlation_clientTrackingId_s, _ResourceId

API Management Step

In this case I am using an app insights workspace which actually lives in a different Azure subscription.  I am logging the http headers to app insights in the APIM logging configuration so I can use them to look up a matching request log event for this workflow.

I am also doing the following:

• Extending the logic app headers so i can easily see them in the query response

• Extending some fields from the request body which was logged to help the user

• Crafting a clickable url for the Azure Portal so I can jump into App Insights if I need to

let logicAppRunId = {WorkFlowRunID};
let resourceGroup = "Platform";
let subscriptionId = "TBC";
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 lookups = dynamic([ '[subscription]', '[resourceGroup]', '[appInsightsName]' ]);
let portalUrl = replace_strings(portalUrlTemplate, lookups, pack_array(subscriptionId, resourceGroup, appInsightsName));
requests 
| where customDimensions.["API Name"] == "outbound-api-sap-invoices"
| where customDimensions.["Operation Name"] == "submit-invoice"
| where customDimensions.["Request-x-ms-workflow-run-id"] == logicAppRunId
| extend InvoiceTotal_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).InvoiceTotal)
| extend InvoiceId_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).InvoiceId)
| extend InvoiceDate_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).InvoiceDate)
| extend DueDate_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).DueDate)
| extend CustomerName_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).CustomerName)
| extend CustomerAddress_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).CustomerAddress)
| extend BillingAddress_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).BillingAddress)
| extend VendorName_ = tostring(parse_json(tostring(customDimensions.["Request-Body"])).VendorName)
| extend Request_x_ms_client_tracking_id_ = tostring(customDimensions.["Request-x-ms-client-tracking-id"])
| extend Request_x_ms_correlation_id_ = tostring(customDimensions.["Request-x-ms-correlation-id"])
| extend Request_x_ms_workflow_name_ = tostring(customDimensions.["Request-x-ms-workflow-name"])
| extend Request_x_ms_workflow_resourcegroup_name_ = tostring(customDimensions.["Request-x-ms-workflow-resourcegroup-name"])
| extend Request_x_ms_workflow_run_id_ = tostring(customDimensions.["Request-x-ms-workflow-run-id"])
| extend invoice_id_ = tostring(parse_json(tostring(customDimensions.["Response-Body"])).invoice_id)
| extend success_ = tostring(parse_json(tostring(parse_json(tostring(customDimensions.["Response-Body"])).success)))
| extend azure_portal_url = replace_string(replace_string(portalUrl, "[RequestID]", itemId), "[startTime]", tostring(datetime_add('hour', -1, timestamp)))