Create correlation search from multiple host or device logs based on the Source IP field

[chrishow]

Hi all,

I want to create the correlation search in order to further enhance our current security alert from splunk by correlating the search from multiple host and device logs and then merge the results and show them together. In this case, my first attempted correlation searches would be to merge the result of both firewall logs and web server logs.

Take example like below search query for web server log:

host="web server" Source_IP="IP Address"| bucket span=1m _time | stats values(URL_Action) as URL, values(response) AS http_response count by _time , Source_IP

The above search query will shown the result of the URL web activity and its response associated with the specified source IP per minute.

Now the below is the search query for checkpoint firewall log:

host=checkpoint src="IP Address" | bucket span=1m _time | stats VALUES(orig) AS firewall_type, VALUES(s_port) AS source_port, VALUES(dst) AS destination_ip, VALUES(service) AS service, VALUES(action) AS Action,count by _time, src

The above search query will shown the result of firewall activity such as firewall zone, source port, Destination, destination port, firewall accept or drop and so on associated with the specified source IP per minute.

For this correlation search, I attempt to correlate and merge the result of both of the web server logs and firewall logs based on the source IP field(which is src and Source_IP). I already attempt to try the correlation using eval correlation_field=case(isnotnull) and eval correlation_field=coalesce(). But unfortunately both of this eval command does not produce the output I desired. I also already try other query like stats and chart but to no avail

Please advice on the correlation search query to merge the result of multiple different host and device logs. If can, i would prefer to correlate our firewall alert that detect suspicious source IP that have high count traffic drop and then correlate the result with other device should it found match the source IP attempting other activity in other device. This would further improve our security alert as it not only just shown the firewall result but also result from other device logs.

Let me know if there are anything

[woodcock · Accepted solution]

Try this:

(index="YouShouldAlwaysSpecifyAnIndex" AND sourcetype="AndSourcetypeToo" AND host="web server" AND Source_IP="IP Address")
OR (index="YouShouldAlwaysSpecifyOtherIndex" AND sourcetype="AndOtherSourcetypeToo" AND host="checkpoint" AND src="IP Address")
| eval Source_IP = coalesce(Source_IP, src)
| fields - src
| bucket span=1m _time
| stats values(URL_Action) as URL, values(response) AS http_response count tats values(orig) AS firewall_type, values(s_port) AS source_port, values(dst) AS destination_ip, values(service) AS service, values(action) AS Action BY _time , Source_IP

[chrishow]

Hi,

Thanks for the query suggestion. It worked and it produce the results what I desired. I will try integrate it with our current splunk security alert and see whether it can merge multiple device logs result together.

Can I ask what is the purpose of this command:

| fields - src

From what I see from the output, I see most of the stats values field. I don't see anything for the fields -src query

[woodcock]

Commentary; it is to make extra sure that nobody tries to use src because it has been subsumed into Source_IP. It can be removed and change nothing.