Splunk Search

How do you return only 1 result from a lookup?

seomisp
Explorer

I'm enriching my search with a match against a lookup table. However, the lookup returns more than 1 result for each match. I want to return just 1 match, depending on a criteria, for example the highest number or such.

Here's my example:

index=index_a ip=10.0.0.1
| lookup ip_lookup_table ipaddr as ip outputnew confidence as c source as s severity as sev _time as l_time
| table ip, c, s, sev, l_time

For example, this will return a table with:

IP           c      s            sev           l_time
10.0.0.1 .   67 .   source_X .   high .        1540238898 
         .   40 .   source_Y .   very-high .   1440238898
         .   80 .   source_Z .   medium .      1530238898

I wanted to return just the line with the max l_time, so that the table would be :

IP           c      s            sev           l_time
10.0.0.1 .   67 .   source_X .   high .        1540238898 

I tried doing for example | eval l_time=max(l_time), but it doesn't affect the full row.

I checked the contents of the lookup table, and it has three different rows concerning the IP 10.0.0.1. Why is the lookup aggregating the results into the columns, instead of returning a row for each match?

0 Karma

valiquet
Contributor

index=index_a ip=10.0.0.1
| lookup ip_lookup_table ipaddr as ip outputnew confidence as c source as s severity as sev _time as l_time
| mvexpand ip
|stats max(l_time) by IP c s sev

0 Karma

FrankVl
Ultra Champion

Unless someone comes up with a smart way to wrestle those multi value fields into shape, you might be best off by using a join in this case, rather than a lookup.

That way, you can do some preprocessing on the table, before joining it to your event data, to ensure it joins the desired rows.

If the criteria for which row to use is always the same, you could also consider making sure the lookup table is sorted accordingly and then configure the lookup definition so that it only returns a single match.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...