Splunk Search

How to find the max value based on the rows?

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

1 Solution

This is my answer...
| 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"]

View solution in original post

0 Karma

This is my answer...
| 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"]

View solution in original post

0 Karma

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*
| transpose header_field=Date

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?

0 Karma

SplunkTrust
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!!!"

what about this FIELD ..?

0 Karma

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*
| transpose header_field=Date

0 Karma

SplunkTrust
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*
| 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"
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

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*
| transpose header_field=Date
 | appendpipe
     [eventstats max(*) as * |dedup column]
0 Karma

SplunkTrust
SplunkTrust

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.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma