KQL list of strings contains any value of list of strings

user_7252805 0
Viewed 415

I have an API that executes some KQL.

I'm executing a KQL that filters all rows such that some column (that is of type list of string) contains any of the values in some given list of strings.

Basically:

let foo1 = datatable(d: dynamic) [
    dynamic([""]),
    dynamic(["foobar"]),
    dynamic(["Alice", "Bob"]),
    dynamic(["Alice"])
];
foo1
| where d has_any (dynamic(["Alice", "otherthing"]))

Which outputs the last 2 rows as I expect - I wanted all rows where d has any of the values "Alice", "otherthing".

Everything works as expected, until I want all rows that contain the empty string value:

let foo1 = datatable(d: dynamic) [
    dynamic([""]),
    dynamic(["foobar"]),
    dynamic(["Alice", "Bob"]),
    dynamic(["Alice"])
];
foo1
| where d has_any (dynamic([""]))

and the result above outputs all rows, instead of only the first row, which has the empty string value.

  1. Why?
  2. What can I change in order for it to work as I expect it?
kql azure-data-explorer azure
1 Answers

1.

Apparently any string has the empty string, e.g.:

print "abc" has ""
print_0
true

Fiddle

2.

It seems you are looking for a full match and not a partial match.
In this case, has might lead to undesired results, e.g.:

print dynamic(["The story of Alice in wonderland", "Bob"]) has "Alice"
print_0
true

Fiddle

Solution:

set_intersect()

let foo1 = datatable(d: dynamic) [
    dynamic([""]),
    dynamic(["foobar"]),
    dynamic(["Alice", "Bob"]),
    dynamic(["Alice"])
];
foo1
| where array_length(set_intersect(dynamic(["Alice", "otherthing"]), d)) > 0
d
["Alice","Bob"]
["Alice"]

Fiddle

let foo1 = datatable(d: dynamic) [
    dynamic([""]),
    dynamic(["foobar"]),
    dynamic(["Alice", "Bob"]),
    dynamic(["Alice"])
];
foo1
| where array_length(set_intersect(dynamic([""]), d)) > 0
d
[""]

Fiddle

user_63364790
Related