Splunk Search

Merge two indexes (ids with proxy) for more alert depth correlation

JSkier
Communicator

I have two indexes for ids (suricata) and proxy (Cisco WSA), I'd like to correlate when splunk finds an IDS alert and pull info from the proxy logs which include the username and internal IP.

Looking over previous answers, this is what I came up with to start (just a base, my alert syntax is complex but I know what I need for that component). I was hoping for a singular event, and being able to format a table for the splunk e-mail alert. However, when I do transaction, the events stay with their respective indexes. So, below would show one of the two tabled fields and one always blank.

index="ironport" OR index="ids" sourcetype="cisco:wsa:squid" OR sourcetype=suri| rename http.hostname AS cs_url_host | transaction cs_url_host maxpause=30s | table cs_url_host,cs_user

The next step is the time issue. Proxy logs are behind x minutes, whereas IDS is almost immediate. I'd need to wait a few minutes before joining the indexes.

I've looked at join, but I haven't figured out how to take the matching hostname IDS alert to the hostname of the proxy logs. Join doesn't seem to work very well either (often giving me no results). Haven't gotten append to work, but open to it.

0 Karma
1 Solution

jkat54
SplunkTrust
SplunkTrust

Try this:

 index="ironport" sourcetype="suri" | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ids" sourcetype="cisco:wsa:squid" | table cs_url_host cs_user] | table cs_url_host, cs_user

It doesnt handle your time issue but it should at least show both columns being populated.

The transaction command followed by the table command you have wont work because transaction is "destroying" the field names or KvPs when it does its thing. There's not a field named cs_user after transaction. Instead you're left with large events and the one field you ran the transaction on.

To see only events that happened in last x amount of time, etc in a specific index... use earliest like below:

 index="ironport" sourcetype="suri" earliest=-2h | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ids" sourcetype="cisco:wsa:squid" earliest=-1h | table cs_url_host cs_user] | table cs_url_host, cs_user

The above would only grab events that were timestamped within the last 2 hours from ironport logs and join with only events from the last hour in your ids logs. There also exists _index_earliest if you're less concerned with timestamp of the events and more concerned with the actual time it arrived at the splunk indexer.

Finally, you can add usetime=true to your join command if you want to preserve your time picker selection.

View solution in original post

jkat54
SplunkTrust
SplunkTrust

Try this:

 index="ironport" sourcetype="suri" | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ids" sourcetype="cisco:wsa:squid" | table cs_url_host cs_user] | table cs_url_host, cs_user

It doesnt handle your time issue but it should at least show both columns being populated.

The transaction command followed by the table command you have wont work because transaction is "destroying" the field names or KvPs when it does its thing. There's not a field named cs_user after transaction. Instead you're left with large events and the one field you ran the transaction on.

To see only events that happened in last x amount of time, etc in a specific index... use earliest like below:

 index="ironport" sourcetype="suri" earliest=-2h | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ids" sourcetype="cisco:wsa:squid" earliest=-1h | table cs_url_host cs_user] | table cs_url_host, cs_user

The above would only grab events that were timestamped within the last 2 hours from ironport logs and join with only events from the last hour in your ids logs. There also exists _index_earliest if you're less concerned with timestamp of the events and more concerned with the actual time it arrived at the splunk indexer.

Finally, you can add usetime=true to your join command if you want to preserve your time picker selection.

JSkier
Communicator

It works. Sorry for the confusing syntax, my original post was not well conveyed. The correct syntax (taken from your answer) would be:

index="ids" sourcetype="suri" | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ironport" sourcetype="cisco:wsa:squid"| table cs_url_host cs_user] | table cs_url_host, cs_user

Going on to the time issue, I thought about looking for pause during search, but I should just set the alert to look later in time to address that part. Thanks!

0 Karma

JSkier
Communicator

Plugging it into the alert, the user name isn't mapping right. Which is very odd, there is only one record in ironport that matches. Looking into it more, but this feels like the right direction to go.

0 Karma

jkat54
SplunkTrust
SplunkTrust

The join command is going to join using the 1st occurrence of the field it can find in the 2nd index.

So if you had userA log in now, and userB logged in a while back, but the cs_url_host was the same for both... join is going to take the userA event and ignore subsequent matches. UNLESS you specify a max matches option in the join command. To get around this and force join to return older events, etc.... you have to use stats in conjuction with latest(field name) or even first(field name). So check out stats first, last, latest, etc. see the examples below too.

Something like this:

index="ids" sourcetype="suri" | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ironport" sourcetype="cisco:wsa:squid"| stats first(cs_user) AS cs_user by cs_url_host ] | table cs_url_host, cs_user

Or this:

index="ids" sourcetype="suri" | rename http.hostname AS cs_url_host | join cs_url_host [ search index="ironport" sourcetype="cisco:wsa:squid"| stats latest(cs_user) AS cs_user by cs_url_host ] | table cs_url_host, cs_user

Again you may have to change the syntax to meet your needs but you strike me as being more than capable!

0 Karma

jkat54
SplunkTrust
SplunkTrust

Hey, you're very welcome. Also i gave you some more stuff to help you with the time issues in an edit to my answer above. Thanks for marking as the answer and upvoting!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...