How to capture 2nd max value of a field and compare with 1st max value of the same field

[pratyushak]

I have data in json format as following:-
{Run=1 , Average=2.1, Max=3, Min=1.4, Transaction=Sample1}
{Run=1 , Average=2.1, Max=3, Min=1.4,Transaction=Sample2}
{Run=2 , Average=2.3, Max=3.1, Min=1.5,Transaction=Sample1}
{Run=2 , Average=2.3, Max=3.1, Min=1.5,Transaction=Sample2}
{Run=3 , Average=2.6, Max=3.2, Min=1.6,Transaction=Sample1}
{Run=3 , Average=2.6, Max=3.2, Min=1.6,Transaction=Sample2}

I want to compare all the fields with each other for the top 2 Run Values.

The below query is getting the data in the format I exactly need but i'm not able to pass Run Values via query dynamically:-

index=pt sourcetype=_json | search Run=3 |rename Average as "Average1" | rename Maximum as "Max1"| join Transaction type=inner[ search index=pt sourcetype=_json | search Run=2 |rename Average as "Average2" | rename Maximum as "Max2"| table Transaction, Average2] |table Transaction, Average1,Average2,Max1, Max2

Result:-
Transaction Average1 Average2 Max1 Max2
Sample1 2.6 2.3 3.2 3.1
Sample2 2.6 2.3 3.2 3.1

How to capture the second max value for a field like Run and get the desired results?

[renjith_nair]

@pratyushak,

Another approach could be using dedup. The result is based on max Run value. If you want max of any other fields, you can add them into the sortby part of dedup

index=pt sourcetype=_json
| dedup 2 Transaction sortby -Run |streamstats last(*) as *1 by Transaction current=f
| rename Average as Average2,Max as Max2,Min as Min2| where isnotnull(Average1)
| table Transaction Average1, Average2, Max1, Max2

[niketn · Accepted solution]

@pratyushak can you try the following:

index=pt sourcetype=_json
| sort 0 - Transaction Max 
| streamstats count by Transaction 
| search count IN (1,2)
| chart list(Average) as Average list(Min) as Min list(Max) as Max list(Run) as Run by Transaction 
| foreach Average Max
    [ eval "<<FIELD>>1"=mvindex('<<FIELD>>',0),"<<FIELD>>2"=mvindex('<<FIELD>>',1)]
| table Transaction Average1, Average2, Max1, Max2

Following is a run anywhere example based on the sample data provided:

| makeresults 
| eval data="{\"Run\":1,\"Average\":2.1,\"Max\":3,\"Min\":1.4,\"Transaction\":\"Sample1\"};{\"Run\":1,\"Average\":2.1,\"Max\":3,\"Min\":1.4,\"Transaction\":\"Sample2\"};{\"Run\":2,\"Average\":2.3,\"Max\":3.1,\"Min\":1.5,\"Transaction\":\"Sample1\"};{\"Run\":2,\"Average\":2.3,\"Max\":3.1,\"Min\":1.5,\"Transaction\":\"Sample2\"};{\"Run\":3,\"Average\":2.6,\"Max\":3.2,\"Min\":1.6,\"Transaction\":\"Sample1\"};{\"Run\":3,\"Average\":2.6,\"Max\":3.2,\"Min\":1.6,\"Transaction\":\"Sample2\"}" 
| makemv data delim=";" 
| mvexpand data 
| rename data as _raw 
| spath 
| sort 0 - Transaction Max 
| streamstats count by Transaction 
| search count IN (1,2)
| chart list(Average) as Average list(Min) as Min list(Max) as Max list(Run) as Run by Transaction 
| foreach Average Max
    [ eval "<<FIELD>>1"=mvindex('<<FIELD>>',0),"<<FIELD>>2"=mvindex('<<FIELD>>',1)]
| table Transaction Average1, Average2, Max1, Max2

PS: Commands from makeresults till spath are used to generate JSON data as per the question.