Jan-1 100 60 87 78 86 545 53 509 56 545 656
Jan2 110 60 87 78 86 545 53 509 56 545 656
Jan-3 111 60 87 78 86 545 53 509 56 545 655
Jan-4 112 60 89 78 86 545 53 509 56 545 656
diff 2 0 2 0 ....
I have to compute "always" the difference between last row and first row ( diff)
How can I achieve this ?
Thanks
@reverse try the following run anywhere example which prepares data similar to your question. from |makeresults
till | fields - data count
| makeresults
| eval data="Jan-1 100 60 87 78 86 545 53 509 56 545 656;Jan-2 110 60 87 78 86 545 53 509 56 545 656;Jan-3 111 60 87 78 86 545 53 509 56 545 655;Jan-4 112 60 89 78 86 545 53 509 56 545 656"
| makemv data delim=";"
| stats count by data
| makemv data delim=" "
| eval date=mvindex(data,0),
field1=mvindex(data,1),
field2=mvindex(data,2),
field3=mvindex(data,3),
field4=mvindex(data,4),
field5=mvindex(data,5),
field6=mvindex(data,6),
field7=mvindex(data,7),
field8=mvindex(data,8),
field9=mvindex(data,9),
field10=mvindex(data,10)
| fields - data count
| fields - date
| stats first(*) as first* last(*) as last*
| foreach first* [| eval diff_<<MATCHSTR>>=first<<MATCHSTR>>-last<<MATCHSTR>>]
| fields diff_*
Then the remaining command calculate difference as per your requirement. Since you have not provided field names I have cooked up all of it as field1, field2 etc.
@reverse try the following run anywhere example which prepares data similar to your question. from |makeresults
till | fields - data count
| makeresults
| eval data="Jan-1 100 60 87 78 86 545 53 509 56 545 656;Jan-2 110 60 87 78 86 545 53 509 56 545 656;Jan-3 111 60 87 78 86 545 53 509 56 545 655;Jan-4 112 60 89 78 86 545 53 509 56 545 656"
| makemv data delim=";"
| stats count by data
| makemv data delim=" "
| eval date=mvindex(data,0),
field1=mvindex(data,1),
field2=mvindex(data,2),
field3=mvindex(data,3),
field4=mvindex(data,4),
field5=mvindex(data,5),
field6=mvindex(data,6),
field7=mvindex(data,7),
field8=mvindex(data,8),
field9=mvindex(data,9),
field10=mvindex(data,10)
| fields - data count
| fields - date
| stats first(*) as first* last(*) as last*
| foreach first* [| eval diff_<<MATCHSTR>>=first<<MATCHSTR>>-last<<MATCHSTR>>]
| fields diff_*
Then the remaining command calculate difference as per your requirement. Since you have not provided field names I have cooked up all of it as field1, field2 etc.
it worked.. thanks! how can i show only that data where diff was maximum... like top 2.. I know it is complex
how can i show only that data where diff was maximum... like top 2..
@reverse try appending the following to your existing search.
| transpose 0 column_name=difference
| sort 0 - "row 1"
| head 2
| transpose header_field=difference
| fields diff_*
results would be dynamic.. first column
@reverse please add more details to your problem. For the data provided what is the output you need?
diff is the output
@reverse the number of rows is it fixed or can it vary? Also once you have the difference do you want to output only the difference?
Rows will vary as per timepicker range .. last 7 days 30 days .. so on .. columns are fixed though