I have 2 indexes that have 2 different parts of same data. One index contains http connection details and another contains it's corresponding application data.
field1=ipadd - Source IP Address
field2=sessionid - Session ID
field3=url - URL Link
field1 = Session - Session ID
field2 = url - URL Link
field3 = user - Username
I need to export a comprehensive report with following fields:
I have tried the
coalesce command and also merging 2 field names
(eval correlation_field=case(isnotnull(sessionid), sessionid, isnotnull(Session), Session, 1=1, "unknown") | stats values(url) as url values(user) as user by ipadd correlation_field.
Both haven't worked out for this case. Any other solution will be most helpful.
Can you post your full search ?
On the face of it, I would try a search like;
index=httpdmz OR index=appdmz | stats values(ipadd) AS ipadd values(user) AS user values(url) AS url BY sessionid
This is the entire search is as below:
index=httpdmz OR index=appdmz | (eval correlation_field=case(isnotnull(sessionid), sessionid, isnotnull(Session), Session, 1=1, "unknown") | stats values(url) as url values(user) as user values(ipadd) as ipadd by correlation_field
But it just doesn't work!
If you've worked in SQL,this might ring a bell: we always bind the columns through a where condition like
where httpdmz.sessionid=appdmz.Session through which the engine understands that it needs to map the
sessionid column in
httpdmz table with
Session column in
appdmz table to fetch its relevant data. I am not sure how Splunk or any other system can fetch the relative details without understanding the relation between fields.
Also in addition to that
index=httpdmz OR index=appdmz | stats values(ipadd) AS ipadd values(user) AS user values(url) AS url BY sessionid doesn't work. Because it doesn't understand the correlation between user and ipadd, which is the sessionid/Session.
Ahh OK so the session and sessionid are different field names in the respective indexes?
If thats the case you could use coalesce ;
search | eval correlationfield=coalesce(session,sessionid) | stats values(ipadd) AS ipadd values(user) AS user values(url) AS url BY correlationfield
upto my understanding. your trying to join the
httpdmz.sessionid=appdmz.Session, sessionid of index=httpdmz with Session of index=appdmz. you want to fetch matching values of sessionid=Session and process further right..
Then you need to go for join condition. I have provided the sample example below. You need to replace your index and required field in below query. For getting matching data. Field name should be same in both the index. So only i have rename sessionid as Session
| rename sessionid as Session .
index=httpdmz (replace with ur tetsing index) | rename sessionid as Session | table Session url ipadd | join type=inner Session [ index=appdmz (replace with ur tetsing index) | table Session user ] | table ipadd Session user url
Example to show how join works with sample data ..
| makeresults | eval mytrimexaxis =mvappend("1531981800","1531982400","1531982700","1531983000","1531983600") | eval mysite =mvappend("alpha","beta","game","show","check") | mvexpand mytrimexaxis | rename mytrimexaxis as datajoin | table datajoin mysite | join type=inner datajoin [ | makeresults | eval datajoin="1531981800" ] | table datajoin mysite
Hi, I am trying this command but not working for me. My requirement is I have two indexes, both has ticket numbers(same name) and I have to append SLA field from one of the indexes.
Index=a - has ticket number, SLA
index =b - has ticket number, and my other fierlds
I want to join both of them and get the value of SLA for matching incident numbers.
join has worked, below is the query.
|join type=inner incnumber [search index= "B"]