if statment in a KQL query?

%3CLINGO-SUB%20id%3D%22lingo-sub-1363551%22%20slang%3D%22en-US%22%3Eif%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1363551%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI%20was%20wondering%20if%20its%20possible%20to%20write%20an%20if%20statement%20in%20a%20kql%20query%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3Efor%20example%20i%20have%20a%20dropdownlist%2C%20and%20based%20on%20the%20value%20i%20want%20to%20execute%20a%20query%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EAnyone%20know%20how%20this%20is%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1363551%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EKusto%20language%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ELog%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364100%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364100%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567396%22%20target%3D%22_blank%22%3E%40FeintBE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhere%20is%20the%20drop%20down%20list%2C%20is%20it%20from%20a%20Workbook%20parameter%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThere%20is%20IIF()%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fiiffunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-explorer%2Fkusto%2Fquery%2Fiiffunction%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1372822%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1372822%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20my%20parameter%20comes%20from%20a%20dropdownlist%2C%20i%20have%20json%20values%20for%20the%20dropdownlist%3C%2FP%3E%3CP%3EThe%20parameter%20i%20will%20use%20is%20called%20%7BHoneytoken%3Alabel%7D%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FeintBE_0-1588920934270.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F189996iF77394E227569790%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22FeintBE_0-1588920934270.png%22%20alt%3D%22FeintBE_0-1588920934270.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20i%20want%20to%20achieve%20is%20that%20based%20on%20the%20dropdownlist%20value%20there%20should%20be%20another%20query%20be%20executed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%20u%20have%20this%20query%20%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22MainPC%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E4663%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EI%20want%20this%20query%20to%20be%20executed%20in%20a%20grid%20form%20on%20my%20workbook%20when%20i%20choose%20the%20value%20file%20from%20the%20dropdownlist.%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3Ei%20was%20thinking%20of%20putting%20my%20query%20in%20a%20let%20variable%20like%20so%20%3A%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3Elet%20q%20%3D%26nbsp%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20Computer%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22MainPC%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E4663%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThen%20use%20another%20SecurityEvent%20with%20the%20iff()%20%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20extend%20test%20%3D%20iff(%7BHoneytoken%3Alabel%7D%20%3D%3D%20%22File%22%2Cq%2C%22none%22)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3ESo%20if%26nbsp%3B%7BHoneytoken%3Alabel%7D%20is%20equal%20to%20File%20run%20the%20q%20variable%20(Query)%20else%20do%20%22none%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EBut%20i%20get%20the%20error%2C%26nbsp%3B'extend'%20operator%3A%20Failed%20to%20resolve%20column%20or%20scalar%20expression%20named%20'File'...%20Click%20to%20Retry.%20%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1378111%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1378111%22%20slang%3D%22en-US%22%3EA%20parameter%20is%20text%2C%20so%20use%20%22%20%22%20e.g.%3CBR%20%2F%3E%3CBR%20%2F%3E%7C%20extend%20test%20%3D%20iff(%22%7BHoneytoken%3Alabel%7D%22%20%3D%3D%20%22File%22%2C%22Yes%20its%20a%20file%22%2C%22No%20its%20not%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1380933%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1380933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%7C%20extend%20test%20%3D%20iff(%22%7BHoneytoken%3Alabel%7D%22%20%3D%3D%20%22File%22%2C%22Yes%20its%20a%20file%22%2C%22No%20its%20not%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethis%20works%20but%20instead%20of%20%22yes%20its%20a%20file%22%20i%20would%20rather%20return%20a%20query%20and%20not%20a%20string.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eeach%20dropdownlist%20value%20need%20to%20return%20a%20different%20query%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382048%22%20slang%3D%22en-US%22%3ERe%3A%20if%20statment%20in%20a%20KQL%20query%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382048%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F567396%22%20target%3D%22_blank%22%3E%40FeintBE%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20done%20this%20another%20way.%26nbsp%3B%20I%20have%20a%20%3CSTRONG%3Eparameter%3C%2FSTRONG%3E%20called%20KQLquery%2C%20which%20has%20the%20KQL%20in%20a%20JSON%20drop-down.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22kqlQuery.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190878iA223E2CCDE458CCE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22kqlQuery.jpg%22%20alt%3D%22kqlQuery.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20all%20you%20need%20to%20do%20is%20%22Add%20a%20query%22%20and%20use%20the%20parameter%20name%20%7BKQLquery%7D%20in%20this%20case.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22query.jpg%22%20style%3D%22width%3A%20627px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190877i8A3826EDCA96D053%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22query.jpg%22%20alt%3D%22query.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

 

I was wondering if its possible to write an if statement in a kql query

for example i have a dropdownlist, and based on the value i want to execute a query

 

Anyone know how this is done?

 

 

5 Replies
Highlighted

@FeintBE 

 

Where is the drop down list, is it from a Workbook parameter?

 

There is IIF() https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/iiffunction

 

 

 

 

Highlighted

@Clive Watson 

 

Yes my parameter comes from a dropdownlist, i have json values for the dropdownlist

The parameter i will use is called {Honeytoken:label}

FeintBE_0-1588920934270.png

 

What i want to achieve is that based on the dropdownlist value there should be another query be executed.

 

for example u have this query :

SecurityEvent
| where Computer contains "MainPC"
| where EventID == 4663
 
I want this query to be executed in a grid form on my workbook when i choose the value file from the dropdownlist.
 
i was thinking of putting my query in a let variable like so :
let q = 
SecurityEvent
| where Computer contains "MainPC"
| where EventID == 4663;
 
Then use another SecurityEvent with the iff() :
SecurityEvent
| extend test = iff({Honeytoken:label} == "File",q,"none")
 
So if {Honeytoken:label} is equal to File run the q variable (Query) else do "none"
 
 
But i get the error, 'extend' operator: Failed to resolve column or scalar expression named 'File'... Click to Retry.

 

 

Highlighted
A parameter is text, so use " " e.g.

| extend test = iff("{Honeytoken:label}" == "File","Yes its a file","No its not")
Highlighted

@Clive Watson 

 

| extend test = iff("{Honeytoken:label}" == "File","Yes its a file","No its not")

 

this works but instead of "yes its a file" i would rather return a query and not a string.

 

each dropdownlist value need to return a different query

Highlighted

@FeintBE 

 

I've done this another way.  I have a parameter called KQLquery, which has the KQL in a JSON drop-down. 

kqlQuery.jpg

 

Then all you need to do is "Add a query" and use the parameter name {KQLquery} in this case.

query.jpg