Splunk Search

Find nearest value in numeric multivalue field to other numeric field

sematag
New Member

I have events with a numeric field "Amount" and a field "User". In a KV Store collection I keep the Amount history values for a each User (Amount_Hist). With a lookup I can get the Amount_Hist for a user in a numeric multivalue field.

Given a new event for a user and the the value of Amount, I need to get the nearest value from the Amount_Hist (where Amount_Hist is a multivalue field and Amount a single value field).

I cant use mvexpand to do it because Amount_Hist is very large and mvexpand produce exesive memory usage when is applied for multiple events.

Thanks a lot for any sugerence.

0 Karma

dtburrows3
Builder

Not sure if you still need this answered but figure I'd give it a shot in case anybody else has a similar problem they need to solve.

I think something like this would do what you are looking for.

<base_search>
    ``` lookup historical values for each user (potentially mv field) ```
    | lookup <lookup_name> user OUTPUT Amount_Hist
    ``` loop through Amount_Hist values and finding the diff of each compared to the users current value, then take the minimum value and assign it to it's own field "Amount_Hist_min_diff" ```
    | eval
        Amount_Hist_min_diff=min(
            case(
                mvcount(Amount_Hist)==1, abs('Amount_Hist'-'Amount'),
                mvcount(Amount_Hist)>1, mvmap(Amount_Hist, abs('Amount_Hist'-'Amount'))
                )
            ),
        ``` loop back through historical values and only return the values whos diff is equal to the minimum diff value assigned previously ```
        Closest_Amount_Hist_value=mvdedup(
            case(
                mvcount(Amount_Hist)==1, if(abs('Amount_Hist'-'Amount')=='Amount_Hist_min_diff', 'Amount_Hist', null()),
                mvcount(Amount_Hist)>1, mvmap(Amount_Hist, if(abs('Amount_Hist'-'Amount')=='Amount_Hist_min_diff', 'Amount_Hist', null()))
                )
            )

 You can see that the field "Closest_Amount_Hist_value" holds the value closest the the user's current value, this can potentially hold multiple values if they are are equidistant from the current value. As show below.

dtburrows3_1-1704753142009.png

 

 

0 Karma

bowesmana
SplunkTrust
SplunkTrust

An alternative which will just return a single 'closest' value using foreach is

your_search_and_lookup
| eval diff=max(Amount_Hist)
| foreach Amount_Hist mode=multivalue [ eval new_diff=min(diff, abs(Amount-<<ITEM>>)), closest=if(new_diff<diff, <<ITEM>>, closest), diff=new_diff ]
| fields - new_diff

 

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 ...