How to get the number of times the mode of a list is seen in KQL

Question

I need a way to get the number of times the number seen most, is seen using KQL. For example, if this is my list:

[1,1,2,3,4,5]

Then I need to output 2 since 1 is seen 2 times. This is my query:

dependencies
| join kind=inner (
    requests
    | project operation_Id, customDimensions, operation_ParentId, name, id
    | extend req_custom_dim = customDimensions, req_endpoint = name
) on $left.operation_Id == $right.operation_Id
| extend customDimensions = parse_json(req_custom_dim) 
| extend 
    siteName = tostring(customDimensions.SiteName), 
    clientName = tostring(customDimensions.ClientName),
    endpoint = req_endpoint
| where (strlen(trim(" ", clientName)) > 0) and (strlen(trim(" ", siteName)) > 0)
| where type == "SQL"
| project endpoint, clientName, siteName, duration, operation_Id, customDimensions, operation_ParentId, id, target, operation_Name, itemType, client_City, itemCount, type, name, data
| summarize Count = count(), operationIdSet = make_set(operation_Id), avg = count()/array_length(make_set(operation_Id)) by endpoint, clientName, siteName

So I need a way to add a column to the summarize statement that works as follows:

1. Figure out which number is seen the most in the list created by make_set(operation_id)
2. Output the number of times that value is seen

How can I do this?

Answer 1

How to get the number of times the mode of a list is seen in KQL, I need a way to get the number of times the number seen most, is seen using KQL. For example, if this is my list [1,1,2,3,4,5]

I have reproduced in my environment and got expected results as below and used top operator:

 datatable(list: string)[1,
1,
2,
3,
4,
5]
|summarize list2 = make_list(list),sr= make_set(list)
| mv-expand sr to typeof(string)
| mv-apply list2 on (
summarize sn=countif(list2 == tostring(sr))
)
| top 1 by sn
| project sr, sn

Here sr is the value and sn is count.

Fiddle