I am trying to create an ifelse statement in KQL but I cannot find documentation about if its possible to do what I am trying. Basically what I am trying to do to summarize ONLY if the conditions(EventResults=="Success") are met and do another summarize if they arent. summarize SuccessCount=count(), SuccessUsers=makeset(User) by SrcDvcIpAddr, bin(TimeGenerated, timeframe) ELSE summarize FailCount=count(), SuccessUsers=makeset(User) by SrcDvcIpAddr, bin(TimeGenerated, timeframe)
I didnt really find any information about where and how I could use iff other than with the extend operator on the offical microsoft documentation page. Is what I am trying to do possible?
// Sample data generation. Not Part of the solution.
let imAuthentication = materialize(range i from 1 to 500 step 1 | extend User = strcat("user_", tostring(toint(rand(10))+1)), SrcDvcIpAddr = tostring(dynamic(["1.1.1.1", "2.2.2.2", "3.3.3.3"])[toint(rand(2))]), EventResult = tostring(dynamic(["Success", "Failure"])[toint(rand(2))]), EventType ="Logon", EventProduct = "AAD", TimeGenerated = ago(12h * rand()));
// Solution starts here.
let sigin_threshold = 5;
let endtime = 12h;
let timeframe = 15m;
imAuthentication
|where TimeGenerated >= ago(endtime)
and EventProduct == "AAD"
and EventType =="Logon"
and EventResult in ("Success", "Failure")
and SrcDvcIpAddr != "-"
and isnotempty(User)
|summarize SuccessCount = countif(EventResult == "Success")
,FailCount = countif(EventResult == "Failure")
,SuccessUsers = make_set_if(User, EventResult == "Success")
,FailUsers = make_set_if(User, EventResult == "Failure")
by SrcDvcIpAddr
,bin(TimeGenerated, timeframe)
|where FailCount > sigin_threshold
SrcDvcIpAddr | TimeGenerated | SuccessCount | FailCount | SuccessUsers | FailUsers |
---|---|---|---|---|---|
2.2.2.2 | 2023-01-20T10:15:00Z | 3 | 7 | ["user_3","user_2"] | ["user_8","user_2","user_10","user_6","user_3","user_5"] |
2.2.2.2 | 2023-01-20T11:00:00Z | 4 | 6 | ["user_9","user_3","user_6"] | ["user_8","user_7","user_2","user_4","user_9"] |
1.1.1.1 | 2023-01-20T11:15:00Z | 4 | 6 | ["user_10","user_7","user_4"] | ["user_9","user_4","user_3"] |
1.1.1.1 | 2023-01-20T11:45:00Z | 3 | 6 | ["user_2","user_1","user_7"] | ["user_2","user_1","user_9","user_4"] |
2.2.2.2 | 2023-01-20T12:15:00Z | 3 | 8 | ["user_4","user_5"] | ["user_6","user_8","user_7","user_2","user_3","user_1","user_5"] |