topic Re: Why is my stats search on 2 indexes not returning fields from one index? in Splunk Search

Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 09:54:14 GMT

Hi all,

I have 2 indexes:
index="abc" with fields uri, a, b, c
and
index="xyz" with fields url, x, y, z

Now the fields url and uri have the same values in both indexes, but this is just a change in the field name.

I want to search both the indexes for all fields a, b, c, x, y, z by url

index="abc" or index="xyz" | eval url=uri | stats values(a),values(b), values(x), values(y) by url

Problem is it is only showing the fields a, b and not x, y in stats results.

any help !!

Re: Why is my stats search on 2 indexes not returning fields from one index?

jeffland — Wed, 22 Apr 2015 09:59:51 GMT

You could try and see what your results look like before the stats command.
What exactly are you trying to achieve with the eval? If you want every event to have a url field, you might want to use coalesce to keep the data that is already in url for those events which don't have uri. If you want to see events where url=uri, you should use where.

Re: Why is my stats search on 2 indexes not returning fields from one index?

gyslainlatsa — Wed, 22 Apr 2015 10:28:06 GMT

hi,
try like this

index="abc" or index="xyz" |eval new_field=coalesce(url,uri) |stats values(a),values(b), values(x), values(y) by new_field

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 10:39:23 GMT

I have url field in index="abc" and uri field in index="xyz" but their values are same , so i am searching for events in both indexes with url field and want to collect a,b,x,y,url in stats.

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 10:49:25 GMT

where will not work.. url and uri are different fields with same values across both indexes.

Re: Why is my stats search on 2 indexes not returning fields from one index?

juvetm — Wed, 22 Apr 2015 10:54:01 GMT

have you try to work with table commad

Re: Why is my stats search on 2 indexes not returning fields from one index?

gyslainlatsa — Wed, 22 Apr 2015 10:58:46 GMT

i proposed the new answers, try and let me know
see above

Re: Why is my stats search on 2 indexes not returning fields from one index?

jeffland — Wed, 22 Apr 2015 11:13:25 GMT

If you really want to use stats, the suggestion from gyslainlatsa is pretty close to what you should do. But maybe you want this:

index="abc" or index="xyz" |eval url=coalesce(url,uri) |table a, b, c, x, y, z, url

Re: Why is my stats search on 2 indexes not returning fields from one index?

MuS — Wed, 22 Apr 2015 11:32:58 GMT

Duplicate question from this one: http://answers.splunk.com/answers/229345/combine-results-from-2-searches-with-a-common-fiel.html

and btw the same answers were provided in here 😉

To me, this sounds like there is either no url field or no y & x field ......

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 11:37:25 GMT

no table command wnt work.

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 11:39:45 GMT

MuS, there is all these fields

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 11:40:18 GMT

it is giving field from one index only

Re: Why is my stats search on 2 indexes not returning fields from one index?

MuS — Wed, 22 Apr 2015 11:44:23 GMT

can you provide two of those events in question? Please remove any sensitive data before!

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 11:45:19 GMT

i got it working with join but it too slow

with simple stats

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

this is only returning values of 'a'. column with values 'b' is coming empty.

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 11:53:55 GMT

simple table command will not work

Re: Why is my stats search on 2 indexes not returning fields from one index?

jeffland — Wed, 22 Apr 2015 11:54:47 GMT

There is no "b" in your join search, is that it?

Re: Why is my stats search on 2 indexes not returning fields from one index?

fdi01 — Wed, 22 Apr 2015 12:22:52 GMT

try like this:

index="abc" | join url [ search index="xyz" | rename uri as url |stats values(*) as * by url ] | stats values(*)  as  *  by url

index=abc OR index=xyz| rename uri as url  | stats values(*)  as list_of_* by url

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 12:38:13 GMT

that is a typo, it is equivalent to s only.

Re: Why is my stats search on 2 indexes not returning fields from one index?

jeffland — Mon, 28 Sep 2020 19:39:39 GMT

Well, if these two searches both work:

index=abc | stats values(a) values(b) by url

and

index=xyz | stats values(x) values(y) by uri

then this search has to do it:

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

Of course, if the first search only returns results for two urls (url_1 and url_2) and the second also for two (url_1 and url_3), then the third search will give you three results: one with values for a, b, x and y for url_1, one with a and b for url_2 and one with x and y for url_3. And if either a or b is missing in one of the original results, it won't be in the third search either.

Re: Why is my stats search on 2 indexes not returning fields from one index?

lohit — Wed, 22 Apr 2015 13:10:04 GMT

exactly my problem, for the third search as mentioned i just want the first result and in that only one index fields are coming.

Individual searches are working fine over respective indexes.