Microsoft Entra Suite Tech Accelerator
Aug 14 2024, 07:00 AM - 09:30 AM (PDT)
Microsoft Tech Community

How can i whitelist combination of different columns in the query

Copper Contributor

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

ABC
john firstlast
deepfirstlast
johnfirst last

 

I want a query which will only display the second row. and operator would not give the expected result

3 Replies
The "and" operator should work just fine. Can you post your KQL that you are using?

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"

@Jonhed 

 

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 @Gary Bushey 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] )

 

Clive_Watson_0-1650370111327.png