Kusto loop array with sub query

1

votes

I am trying to define an array and loop through it looking up traces for where the message contains element in my array. Is it possible to do this? For example:

let myIds = datatable (name: string)
[
 "111",
 "222",
 "333",
];

forach (id in myIds)
{
  traces
  | where message contains id
}

I know this isn't the right syntax above but hopefully it explains what I am trying to achieve. In a nutshell, loop through an array and perform a lookup in my logs (specifically traces).

azure-data-explorerkql

1

votes

you can look into using mv-expand or mv-apply

1

votes

The first option is to use has_any. This is a simpler solution that might work for your use case but only if your ID appears as a discrete term within the message.

So if the message is in the form "blah blah ID: 111" it will get picked up, but if it's part of another word then it won't (because has works a little differently from contains).

let myIds = datatable (name: string) [ "111","222","333"];
let traces=datatable(message:string) ["aaaaaaaaaaaaaaaa", "blah blah 111", "blah111 blah", "111blah"];
traces
| where message has_any (myIds)

If you need the functionality of contains (if you need to find every single instance of 111) then you can use mv-apply. This loops through your myIds subtable and does the comparison against each entry individually and then unions all the results. Be aware this means you can get duplicates if multiple IDs are matched in the same message.

let myIds = datatable (name: string) [ "111","222","333"] | summarize make_set(name);
let traces=datatable(message:string) ["aaaaaaaaaaaaaaaa", "blah blah 111", "blah111 blah", "111blah"];
traces
| mv-apply id=toscalar(myIds) to typeof(string) on (where message contains id)

Kusto loop array with sub query

2 Answers