Append the columns of a search onto the results of another search many times [lookup, but for a search]

[ccunov]

Search A returns many events for each ID.
Search B returns a single event for each ID.

My end result is a table with each event from search A, with the values from a few fields in search B appended as new columns to EACH event in A.

If that's not clear here's more:

Search A looks like this:

_time,ID,interesting_field
1,1,420
1,2,69
1,3,8008
2,1,5318008
2,2,12
2,3,41
...

Search B looks like this:

_time,ID,tag
1,1,hello
1,2,wuddup
1,3,yo

End-result should look like this:

_time,ID,interesting_field,tag
1,1,420,hello
2,1,5318008,hello
1,2,69,wuddup
2,2,12,wuddup
1,3,8008,yo
2,3,41,yo

Extra tags for the people from google: one-to-many

[MuS]

Hi ccunov,

I used the sample events from search B and created a lookup file with it so it contains :

_time,ID,tag
1,1,hello
1,2,wuddup
1,3,yo

then you can use this SPL to get the expected table view:

search a | rename _time AS time | lookup search_b.csv ID | table time ID int* tag | sort tag

The result looks like this:

Just ignore the first inputlookup this was just me getting your search a sample events in.

Hope this helps ...

cheers, MuS

[ccunov]

I guess what I'm trying to do is exactly what lookup does. Unfortunately I can't dynamically create a lookup, and that wouldn't be efficient anyway. Is there a similar command for a search?

Thanks for putting in the effort to help me btw!

[MuS]

Why can't you create dynamic lookups? Any search result can be saved as lookup using outputlookup command. Also lookup's are a very efficient tool to enrich events or limit events, they can get inefficient when we talk about millions of lines or very high 3 digit MB in size.

cheers, MuS

[MuS]

Hi ccunov,

try something like this:

( search A ) OR ( search B )
| stats values(*) AS * by _time ID

This will combine the events as shown in your expected end result.

Hope this helps ...

cheers, MuS

[ccunov]

This doesn't combine the results into one table, it just returns events from both searches

[MuS]

Yeah, I see what you mean and will post a new answer with a better approach 😉

cheers, MuS