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?
@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.
@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
@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.