Combine results from 2 searches with a common field

[lohit]

Hi all ,

i am trying to merge my results from 2 searches running over different indexes that have a common field "url".

index ="abc"| join url [search index="xyz" | eval url=uri | fields + url, a,b,c ]

i have used the eval command because in index "xyz" field name is uri but the values are same. What i am looking of is

1. To merge these 2 results and output uncommon fields from index "abc" and index "xyz along with "url".

Please help

[MuS]

Hi lohit,

you should be able to use something like this:

index=abc OR index=xyz | eval url=coalesce(url, uri) | stats count by url, a, b, c

The eval coalesce will take care that you have a url field for each event fron any index.

Also take a look at this answer for more details http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi...

Hope this helps ...

cheers, MuS

[lohit]

Hi MuS ,

i have match events containing url from index "xyz" which have same url in index "abc" and not from either of them.

[MuS]

Okay, then you should be able to simply use a index=abc OR index=xyz | stats by url if you have this field in both indexes....but you wrote in your question that you have not, that's why you used the eval.....

[lohit]

ok let me explain again.

in index "abc" i have field url, x,y
in index "xyz" i have field uri,a,b
now this uri and url are same fields across indexes. my concern is that how can i get something like this without join in table

col1 col2 col3 col4 col5
url(both index) x y a b

[MuS]

Understood, try my first search and to add a b x y use streamstats like in the answer I provided the link 😉 Or do it only by using streamstats .... like

 index=abc OR index=xyz | streamstats values(a) AS a, values(b) AS b .... 

[lohit]

Ok i have written the below query

index="abc" |eval agt=cs_us| join url [ search index="xyz" | eval url=uri |stats values(a) as a,values(s) as s, by url | fields a,s,url] | stats values(agt) values(a),values(s) by url

how can i better write this query ?

[MuS]

try this:

index="abc" OR index=xyz |eval agt=cs_us | eval url=uri | stats values(a) AS a,values(s) AS s, values(agt) AS atg by url 

[lohit]

it is not returning any results !!

[MuS]

Since I don't have any of your events, I cannot test it. But I did show your the direction to go. Read the linked answer and use the provided searches to try it yourself. Afterwards adopt the searches to your real world events.

[lohit]

ok thanks MuS

[lohit]

MuS, if i run your last suggestion,it is
giving fields from only one index.

[MuS]

I just did a copy paste of your provided search where you asked how can i better write this query ? yesterday

[lohit]

using join , my query is very slow since both the index is very noisy. Any help from your side ?

[lohit]

Yes it is quite strange. my join query is working fine but with stats only it is returning fields from one index.

[lohit]

index=abc OR index=xyz| eval url=uri | stats values(a),values(x) by url

values(a) is from index=abc and is coming
values(x) is from index=xyz and is not coming.

[lohit]

Also in the output i have to display fields of both indexes. i have done it using join but it very slow. any faster way of doing it.

[lohit]

solved it
myself