How can I set multiple variables inside a KQL iff statement?

Question

How can I set multiple variables inside a KQL iff statement?

Tyler 1

I have an SQL statement like this:

if @stopDate is null  
begin  
  set @stopDate=dateadd(day,-1,convert(date,getdate()))  
  set @startDate=dateadd(day,1,dateadd(month,-1,@stopDate))  
end

How can I convert this to KQL? I can set one variable fine, but I'm not sure how to do two.

Answer 1

Maxim Sergeev 6,386 Microsoft Employee

Hi @Tyler ,

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/casefunction
or
https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/iiffunction

Tyler 1 Reputation point

2022-09-03T06:18:53.323+00:00
Hi @Maxim Sergeev ,

I have tried iff but I am not sure how to set multiple variables. As an example:

In SQL:

if true begin set x = 5 set y = 5 end

In KQL I would do something like:
iff(true, x = 5 and y = 5, "")

But I'm not sure how to set the two variables in one statement.

Maxim Sergeev 6,386 Microsoft Employee

Use let for the variables, as an example below

    let start_time=startofday(ago(24h));  
    let end_time=startofday(now());  
    let serviceName = "Custom Service";  
    let AVAILsla = 90;  
    let hbav=Heartbeat  
        | where TimeGenerated > start_time and TimeGenerated < end_time  
        | summarize heartbeat_per_hour=count() by bin_at(TimeGenerated, 1h, start_time), Computer  
        | extend available_per_hour=iff(heartbeat_per_hour > 0, true, false)  
        | summarize total_available_hours=countif(available_per_hour == true) by Computer  
        | extend total_number_of_buckets=round((end_time - start_time) / 1h)  
        | extend availability_rate=total_available_hours * 100 / total_number_of_buckets  
        | order by availability_rate desc;  
    hbav  
    | summarize  
        Name = serviceName,  
        avg(availability_rate),  
        availabilitySLA = iif(avg(availability_rate) < AVAILsla, "Bad", "Good"),  
        ComputerList = makeset(Computer),  
        dcount(Computer)  
    | extend SLAobjectiveMet = iif(availabilitySLA == "Good" , "SLA ok", "SLA Not Ok")  
    | extend HealthState = iif (availabilitySLA == "Good", "✔️", "⚠️")  
    | project serviceName, SLAobjectiveMet, HealthState

Answer 2

ShaikMaheer-MSFT 30,041 Microsoft Employee

Hi @Tyler ,

Thank you for posting query in Microsoft Q&A Platform.

You can consider writing code as below in this case.

let stopDate = iif(testBool, dateadd(...), <something else>);  
let startDate = iif(testBool, dateadd(...), <something else>);

Hope this helps. Please let me know if any further queries.

-------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 30,041 Reputation points Microsoft Employee

2022-09-14T05:26:50.94+00:00

Hi @Tyler ,

Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

How can I set multiple variables inside a KQL iff statement?

2 answers

Activity