topic Re: Difference between rows of query result in Splunk Search

Difference between rows of query result

reverse — Sat, 18 May 2019 00:35:28 GMT

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

Re: Difference between rows of query result

reverse — Sat, 18 May 2019 02:08:44 GMT

results would be dynamic.. first column

Re: Difference between rows of query result

niketn — Sat, 18 May 2019 14:07:20 GMT

@reverse please add more details to your problem. For the data provided what is the output you need?

Re: Difference between rows of query result

reverse — Sat, 18 May 2019 14:28:51 GMT

diff is the output

Re: Difference between rows of query result

niketn — Sat, 18 May 2019 16:46:08 GMT

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

Re: Difference between rows of query result

reverse — Sat, 18 May 2019 16:48:49 GMT

Rows will vary as per timepicker range .. last 7 days 30 days .. so on .. columns are fixed though

Re: Difference between rows of query result

niketn — Sat, 18 May 2019 16:57:20 GMT

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

Re: Difference between rows of query result

reverse — Sat, 18 May 2019 17:06:19 GMT

it worked.. thanks! how can i show only that data where diff was maximum... like top 2.. I know it is complex

Re: Difference between rows of query result

reverse — Sun, 19 May 2019 03:15:02 GMT

how can i show only that data where diff was maximum... like top 2..

Re: Difference between rows of query result

niketn — Sun, 19 May 2019 17:25:24 GMT

@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_*