topic Re: How do you correlate data from multiple indexes? in Splunk Enterprise Security

How do you correlate data from multiple indexes?

harishbenne2 — Fri, 31 Aug 2018 11:40:49 GMT

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:

ipadd
sessionid
user
url

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.

Re: How do you correlate data from multiple indexes?

deking_splunk — Fri, 31 Aug 2018 12:09:18 GMT

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

Re: How do you correlate data from multiple indexes?

harishbenne2 — Fri, 31 Aug 2018 13:00:22 GMT

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.

Re: How do you correlate data from multiple indexes?

harishbenne2 — Fri, 31 Aug 2018 13:02:14 GMT

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.

Re: How do you correlate data from multiple indexes?

deking_splunk — Tue, 29 Sep 2020 21:06:00 GMT

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

Re: How do you correlate data from multiple indexes?

harishbenne2 — Fri, 31 Aug 2018 14:50:10 GMT

Tried it in two ways, still only the user data is being fetched. ipadd field is still blank.

Re: How do you correlate data from multiple indexes?

Shan — Sat, 01 Sep 2018 07:17:43 GMT

@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..

Re: How do you correlate data from multiple indexes?

abhijitsaoji — Thu, 23 May 2019 11:32:31 GMT

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.

Re: How do you correlate data from multiple indexes?

abhijitsaoji — Wed, 30 Sep 2020 00:39:55 GMT

join has worked, below is the query.
index="A"
|join type=inner inc_number [search index= "B"]
|dedup inc_number
|table inc_number,SLA