topic Re: Joining two logs by two common fields and output the values as a table in Splunk Search

Joining two logs by two common fields and output the values as a table

rajadatta — Thu, 11 Jun 2015 01:20:42 GMT

Hi - I would like to join two logs and get specific result as table. I want to join by two common fields. Been working on getting this all day and need help.

so I have log 1 as below and want these values in a table

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-device-category.log" | table DateStamp UserId
mailingid ttype DeviceInfo

I have log 2
index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-success.log"

The common fields I want to join by mailingid,UserId.

In this second log, I want to only return the field EMAIL

In the end I need including the first logs output and second logs output in a table

So looking for this

DateStamp UserId EMAIL mailingid ttype DeviceInfo

Thanks for any help, if more info is needed, I will gladly input them in this forum

Re: Joining two logs by two common fields and output the values as a table

woodcock — Thu, 11 Jun 2015 03:08:44 GMT

Try this:

(index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-device-category.log") OR (index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-success.log") | stats values(*) AS * BY mailingid,UserId | table DateStamp UserId EMAIL mailingid ttype DeviceInfo

Re: Joining two logs by two common fields and output the values as a table

rajadatta — Fri, 12 Jun 2015 17:20:12 GMT

Thank you for your help.

I was curious what to do when the second search does not have a common field to join on and ommitting those results.

So I specifically want to output when there is an actual join with both fields for the two searches.

Again appreciate the help.