How to use IN function with VALUE-LIST as a search or lookup

[koshyk]

hi,
We have a SPL which emits hostname as a single value, but this needs to be checked against a valid list of hostnames on every line. The list is "colon separated"

So ideally, we need to check if

server01
server02

is present in

List1,server101:server102:server103
List2,server04:server02:server05

So in above example, the List2 and server02 should have a match. Please note, the List is a search itself.
Ideally looking for something like

| where IN (servername, [|search myListSearch| fields serverlist|split(serverlist,":")])

Any way to do this using IN operator? ideally i wanted to check if server01 or server02 is present IN the lists in an efficient and short way (rather than splitting the list and comparing). Ideally a "grep -w" should be sufficient

[woodcock]

Like this:

...  | where [search myListSearch
             | fields serverlist
             | makemv delim=":" serverlist
             | mvexpand serverlist
             | rename serverlist AS servername]

[maciep · Accepted solution]

You could probably accomplish this with a "normal" subsearch, but I think this works if you want to use the IN function. I'm just reformatting your server list so it looks like "server01","server02", xxx since that's what the function expects.

   | where IN (servername, [
           |search myListSearch
           | fields serverlist
           |eval serverlist = "\"" . replace(serverlist,":","\",\"") . "\""
           | return serverlist 
          ]
      )