How do you return only 1 result from a lookup?

[seomisp]

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?

[valiquet]

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

[FrankVl]

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.