Splunk Search
Highlighted

Difference between rows of query result

Contributor
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

Tags (1)
0 Karma
Highlighted

Re: Difference between rows of query result

Contributor

results would be dynamic.. first column

0 Karma
Highlighted

Re: Difference between rows of query result

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Difference between rows of query result

Contributor

diff is the output

0 Karma
Highlighted

Re: Difference between rows of query result

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: Difference between rows of query result

Contributor

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

0 Karma
Highlighted

Re: Difference between rows of query result

Legend

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




| eval message="Happy Splunking!!!"


View solution in original post

Highlighted

Re: Difference between rows of query result

Contributor

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

0 Karma
Highlighted

Re: Difference between rows of query result

Contributor

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

0 Karma
Highlighted

Re: Difference between rows of query result

Legend

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



| eval message="Happy Splunking!!!"


0 Karma