Useful Log Analytics queries

As of now, there’s no uniform monitoring solution that we can use for all sorts of Azure resources. As an example, for API Management we use Application Insights. For Logic Apps we use Log Analytics. Recently, Microsoft has tried to put all these monitoring solutions under one umbrella: Azure Monitor. But that’s not the focus of this post. In this post we will show two Log Analytics queries, that will be a useful starting point for the rich Kusto Query Language.

| where resource_workflowName_s contains ('relation') 
  and resource_workflowName_s has ('t-la')   
| where OperationName == "Microsoft.Logic/workflows/workflowRunCompleted"  
| where TimeGenerated > ago(24h)
| extend Success = iff(status_s == "Succeeded", 1, 0), 
  Failed = iff(status_s == "Failed", 1, 0)
| summarize SuccesCount = sum(Success), FailedCount = sum(Failed) 
  by resource_workflowName_s
| project resource_workflowName_s, SuccesCount, FailedCount
| order by  resource_workflowName_s asc

The above query shows a summary where you can see the succesful and failed runs per Logic App. Note the first where clause “where resource_workflowname_s contains (‘relatie’)”. By using “contains” instead of “has” we will select all Logic Apps that have “relation” in the name, including “relationmutations” or “relationcorrections”. With “has” we would only select Logic Apps with “relation”, so we would miss out on a lot of Logic Apps.

Another where clause to note is on tho operation name: where OperationName == “Microsoft.Logic/workflows/workflowRunCompleted”. If we use this where clause we will get the results on the Logic App level. If we leave out this class we will get the number of succesful/failed actions within each Logic App summarized by Logic App. This will give us much higher numbers, which is not correct.

Finally, we see a where clause that restricts TimeGenerated to the last 24 hours. That’s a bit restrictive. We can leave out the time span selection in the query and select the time span via the Log Analytics Query window or the Azure Dashboard.

| project resource_workflowName_s, resource_runId_s, OperationName, 
  error_message_s, status_s, error_code_s, TimeGenerated
| where OperationName in ("Microsoft.Logic/workflows/workflowRunCompleted")
| where resource_workflowName_s contains ('relation')   
   and resource_workflowName_s has ('t-la')
| where status_s == "Failed"
| where TimeGenerated > ago(24h)
| order by resource_workflowName_s asc, resource_runId_s asc, TimeGenerated desc

The second query is much like the first query when it comes to the where clauses. Here we show information on all failed runs. Obviously, failed runs are the most interesting runs for troubleshooting. Note that we use the project clause to limit the number of columns returned by the select query. Resource_workflowName_s is the name of the Logic App. Resource_runId_s is the unique identifier of the Logic App run. We can use this id to select the correct Logic App run via the Logic App window. Note that we can’t use the rows in the Failed Runs query as a hyperlink to directly jump to the Logic App instance.

To see useful Application Insights queries, see Link.

Log Analytics can be enabled for Logic Apps in the Azure Portal and via ARM templates. Log Analytics queries can be pinned to an Azure Dashboard for easy access. Azure Dashboards can be deployed via an ARM template.