Splunk Search

How to find the max value based on the rows?

harishalipaka
Motivator

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

Thanks
Harish
1 Solution

harishalipaka
Motivator

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"]

Thanks
Harish

View solution in original post

0 Karma

harishalipaka
Motivator

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"]

Thanks
Harish
0 Karma

HattrickNZ
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

niketn
Legend

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

harishalipaka
Motivator

what about this FIELD ..?

Thanks
Harish
0 Karma

harishalipaka
Motivator

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

Thanks
Harish
0 Karma

niketn
Legend

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

cmerriman
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

niketn
Legend

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
Get Updates on the Splunk Community!

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...

Tech Talk | Elevating Digital Service Excellence: The Synergy of Splunk RUM & APM

Elevating Digital Service Excellence: The Synergy of Real User Monitoring and Application Performance ...