Filter multivalue field based on regex generated by subsearch

[giladirim]

Hi,

I have "impression" events for a mobile page that has many games on it, and they have 1 field called "game_ids" which is a comma delimited list of id's of the relevant games. I also have a lookup table which holds a small number of games which i'm particularly interested in.

Because the number of impression events is very large, and there can be dozens of games in each impression, I want to filter these events before I use MVEXPAND.

Took me a bit, but I came up with the following solution - subsearch the lookup into a regex string literal, then use it to filter the events:

index=main sourcetype=mobile_catalog_events event_name=impression
| eval relevant_ids=  [ inputlookup my_lookup| table id| rename id as game_ids | mvcombine  delim="|" game_ids | nomv game_ids | eval game_ids="\"".game_ids."\"" | return $game_ids]
| regex game_ids=relevant_ids

However, the search returns no results. The subsearch is verified to work. I also took a sample of game_ids, and ran them through an external online REGEX tool, and the regex works.

Any assistance would be greatly appreciated!

[somesoni2 · Accepted solution]

This should do the trick:

 index=main sourcetype=mobile_catalog_events event_name=impression
 | eval relevant_ids=  [ inputlookup my_lookup| table id| rename id as game_ids | mvcombine  delim="|" game_ids | nomv game_ids | eval game_ids="\"".game_ids."\"" | return $game_ids]
 | where match(game_ids,relevant_ids)