Can you limit events to the max numerical value of a given field by the unique value of another field?

[andrewgbennett3]

I am trying to limit my search results to events that contain the highest numerical value of a given field (vulnerability score) for each unique value of another field (ip address).

As events are created per vulnerability, there are multiple events with the same ip address but varying vulnerability scores. The goal here is to identify individual events for a given ip address / vulnerability score pair, and then populate a table containing other important field values unique to those events.

Sample Events:
IP: 1.2.3.4 Vulnerability Score: 100
IP: 1.2.3.4 Vulnerability Score: 200
IP: 1.2.3.4 Vulnerability Score: 300

IP: 1.2.3.5 Vulnerability Score: 100
IP: 1.2.3.5 Vulnerability Score: 200
IP: 1.2.3.5 Vulnerability Score: 300

I would like to limit my search to the following events:

IP: 1.2.3.5 Vulnerability Score: 300
IP: 1.2.3.4 Vulnerability Score: 300

How would I achieve this result?

[elliotproebstel · Accepted solution]

I'd use eventstats to apply the max() by IP address.
Here's some documentation about eventstats:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Eventstats

So let's say you have a base search that returns those events above, and you have already extracted the fields ip_address and vulnerability_score. Then here's how I'd do it:
base search | eventstats max(vulnerability_score) AS max_score BY ip_address | where vulnerability_score=max_score | fields - max_score

[andrewgbennett3]

This worked perfectly for my needs. Thanks for the quick and detailed response!

[elliotproebstel]

Glad to help!