CODEWITHSUNDEEP
azurekql
Aviator
Aviator

Reputation: 722

multiple if and else within kql azure

Trying to write a kql query where date should be populated in the "date" column based on the if condition :-

If RawData has a string starting with "" then the result of this - substring(RawData, 0, 1900) which is a date should be populated in the column Date.

If RawData has a string starting with "DD" or "II" then result of this- substring(RawData, 22, 1900) which is a date should be populated in the column Date.

And similarly there are 2-3 more conditions. How to implement this? I was trying something like this -

 T
| project Date=substring(RawData, 0, 24), RawData
 | project Date, RawData=substring(RawData, 0, 1900)
| project Date = iff(RawData contains_cs  "||", "substring(RawData, 22,

1900)","substring(RawData, 0, 1900)"), RawData

Upvotes: 0

Views: 159

Answers (1)

decius
decius

Reputation: 1445

As far as I have understood your question, you could do the following:

let T = datatable (
    RawData: string
)
[
     "DD 2024-07-31 23:25:01.434 Callingfunctionality . II 2024-07-31 23:25:01.412 calling refer-functionality performance tune"
]; 
T
| extend SplitDD = split(split(RawData, "DD ")[1], " ")
| extend SplitII = split(split(RawData, "II ")[1], " ")
| extend DateDD = todatetime(strcat(SplitDD[0], " ", SplitDD[1]))
| extend DateII = todatetime(strcat(SplitII[0], " ", SplitII[1]))
| project DateDD, DateII

So using extend essentially and split.

Here is a working Sample.

UPDATE 2 (with union):

let T = datatable (
    RawData: string
)
[
     "DD 2024-07-31 23:25:01.434 Callingfunctionality . II 2024-07-31 23:25:01.412 calling refer-functionality performance tune"
]; 
let DD = 
T
| extend Split = split(split(RawData, "DD ")[1], " ")
| extend Date = todatetime(strcat(Split[0], " ", Split[1]))
| project Date;
let II = 
T
| extend Split = split(split(RawData, "II ")[1], " ")
| extend Date = todatetime(strcat(Split[0], " ", Split[1]))
| project Date;
union DD, II

Here is this working sample.

Result:

enter image description here

Upvotes: 0

Related Questions