topic Re: Find nearest value in numeric multivalue field to other numeric field in Splunk Search

Find nearest value in numeric multivalue field to other numeric field

sematag — Wed, 30 Sep 2020 00:45:30 GMT

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.

Re: Find nearest value in numeric multivalue field to other numeric field

dtburrows3 — Mon, 08 Jan 2024 22:32:59 GMT

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.

Re: Find nearest value in numeric multivalue field to other numeric field

bowesmana — Mon, 08 Jan 2024 23:15:03 GMT

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