0

I am having trouble with KQL when using union to include 2 log analytics workspaces. My task is to count all failed ADFPipelineRun from 2 different Log Analytics Workspaces; A and B

Here is my syntax for running a Kusto query from Log Analytics Workspace A, and include Workspace B:

ADFPipelineRun //which is Workspace A
|union 
workspace("<WorkspaceID for workspace B>").ADFPipelineRun 
| where Status == "Failed"
| where TimeGenerated > ago(90d)
| summarize CountFailed=count()

Problem is it seems some rows are not counted, I suspect the use of union has something to do with it.

If I run the below query first from workspace A and then from workspace B, and then just add the counts, then I can see that using the union is missing approx 20% of the rows in the count.

ADFPipelineRun
| where Status == "Failed"
| where TimeGenerated > ago(90d)
| summarize Count=count()

From KQL-documentation union should return all rows when counting, but that does not happen. Rather it seems union returns distinct row-count. So, how to return the count of ALL rows?

Soffi
  • 1
  • 1

1 Answers1

0

You can use the countif() aggregation function:

ADFPipelineRun //which is Workspace A
|union 
workspace("<WorkspaceID for workspace B>").ADFPipelineRun 
| summarize CountFailed = countif(Status == "Failed")
            TimeStatus = countif(TimeGenerated > ago(90d))

You can refer to Kusto | Summarize count() multiple columns with where clauses, union operator and Combine 2 result set using union in Kusto

Ecstasy
  • 1,866
  • 1
  • 9
  • 17