KQL query matching values and count

Question

i am totally new to Kusto and would like somebody advice and help.

I have a file with a lot of data in it. this is a very short sample:

what I would like to do, is to compare the name,userID and count how many times those 2 column repeat themselves in a timespan of minutes (based on the timestamp) or days (just to make it easy I can convert the days in minutes).

The bit of code that I used so far it seems to consider only one of the columns and count, it doesn't check if they appeared before.

this is what I was using:

"| summarize count() by bin(_timestamp, 4320m), name, userID"

with the correct query, what I am expecting to receive back (assuming that the timestamp is 13:38:01) for all the column, is to have a extra column named count and value of 2 for value 1 and value 2

I hope I made my question clear enough, and please if you need more informations, just ask.

Thank you so so much guys

can you please provide a sample input, and the output you expect for that input? — rony l

yifats yifats · Accepted Answer · 2021-05-27T17:00:58

I'm not entirely following your intention. The below example returns the count per each combination of name, userID, and 3d period, as per your sample data. If this is not the expected output, can you please share the expected input and output in form of a datatable, as below?

let T = datatable(_timestamp:datetime, name:string, message:string, userID:string)
[
    datetime(2021-05-26 13:41:16), "value1", "message1", "number1",
    datetime(2021-05-26 13:38:01), "value2", "message2", "number2",
    datetime(2021-05-26 13:41:16), "value1", "message1", "number1",
    datetime(2021-05-26 13:34:05), "value2", "message2", "number2"
];
T
| summarize count() by bin(_timestamp, 3d), name, userID

KQL query matching values and count

1 Answers