CODEWITHSUNDEEP
azure-data-explorerkqlkusto-explorer
mcool
mcool

Reputation: 775

Count the number of times a value exists in a list - KQL Kusto query

This is the end of my query, the relevant piece:

| project endpoint, clientName, siteName, duration, operation_Id, customDimensions, operation_ParentId, id, target, operation_Name, itemType, client_City, itemCount, type, name, data
| summarize NumberInGrouping = count(), operationIdSet = make_set(operation_Id), operationIdList = make_list(operation_Id) by endpoint, clientName, siteName
| mv-expand operationIdSet to typeof(string)

My question is, how do I do something similar to this:

| project endpoint, clientName, siteName, duration, operation_Id, customDimensions, operation_ParentId, id, target, operation_Name, itemType, client_City, itemCount, type, name, data
| summarize NumberInGrouping = count(), operationIdSet = make_set(operation_Id), operationIdList = make_list(operation_Id) by endpoint, clientName, siteName
| mv-expand operationIdSet to typeof(string)
| extend some_num = countif(array_contains(operationIdList, operationIdSet))

Notice the last line. This does not work because countif is not allowed in this context but I need something that can do the same thing. After mv-expand is ran, operationIdSet is a single value, not a list.

Therefore, my goal is to find out how many times the value in the operationIdSet column exists in the list in the operationIdList column but this must be done after mv-expand because only then is the data grouped correctly.

I tried this:

| mv-expand operationIdSet to typeof(string)
| summarize some_num = countif(set_has_element(operationIdList, tostring(operationIdSet))) by endpoint, clientName, siteName, NumberInGrouping, tostring(operationIdSet), tostring(operationIdList)

But some_num is always 1 so it's definitely not working properly.

Upvotes: 0

Views: 1123

Answers (1)

Aswin
Aswin

Reputation: 7126

You can use the mv-apply operator to iterate over the array and count the number of times a value exists in the list.

| project endpoint, clientName, siteName, duration, operation_Id, customDimensions, operation_ParentId, id, target, operation_Name, itemType, client_City, itemCount, type, name, data
| summarize NumberInGrouping = count(), operationIdSet = make_set(operation_Id), operationIdList = make_list(operation_Id) by endpoint, clientName, siteName
| mv-expand operationIdSet to typeof(string)
| mv-apply operationIdList on (
summarize some_num=countif(operationIdList == tostring(operationIdSet))
)

FIDDLE

In the above code, the last line counts the number of times each operation_Id appears in the list of operation_Id values for each group using the mv-apply operator.

Upvotes: 0

Related Questions