Linked searches

[Bulluk]

I index 2 log files which have a common ID field in them. I'd like to search against log file 1, get a series of IDs which match my search term and then use those IDs to find info from log file 2, eg

source=Log1 error="some error message" | source=Log2 ID

In the above, the search to the left of the pip finds 1 or more results. On the right of the pipe we search for all events which have the ID from the first part of the search.

Seems like it should be easy but I'm having a "can't see the wood for the trees" moment and can't work it out.

thanks in advance!

[Ayn · Accepted solution]

As I understand your requirement I think your best option might be to use a regular subsearch (http://docs.splunk.com/Documentation/Splunk/latest/User/HowSubsearchesWork ).

Step-by-step: " I'd like to search against log file 1, get a series of IDs which match my search term"

source=Log1 error="some error message"

"and then use those IDs to find info from log file 2"

source=Log2 [search source=Log1 error="some error message" | fields ID]

EDIT: If you want the actual events from both sources, you could either use join as has been suggested, or transaction that will create a new event (a transaction) that combines all events having the same ID.

(source=Log1 error="some error message") OR (source=Log2) | transaction ID

[Bulluk]

source=Log2 [search source=Log1 error="some error message" | fields ID]

This is the one that nailed it for me. Thanks for your help 🙂

[Ayn]

You could also use transaction. See my updated answer.

[Ayn]

OK, so you want not just the ID from log1, but also the actual event? In that case the requirement will indeed be a bit different, as the subsearch would essentially return something like ((id="id1") OR (id="id2") OR ... ) to the outer search.

[Bulluk]

Log1 contains the error message and the ID.
Log2 contains the ID and some other info I'm interested in. In a scripting language I'd pass the result of the first query into an array then iterate around it to find the info I want in Log2 ie

$IDs = 1,2,3,4

foreach ($ID in $IDs)
{
select $info1, $info2
where $IDfield = $ID
}

[johandk]

source=log1 error="ERROR" | join ID [search source=Log2]

This will do a SQL like join on the ID field.

[Bulluk]

I ended up using Ayn's solution below but I think the join approach would have worked if I'd spent time with it. Thanks for your help

[Bulluk]

This looks close but I'm getting odd results

source=log1 error="ERROR" returns over 100 results

source=log1 error="ERROR" | join ID [search source=Log2] only returns 1 result despite there being a 1 to 1 relationship with the ID.

I shall play a bit more to see if I can work out what I'm doing wrong