I have 2 indexes with a common field that I extracted (The JSession ID)
So I want to join index=main
and index=access
by 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]
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"
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"
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
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"
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
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.