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.
Index=HTTPDMZ
field1=ipadd - Source IP Address
field2=sessionid - Session ID
field3=url - URL Link
index=APPDMZ
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.
join has worked, below is the query.
index="A"
|join type=inner inc_number [search index= "B"]
|dedup inc_number
|table inc_number,SLA
@harishbenne2,
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
Thanks..
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.
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 correlation_field=coalesce(session,sessionid) | stats values(ipadd) AS ipadd values(user) AS user values(url) AS url BY correlation_field
Tried it in two ways, still only the user
data is being fetched. ipadd
field is still blank.