topic join two table in Splunk Search

join two table

splunkpoornima — Fri, 19 Oct 2012 10:50:48 GMT

hi all ,

after using the below search i got one table which has the transactional data as

source="aaa"|transaction TaskName startswith=START endswith=Succeeded|table TaskBP duration

TaskName duration

Task1 1.90

Task2 2.67

Task1 7.55

another table with Taskname and its average duration by using the below search

source="aaa"|transaction TaskName startswith=START endswith=Succeeded|stats avg(duration) by taskname |table taskname avg(duration)

i want to join these two searches so that my table will become as

Taskname duration avg(duration)

Task1 1.90 12.4

task2 2.67 5.9

Task1 7.55 12.4

i think of using the left outer join..plz help in writing the search

Re: join two table

Ayn — Fri, 19 Oct 2012 11:24:44 GMT

How is this different from the other question you had regarding adding an avg(duration) value to your search?

Re: join two table

yannK — Sun, 21 Oct 2012 15:21:06 GMT

use a join on the common field

mysearchA | table field1 field2 | JOIN field1 [ mysearchB | table field1 field3] | table field1 field2 field3

see http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Join

Re: join two table

shayhk — Tue, 17 Dec 2013 14:10:37 GMT

hi,

I have the same problem

mysearchA | table field1 field2
| JOIN field1 [ mysearchB | table field1 field3]
| table field1 field2 field3

what is the mySearchA,mySearchB stands for ?
my data provider(file) is a host named XXX
when i am using a host=XXX in both of "mySearchA,B" expression i am getting an error

help needed
Thanks shay

Re: join two table

somesoni2 — Tue, 17 Dec 2013 14:35:14 GMT

try "source=XXX"

Re: join two table

yannK — Tue, 17 Dec 2013 17:44:44 GMT

You want that the searchA and searchB return a single line per field1, otherwise the join between the 2 lists will be wrong.

An example with a join between a list of users and the logins per server can be :
index=users username=* email=* | stats values(email) AS email by username | JOIN username [ search index=servers login username=* | stats values(host) AS server_login_list earliest(_time) AS recent_login earliest(host) AS recent_server by username ] | table username email server_login_list recent_login recent_server