topic Re: Joining two log files that have a common field in Splunk Search

Joining two log files that have a common field

Al — Thu, 04 Nov 2010 21:46:21 GMT

Hi All -

I am pretty new at advanced splunk searching, so I'm probably missing something very easy. I have two access log files that are of the same request, but from different servers, logging different things. They share a common field that is unique per request. I'd like to join these two files in a splunk search. I've easily whipped up a search using join which seems to work, however the main search results screen only shows one of the two files as output. I'd like to see a combination of both files instead. Here are examples:

file 1:

10.10.10.10 - - [04/Nov/2010:10:40:02 -0400] "GET /favicon.ico HTTP/1.1" 200 318 "-" AAABBBCCCDDDEEEFFF

file 2:

10.10.10.10 myusername AAABBBCCCDDDEEEFFF

AAABBBCCCDDDEEEFFF is unique, and common between the two. I turned that into a field called UniqueID on both. What I'm ultimately after is a single result that looks like (or something similar):

10.10.10.10 - - [04/Nov/2010:10:40:02 -0400] "GET /favicon.ico HTTP/1.1" 200 318 "-" AAABBBCCCDDDEEEFFF myusername

The join I have come up with is simply:

sourcetype="access_log_1" | join UniqueID [search sourcetype="access_log_2" ]

If I save these results a csv, it works as expected - however, I can't see the results layed out nicely in the splunk interface. Am I missing something?

Thanks,

Re: Joining two log files that have a common field

sideview — Fri, 05 Nov 2010 00:33:58 GMT

You dont really want to use join in this case. Join is the most intuitive direction to go in, but it's not very efficient and it's pretty cumbersome.

there are two other ways to do it that are usually better -- transaction and stats.

1) Stats. Im not sure what you're looking to do, but here's a made-up example showing the basic idea:

sourcetype="access_log_1" OR search sourcetype="access_log_2" | stats first(someField) first(someOtherField) sum(bytes) by UniqueID

This will allow you to merge the two rows into a single row and calculate statistics on the pairs.

2) Transaction

sourcetype="access_log_1" OR search sourcetype="access_log_2" | transaction UniqueID

This is a little different in how it does it, but similar. transaction takes the two single line events and basically makes a single multiline event. Any fields present on either event get carried over, (and where the field is present on both you will get multivalued fields in the process)

Re: Joining two log files that have a common field

Al — Fri, 05 Nov 2010 20:46:48 GMT

Hi nick,

transaction is perfect. Thank you very much.

Re: Joining two log files that have a common field

sideview — Fri, 05 Nov 2010 23:54:27 GMT

Just a note - but often after a few more days of headscratching people come back to stats. It's the least intuitive but generally performs the best. Once you get used to it it starts to feel simpler than transaction.

Re: Joining two log files that have a common field

karthicjayarama — Mon, 17 Nov 2014 05:02:59 GMT

Re: Joining two log files that have a common field

loopbit — Thu, 17 Mar 2016 02:57:26 GMT

The results seems to be outer join of SQL. It works perfectly. Thank you~