topic How to add fields from subsearch ? Used Join but didnt work ... in Splunk Search

How to add fields from subsearch ? Used Join but didnt work ...

kshanky143 — Thu, 12 Mar 2015 17:39:49 GMT

I have a subsearch which returns a table with 2 columns 'input' and 'Time'. Table from subsearch looks like this.
input ---------------- Time
Movie1 ---------------- 2
Movie2 ---------------- 2
Movie3 ---------------- 2

Now i would like to return input and Time fields to my main search. As shown below, i'm using join to return input and Time, but it doesn't show any results. I would like to know if im doing anything wrong here ? or is there any better approach to get this done.

index=cas OR index=vod (sourcetype=ch AND input=) OR (sourcetype=ac AND id= AND code=) OR (sourcetype=hy AND ruid=)| join ,input, TimeInHours [search sourcetype=ch input=* | chart latest(Status) as stat earliest(_time) as earliest by input| eval Time=round(((now()-earliest)/60)/60) | where match(stat, "Failed") | table input,Time]

Thanks
Sheshank

Re: How to add fields from subsearch ? Used Join but didnt work ...

vasanthmss — Thu, 12 Mar 2015 17:58:43 GMT

Hi,

What's the common field? I can see that common field TimeInHours is not available in the table result.

index=cas OR index=vod (sourcetype=ch AND input=) OR (sourcetype=ac AND id= AND code=) OR (sourcetype=hy AND ruid=)| join ,input, TimeInHours [search sourcetype=ch input=* | chart latest(Status) as stat earliest(_time) as earliest by input| eval Time=round(((now()-earliest)/60)/60) | where match(stat, "Failed") | table input,Time]

Assuming input is the only common field in both the searches,

index=cas OR index=vod (sourcetype=ch AND input=) OR (sourcetype=ac AND id= AND code=) OR (sourcetype=hy AND ruid=)| join input, TimeInHours [search sourcetype=ch input=* | chart latest(Status) as stat earliest(_time) as earliest by input| eval Time=round(((now()-earliest)/60)/60) | where match(stat, "Failed") | table input,Time]

execute the searches and check it manually input has common values,

Search 1: 
     index=cas OR index=vod (sourcetype=ch AND input=) OR (sourcetype=ac AND id= AND code=) OR (sourcetype=hy AND ruid=) | table input

Search 2:
sourcetype=ch input=* | chart latest(Status) as stat earliest(_time) as earliest by input| eval Time=round(((now()-earliest)/60)/60) | where match(stat, "Failed") | table input,Time

Re: How to add fields from subsearch ? Used Join but didnt work ...

kshanky143 — Thu, 12 Mar 2015 18:18:34 GMT

I dont have Time field at all to join. Thats what i have just realized now. Your answer makes more sense now.
Thanks.