IFF function in KQL azure analytics?

Question

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?

You already asked this question and was asked to provide sample data + required results in textual format, e.g., CSV. — David דודו Markovitz, Jan 20 '23 at 15:36
Please avoid tag spamming. Tag your query with the actual service you are using and not various services that make usage of KQL. — David דודו Markovitz, Jan 20 '23 at 15:37
Please improve the question so it could server well other SO users — David דודו Markovitz, Jan 23 '23 at 14:28

score 0 · Accepted Answer · answered Jan 20 '23 at 19:03

// 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"]

Fiddle

IFF function in KQL azure analytics?

1 Answers1