Kusto Query with SubQuery

In this example I wanted to show the runId as well as a tracked property named PersonId for each failed run of a logic app. The tracked property was set in an action named ‘GET_MEDEWERKER_FROM_TOPDESK’.

If we look at the subquery first, we see it selects the runIds for all completed runs with status Failed. If we look at the parent query, you can see we select the specific action named ‘GET_MEDEWERKER_FROM_TOPDESK’. This action
should not be Running, but should have a status of either Failed or Succeeded. Als note how we select the tracked property PersonId and give it a short alias PersonId: PersonId=trackedProperties_PersonId_s

| where status_s in (‘Failed’,’Succeeded’)
| where resource_runId_s in (
| where resource_workflowName_s == ‘la-topdesk-pnb-werknemer-uit-cwd-prod’
| where OperationName in (“Microsoft.Logic/workflows/workflowRunCompleted”)
and status_s == ‘Failed’
| order by TimeGenerated desc
| project resource_runId_s)
| order by TimeGenerated desc
| project Tijd=format_datetime(TimeGenerated, ‘yyyy-MM-dd hh:mm:ss’), Identifier=resource_runId_s, PersonId=trackedProperties_PersonId_s