Search code examples
azure-data-explorerkql

Kusto !has_any | where value does not contain any value in set

Is there a built-in way in Kusto to check that a value does not contain multiple items? I know that I can use has_any to check if an item contains any values in a set, but I can't seem to get it to work with an "!" operator. Example:

let Employees = datatable(Id:int, Name:string, Position:string ) 
[
   1, "Bob", "General Manager",
   2, "Mary", "Coordinator",
   3, "John", "Sales Representitive"
];
Employees
| where Position !has_any("Manager", "Sales")

Expected output:

Expected output

If I remove the not operator (!) it works, and returns info for Bob and John. But I want to reverse it from has any/contains any to does not contain any. I have also tried

| where Position has_any !("Manager", "Sales")

But it seems like any combination of has_any and "!" throws a syntax error and won't run. Is there a way to do this without listing out individual !contains statements? i.e. this is not what I am looking for:

| where Position !contains "Manager" and Position !contains "Sales"

This isn't a big deal for just two conditions but when you're dealing with a long list it would be much better to use has_any with a simple exclamation mark in front of it.


-- UPDATE --

I only recently found out that has_any actually does not pick up if a field contains any item in a set. To be technically correct, it only picks up if the field has any whole word matches from the set. To clarify, here is an example:

let TestTable = datatable(Id:int, FirstName:string, LastName:string ) 
[
   1, "John", "De La Cruz",
   2, "Susana", "Vanderbelt",
   3, "Stan", "Manlyman",
   4, "Henry", "Van De Camps",
   5, "Alissa", "New man"
];
TestTable
| where not(LastName has_any("de", "man"))

Take careful note of the results:

Table results

If you where to replace the last line with this

| where LastName !contains("de") and LastName !contains("man")

It would yield nothing in the results set. So if you're really going for "where field doesn't contain any value in the set" then as far as I know you have to write out each one individually, there is no such keyword where you can pass in a set for that functionality. If anyone knows of such a keyword please let me know in the comments or a new answer and I will update this question.

Solution

  • you could use not(): https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/notfunction

    for example:

    let Employees = datatable(Id:int, Name:string, Position:string ) 
[
   1, "Bob", "General Manager",
   2, "Mary", "Coordinator",
   3, "John", "Sales Representitive"
];
Employees
| where not(Position has_any("Manager", "Sales"))