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

Question

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:

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.

Yoni L. Yoni L. · Accepted Answer · 2020-12-04T22:14:38

you could use not(): https://docs.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"))

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

1 Answers