topic Joining 2 Indexes by JSESSION in Splunk Search

Joining 2 Indexes by JSESSION

skoelpin — Wed, 25 Nov 2015 14:24:46 GMT

I have 2 indexes which both have a common filed (JSESSIONID).. One index has an IndexOutOfBoundsException and another index has the user agent string along with the web request. So I want to join these 2 indexes by their JSESSIONID so I have 1 event which is made up of both the index=main and index=web-access .. Once I have this event I can then find out how many of those IndexOutOfBoundsExceptions were bots or humans by looking at their web request and user agent string.

Index=main has indexoutofboundsexception and the JSESSIONID but does not include the web request or user agent string..
index=web-access has the User Agent string/Web Request and the JSESSIONID but does not include the indexoutofboundsexception

Here's my current search that is not working

index=main AND index=web-access | transaction   JSESSIONID

Re: Joining 2 Indexes by JSESSION

jplumsdaine22 — Wed, 25 Nov 2015 14:34:29 GMT

Try this (change fieldnames to suit)

index=main OR index=web-access | stats values(indexoutofboundsexception) values(useragentstring) by JSESSIONID

Generally if you have a unique ID you shouldn't need transaction. Use stats instead.

Re: Joining 2 Indexes by JSESSION

skoelpin — Wed, 25 Nov 2015 14:47:42 GMT

The user agent string is not an extracted field.. Also I'm not sure how this could work if we're using and AND statement to join the indexes when the indexoutofboundsexception is only present in the index=main.. Wouldn't we always get 'No Results Found' if the value is in one index and we use an AND to join 2 indexes?

Re: Joining 2 Indexes by JSESSION

woodcock — Wed, 25 Nov 2015 14:59:17 GMT

Try this:

 index=main OR index=web-access | stats values(*) AS * BY JSESSIONID

Go on (piping) from there.

Re: Joining 2 Indexes by JSESSION

jplumsdaine22 — Wed, 25 Nov 2015 15:05:43 GMT

Silly me, I missed the AND in your initial search. Assuming the JSESSIONID is present in every event you are interested in, try something more like this.

(index=main OR index=web-access) AND JSESSIONID=* | stats values(*) by JSESSIONID

Using index=x AND index=y will actually return no events. Unless you have events with more than one index field, which I'm not sure is possible? (Not 100% certain there but I wouldn't think it is). Its not actually joining the indices, but telling splunk to give you every event where the index field is BOTH main and web-access. Instead you want to get splunk to give you all the events from the main and web-access indices that have a value for JSESSIONID, then aggregate all the field values for those events based on their JSESSIONID.

Re: Joining 2 Indexes by JSESSION

skoelpin — Wed, 25 Nov 2015 16:29:18 GMT

This does return results, but the events return one index each rather than 2 indexes for each event.

So what I'm looking for is an event to have both index=main and index=web-access as the same event. Both indexes will have the same JSESSIONID

Re: Joining 2 Indexes by JSESSION

jplumsdaine22 — Wed, 25 Nov 2015 16:40:56 GMT

Can you update your question with an example of each event, and perhaps an example of what you want the final table to look like?

Re: Joining 2 Indexes by JSESSION

woodcock — Wed, 25 Nov 2015 16:45:48 GMT

Did you try my entire answer ( index=main OR index=web-access | stats values(*) AS * BY JSESSIONID )? It does exactly what you are saying that you need!