topic Re: How to find the max value based on the rows? in Splunk Search

How to find the max value based on the rows?

harishalipaka — Tue, 05 Sep 2017 09:32:21 GMT

Hi all,
I want max value by row wise not max (field name)

**Date**           **shiftA**     **shiftB**     **shiftC**    **Max**
12/08                     102                   10                     200               200
25/08                     240                   102                   52                  240

I want like this. Please help for this. I want it urgent.
Thanks

Re: How to find the max value based on the rows?

niketn — Tue, 05 Sep 2017 09:50:14 GMT

Hi, you would need to use foreach command to iterate through your fields in every row to compute Max. Following is the run anywhere search which mocks the data in this example and applies foreach. Pipes till | table Date shift* generate the mock data. You need the two pipes after that.

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
    [| makeresults
     | eval  Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| table Date shift*
| eval Max=0
| foreach shift*
    [eval Max=case(Max>=<<FIELD>>,Max,true(),<<FIELD>>)]

Re: How to find the max value based on the rows?

harishalipaka — Tue, 05 Sep 2017 09:54:50 GMT

what about this FIELD ..?

Re: How to find the max value based on the rows?

harishalipaka — Tue, 05 Sep 2017 10:01:30 GMT

thanks for your replay sorry i want like this ..below is my query (dates not unique chaged based on query)

Re: How to find the max value based on the rows?

niketn — Tue, 05 Sep 2017 11:01:46 GMT

Would it be possible for you to give us your existing query? What is the final transfoming command you have used to build your table?
I am converting answer to comment. Since this is not resolved yet and we would require more details from your end. You can perform a transpose on above data so that above answer works for you. However, I feel it is better if we handled it directly.

Re: How to find the max value based on the rows?

cmerriman — Tue, 05 Sep 2017 11:57:50 GMT

with your makeresults, if you're doing a transpose before trying to calculate the max, you can try this:

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append 
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date
 | appendpipe
     [eventstats max(*) as * |dedup column]

Re: How to find the max value based on the rows?

niketn — Tue, 05 Sep 2017 12:22:21 GMT

As per updated info, following query generates Data in required format.

| makeresults
| eval Date="12/08", shiftA=102, shiftB=10, shiftC=200
| append 
[| makeresults
| eval Date="25/08", shiftA=240, shiftB=102, shiftC=52]
| append 
[| makeresults
| eval Date="15/08", shiftA=158, shiftB=15, shiftC=178]
| table Date shift*
| transpose header_field=Date

Pipe the following to your transpose to generate required stats:

| foreach */*
     [eval <<FIELD>>=case(len('<<FIELD>>')=3,'<<FIELD>>',len('<<FIELD>>')=2,"0".'<<FIELD>>',len('<<FIELD>>')=1,"00".'<<FIELD>>')]
| foreach */*
     [eval key_<<FIELD>>='<<FIELD>>'."-".Shift]
| fields key_*
| eventstats max(*) as *
| head 1
| transpose column_name="Date"
| eval Date=replace(Date,"key_","")
| eval "row 1"=split('row 1',"-")
| eval Value=mvindex('row 1',0)
| eval Shift=mvindex('row 1',1)
| fields - "row 1"

Re: How to find the max value based on the rows?

harishalipaka — Thu, 07 Sep 2017 08:08:32 GMT

Re: How to find the max value based on the rows?

HattrickNZ — Mon, 23 Apr 2018 23:02:39 GMT

How do you actually get the max column other than typing all that out after the below?

Or in other words is there a way to dynamically get the max without having to specify the field names? Or what do I have to add here, dynamically, to get the max?