Splunk Search

## How to find the max value based on the rows?

Builder

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

Thanks

Tags (3)
1 Solution
Builder

| 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 | appendcols [ | 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*| stats max(shiftA) AS Total ,max(shiftB) as ttt ,max(shiftC) as cc | transpose 10 |rename "row 1" as "All"]

Builder

| 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 | appendcols [ | 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*| stats max(shiftA) AS Total ,max(shiftB) as ttt ,max(shiftC) as cc | transpose 10 |rename "row 1" as "All"]

Motivator

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

This is what the user has. How do we add a max field to it?
| 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*

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? SplunkTrust

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>>)]
``````
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Builder

Builder

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

| 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* SplunkTrust

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*
``````

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 *
| 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"
``````
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Super Champion

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