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

[lohit]

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 !!

[gyslainlatsa]

hi lohit,
try do this

create a new_index to collect all the data of the two indexes and then execute your request on this new_index as follow.

index="abc" OR index="xyz" |table a b c uri x y  z url |collect index=new_index

index=new_index  |stats values(a), values(b), values(x),values(y) by url

OR

index="abc" OR index="xyz" |table a b uri x y url |collect index=new_index

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

try and let me know.

[stephane_cyrill]

Hi give a try to this:

index = "abc" or index = "xyz" | eval url_uri=mvzip(url,uri) |mvexpand url_uri | stats values ( url_uri) by url

[chimell]

Hi lohit
Try this search

     index="abc"  |rename uri as url |table a b c url|appendcols[search index="xyz"  |table x y z url ]  | stats values(a),values(b), values(x), values(y) by url

[lohit]

values x, y are not coming with this.

[fdi01]

try like this:

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

or

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

[MuS]

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 ......

[lohit]

MuS, there is all these fields

[MuS]

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

[gyslainlatsa]

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

[lohit]

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

[gyslainlatsa]

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

[lohit]

it is giving field from one index only

[lohit]

i got it working with join but it too slow

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

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.

[jeffland]

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

[lohit]

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

[jeffland]

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.

[lohit]

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.

[lohit]

another problem which i have figured out is that logs in both the index have some time lag and i am running the search for last 60 minutes, so the situation is like this

event L happens in index="abc" at time 'q' and having fields a,b,url
event M happens in index="xyz" at time 'p' and having fields x,y,uri

So there might be case when url from index="abc" donot exist in index="xyz" for last 60 minuts but if i increase it to say 24 hours then the value is showing .

Note url and uri have same values but different field name but their timestamp is different.

now what i want is a search which can give output with columns like

url(both index) a b x y

[jeffland]

I (and I believe everyone else here) understand what you want. The search we proposed is exactly what you need, but it seems there are other problems, the time difference for example. Those are not things we can help you with if you don't post your data (you can anonymize it).

[jeffland]

Do the individual searches deliver values for all fields, i.e. do the results from index abc always have fields a and b and have exactly the same url as those from index xyz? If the answer is yes, then the search has to work. As MuS requested, can you post an event from index abc and one from index xyz?