CODEWITHSUNDEEP
azure-log-analyticsazure-data-explorerkql
Danh Nguyen
Danh Nguyen

Reputation: 3

Kusto query: How to summarize by column(s), then check if certain records are in the group

I'm fairly new to Kusto and need to query for certain records in Log analytics.

Problem: Need to summarize by column ActivityId, then check if a list of RunbookNames (another column name) are within the group. I want all activityids that has Foo AND Bar. If it does not contain both then it doesn't satisfy criteria.

Something analogous to SQL query, we have GROUP BY then HAVING clause.

Any idea?

Sample Data:

ActivityId RunbookName Result
5271D9E9 Foo State 2, Status 1
5271D9E9 Foo State 2, Status 1
5271D9E9 Bar State 2, Status 3
394B044 Fooey State 2, Status 1
394B044 Dooey State 2, Status 1
D94321B Dooey State 2, Status 1
9E4371A Foo State 2, Status 1

Expected Results:

ActivityId RunbookName Result
5271D9E9 Foo State 2, Status 1
5271D9E9 Foo State 2, Status 1
5271D9E9 Bar State 2, Status 3

Upvotes: 0

Views: 7014

Answers (1)

Yoni L.
Yoni L.

Reputation: 25895

judging by the given input and output, and your clarification in the comment, the following query could give you a direction.

let RunbookNames = dynamic("Foo", "Bar");
T
| SUMMARIZE r = make_set(RunbookName) by ActivityId
| where array_length(set_intersect(r, RunbookNames)) == array_length(RunbookNames)
| mv-expand RunbookName = r

Upvotes: 1

Related Questions