Splunk Enterprise Security

How do you correlate data from multiple indexes?

Explorer

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:

  1. ipadd
  2. sessionid
  3. user
  4. 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.

0 Karma

Explorer

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

0 Karma

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

0 Karma

Explorer

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.

0 Karma

Splunk Employee
Splunk Employee

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

Explorer

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.

0 Karma

Explorer

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.

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Explorer

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

0 Karma