KQL Has_any Operator

Yang, Steven 151 Reputation points
2023-12-14T17:59:31.0733333+00:00

Hello,

I need to develop a KQL query that can perform a lookup on a string array that contains different text descriptions. the lookup will be based on a set of key terms. I was hoping to leverage "has_any" operator, which almost had it working. Then I came across below problem.

String 5 & 6 should not be in the output. looks like symbols such as "-","_" are dropped during indexing, which makes sense, but anyway to achieve this use case?

User's image

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
501 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Baumgarten 101.5K Reputation points MVP
    2023-12-14T18:43:17.8566667+00:00

    Hi @Yang, Steven ,

    you have to filter your result set a second time to get rid of the NOT-lines in the string. For instance like this:

    let Example = datatable(Query:string)[
"String1: should be in the output. Keyterm:OUTPUTVIEW",
"String2: should be in the output. Keyterm:OUTPUTVIEW",
"String3: should be in the output. Keyterm:OUTPUTVIEW",
"String4: should be in the output. Keyterm:IMPORTANT",
"String5: should NOT be in the output. Keyterm:OUTPUTVIEW_NOT",
"String6: should NOT be in the output. Keyterm:OUTPUTVIEW-NOT"
];
Example
| where Query has_any (dynamic(["OUTPUTVIEW", "IMPORTANT"]))
| where Query !has "NOT"

    or

    let Example = datatable(Query:string)[
"String1: should be in the output. Keyterm:OUTPUTVIEW",
"String2: should be in the output. Keyterm:OUTPUTVIEW",
"String3: should be in the output. Keyterm:OUTPUTVIEW",
"String4: should be in the output. Keyterm:IMPORTANT",
"String5: should NOT be in the output. Keyterm:OUTPUTVIEW_NOT",
"String6: should NOT be in the output. Keyterm:OUTPUTVIEW-NOT"
];
Example
| where Query has_any (dynamic(["OUTPUTVIEW", "IMPORTANT"]))
| where Query !contains "NOT"

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards Andreas Baumgarten

  2. Andreas Baumgarten 101.5K Reputation points MVP
    2023-12-14T20:47:17.0133333+00:00

    Hi @Yang, Steven ,

    sorry, I wasn't aware the Keyterm: varies that way.

    Maybe this works better for you. In foobar define the Keyterms that should be in the output (case sensitive!).

    let foobar = dynamic(['OUTPUTVIEW', 'IMPORTANT']);
let Example = datatable(Query:string)[
"String1: should be in the output. Keyterm:OUTPUTVIEW",
"String2: should be in the output. Keyterm:OUTPUTVIEW",
"String3: should be in the output. Keyterm:OUTPUTVIEW",
"String4: should be in the output. Keyterm:IMPORTANT",
"String5: should NOT be in the output. Keyterm:OUTPUTVIEW_NOT",
"String6: should NOT be in the output. Keyterm:OUTPUTVIEW-NOT",
"String7: should NOT be in the output. Keyterm:ZONK",
"String8: should NOT be in the output. Keyterm:OUTPUTVIEW-foobar",
"String9: should NOT be in the output. Keyterm:OUTPUTVIEW-123-383",
"String10: should NOT be in the output. Keyterm:OUTPUTVIEW-xyz",
"String11: should NOT be in the output. Keyterm:IMPORTANT-123",
"String12: should NOT be in the output. Keyterm:IMPORTANT-NOT",
"String13: should NOT be in the output. Keyterm:IMPORTANT_NOT"
];
Example
| extend splitted=split(Query, 'Keyterm:')[1]
| where splitted in (foobar)
| project Query

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards

    Andreas Baumgarten