topic Re: Help Joining Indexes in Splunk Search

Help Joining Indexes

skoelpin — Mon, 13 Jul 2015 16:06:14 GMT

I have 2 indexes with a common field that I extracted (The JSession ID)

So I want to join index=mainand index=accessby using the field RTG_JSession

index=access has web requests with the Jsession ID
index=main has the IP address with the Jsession ID

So I want to have a table which shows the:

   IP Address | Web Request | JSession ID

Here's my current search which could use some tweeking.. The RTG_IP field was extratced to grab the IP Address

index=main | Join RTG_JSession [search index=access | stats count by RTG_IP]

Re: Help Joining Indexes

woodcock — Mon, 13 Jul 2015 16:17:28 GMT

Try to avoid join ( subsearches ) if you can. Will this work?

 index=main OR index=access | stats values(*) AS * BY RTG_JSession | stats count AS "Web Requests" values(RTG_JSession) AS "JSession IDs" dc(RTG_JSession) AS "JSession ID count" BY RTG_IP | rename RTG_IP AS "IP Address"

Re: Help Joining Indexes

skoelpin — Tue, 29 Sep 2020 06:40:33 GMT

This was very close to what I needed. It seems to be that the 'Web Requests' field is counting the number of JSession's. I need to have 3 columns showing IP Address | Web Requests | JSession ID.. The web requests are the HTTP calls we get, so "GET /browse/financeInquiry.jsp?provider=GE&_requestid=32423423423 HTTP/1.1" would be the Web Request. So I went ahead and extracted another field called RTG_WebReq which grabs these values. I also expect an IP Address to have many Jsessions and Web Requests

Here's an example of what I'm looking for

172.20.197.82 | GET /browse/financeInquiry.jsp?provider=GE&_requestid=43534534 HTTP/1.1 | 99CA349F0BB69E402A678345AC007E2Z

Re: Help Joining Indexes

woodcock — Mon, 13 Jul 2015 18:28:04 GMT

OK, then, this should do it:

 index=main OR index=access | stats values(*) AS * BY RTG_JSession | stats count(RTG_WebReq) AS "Web Requests" values(RTG_JSession) AS "JSession IDs" dc(RTG_JSession) AS "JSession ID count" BY RTG_IP | rename RTG_IP AS "IP Address"

Re: Help Joining Indexes

skoelpin — Mon, 13 Jul 2015 23:33:06 GMT

I took a different approach and this does the trick. You gave me the idea to abandon the join which I now see why. Below is the search I used for anyone needing help in the future. Thanks for the help!

index=access OR Index=main | transaction RTG_JSession | transaction RTG_JSession RTG_IP RTG_WebReq | table RTG_IP RTG_JSession RTG_WebReq

Re: Help Joining Indexes

woodcock — Mon, 13 Jul 2015 23:36:02 GMT

Well, transaction has other problems and it's performance can easily be worse than join but if your times are close together and you are using only 1 field that all events contain, it should be fine (e.g. your first transaction) but I would be concerned about your second one on large data sets.