Forum Discussion
How can i whitelist combination of different columns in the query
I Have three columns and if the data in both the three columns matches a specific value , it should not give that result. I can't use "and" as it is not giving the expected result
| A | B | C |
| john | first | last |
| deep | first | last |
| john | first | last |
I want a query which will only display the second row. and operator would not give the expected result
This should work.
| where not(A == "john" and B == ''first" and C == "last")(A == "john" and B == ''first" and C == "last") will get the specific combination of the three rows, so negating this will get anything that is not that specific combination.
Maybe you tried something like this? which would not show anything since each row will be checked separately. B and C are identical on the row that you want, and the rows you don't want, and thus nothing will show.
| where A != "john" and B != ''first" and C != "last"You (may) need an "or" as well? However it's hard to tell unless you provide some sample data, and the KQL so far, like GaryBushey suggested - a DataTable is a good idea. e.g.
let table_ = datatable (fullName_:string, firstName_:string, lastName_:string) [ 'john smith' ,'john' ,'smith' , 'joan smith' ,'joan' ,'smith' , 'deep end' ,'deep' ,'end' , 'john jones' ,'first' ,'last' , ]; table_ | where fullName_ != 'john smith' and ( firstName_ != 'john' or lastName_ != 'smith' )
or, depending on what in Column A?let srch = 'joan smith'; let table_ = datatable (fullName_:string, firstName_:string, lastName_:string) [ 'john smith' ,'john' ,'smith' , 'joan smith' ,'joan' ,'smith' , 'deep end' ,'deep' ,'end' , 'john jones' ,'first' ,'last' , ]; table_ | where fullName_ != srch and ( firstName_ != split(srch,' ')[0] or lastName_ != split(srch,' ')[1] )
- The "and" operator should work just fine. Can you post your KQL that you are using?
