topic Re: smallest value in a multi value field splunk in Splunk Search

smallest value in a multi value field splunk

nawazns5038 — Thu, 25 Jul 2019 22:54:27 GMT

Hi,

Does anybody know how to pull the smallest or the largest value in a multi value field ?

| makeresults | eval h="1234,1" | makemv delim="," h

I want the output as the lowest of the values in the multi value field . that is "1"

Thanks

Re: smallest value in a multi value field splunk

niketn — Thu, 25 Jul 2019 23:11:46 GMT

[UPDATED ANSWER]
Since min() on multi-valued field is applying lexicographical order (treating the numbers as string).
One approach would be to pad zeros to the values using rex (I have added 10 zeros to example below, you can add/remove as per your needs). Then apply min(). Finally, using ltrim() remove padded zeros from left.

Please try out and confirm.

| makeresults 
| eval h="10,9,70,100" 
| makemv delim="," h 
| rex mode=sed field=h "s/(\d+)/0000000000\1/"
| eval smallest=trim(min(h),"0")

@nawazns5038 just pipe | eval smallest=min(h) to your existing search.

| makeresults 
| eval h="1234,1" 
| makemv delim="," h
| eval smallest=min(h)

Re: smallest value in a multi value field splunk

nawazns5038 — Fri, 26 Jul 2019 00:10:56 GMT

Hi @niketnilay , It does not work , please look into the following example and run the below query . I think it sorts lexicographically

| makeresults 
 | eval h="10, 9, 70, 100 " 
 | makemv delim="," h
 | eval smallest=max(h)

The query result would be 9 but not 100. The min(h) would show as 10 instead of 9.

Re: smallest value in a multi value field splunk

vnravikumar — Fri, 26 Jul 2019 01:35:52 GMT

Try this, min and max in multi-value

| makeresults 
 | eval test="10,5,6,1" 
 | makemv delim="," test 
 | stats min(test) as min max(test) as max

Re: smallest value in a multi value field splunk

niketn — Fri, 26 Jul 2019 02:46:36 GMT

@nawazns5038 what is your existing query which gives you multivalue field? Is it because of stats or directly from your data or multi-valued field extraction like rex.

Re: smallest value in a multi value field splunk

nawazns5038 — Mon, 29 Jul 2019 19:12:04 GMT

Does not work,

Try this

| makeresults 
  | eval test="10, 9, 70, 100 " 
  | makemv delim="," test 
  | stats min(test) as min max(test) as max

Re: smallest value in a multi value field splunk

nawazns5038 — Mon, 29 Jul 2019 20:32:21 GMT

@niketnilay , the values are coming from a lookup , so for one field value in splunk there are multiple matches and the matches come in a multivalue value after we do the lookup command , I want the lowest or the highest of the multivalue field values.

Re: smallest value in a multi value field splunk

jacobpevans — Mon, 29 Jul 2019 20:45:07 GMT

This works for me to treat the results as numbers:

| makeresults 
| eval test="10, 9, 70, 100" 
| makemv delim="," test

| rex field=test "(?<test_nums>\d+)"
| stats min(test_nums) as min
        max(test_nums) as max

Re: smallest value in a multi value field splunk

vnravikumar — Mon, 29 Jul 2019 22:38:19 GMT

Try this,

| makeresults 
| eval test="10, 9, 70, 100 " 
| makemv delim="," test 
| eval test= trim(test) 
| stats min(test) as min max(test) as max

Re: smallest value in a multi value field splunk

woodcock — Tue, 30 Jul 2019 06:01:19 GMT

Like this:

... | streamstats count AS _serial
| stats min(h) AS min_h max(h) AS max_h BY _serial

You may have to add makemv delim="," h to make h truly mutlivalued.

Re: smallest value in a multi value field splunk

chinmoya — Tue, 30 Jul 2019 06:18:57 GMT

I think its the white space that's creating an issue.
Try below

Re: smallest value in a multi value field splunk

niketn — Tue, 30 Jul 2019 16:27:05 GMT

@nawazns5038 have you tried the updated query?

<yourCurrentSearchReturningMultiValueField_h>
 | rex mode=sed field=h "s/(\d+)/0000000000\1/"
 | eval smallest=trim(min(h),"0")