AND operator in KQL

%3CLINGO-SUB%20id%3D%22lingo-sub-2773837%22%20slang%3D%22en-US%22%3EAND%20operator%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773837%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20can%20we%20whitelist%20combination%20of%20columns%20using%20KQL.%20For%20Eg.%20I%20want%20to%20create%20exclusion%20like%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7C%20where%20column1%20!contains%20%22abc%22%20and%20column2%20!contains%20%22qwe%22%20and%20column3%20!contains%20%22xyz%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhile%20this%20looks%20pretty%20straight%20forward%20but%20my%20observation%20is%20that%20it%20does%20not%20make%20exclusion%20based%20on%20above%203%20combination%20of%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESeems%20like%20I'm%20missing%20something%20very%20basic%20but%20not%20able%20to%20identify%20what.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773975%22%20slang%3D%22en-US%22%3ERe%3A%20AND%20operator%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1121150%22%20target%3D%22_blank%22%3E%40jainshamu%3C%2FA%3E%26nbsp%3BYou%20can%20always%20use%20multiple%20lines.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7C%20where%20column1%20!contains%20%22abc%22%0A%7C%20where%20column2%20!contains%20%22qwe%22%0A%7C%20where%20column3%20!comtains%20%22xyz%22%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2774268%22%20slang%3D%22en-US%22%3ERe%3A%20AND%20operator%20in%20KQL%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2774268%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46875%22%20target%3D%22_blank%22%3E%40Gary%20Bushey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20have%20expected%20the%20original%20query%20to%20work%2C%20similar%20example%20I%20use%20(slightly%20modified).%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EUsage%0A%7C%20where%20tostring(IsBillable)%20!contains%20%22false%22%20and%20%0A%20%20%20%20%20%20%20%20DataType%20!in(%22Operation%22%2C%20%22Usage%22)%20and%0A%20%20%20%20%20%20%20%20DataType%20!startswith%20%22VM%22%20and%20%0A%20%20%20%20%20%20%20%20Solution%20!contains%20%22Log%22%0A%7C%20summarize%20by%20DataType%2C%20Solution%2C%20IsBillable%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

How can we whitelist combination of columns using KQL. For Eg. I want to create exclusion like below:

 

| where column1 !contains "abc" and column2 !contains "qwe" and column3 !contains "xyz"

 

while this looks pretty straight forward but my observation is that it does not make exclusion based on above 3 combination of criteria.

 

Seems like I'm missing something very basic but not able to identify what.

4 Replies

@jainshamu You can always use multiple lines.

| where column1 !contains "abc"
| where column2 !contains "qwe"
| where column3 !comtains "xyz"

 

@Gary Bushey 

 

I would have expected the original query to work, similar example I use (slightly modified). 

 

Usage
| where tostring(IsBillable) !contains "false" and 
        DataType !in("Operation", "Usage") and
        DataType !startswith "VM" and 
        Solution !contains "Log"
| summarize by DataType, Solution, IsBillable

 

@CliveWatson  Out of curiosity, Is there any benefit to using one way over the other?  Is one faster than the other?

While I could not unravel the mystery around use of !contains & AND operator, I finally managed to do achieve end result with something like

| extend X = column1 contains "abc" and column2 contains "qwe" and column3 contains "xyz"
| where X != true