Splunk Search

Difference between rows of query result

reverse
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
1 Solution

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

reverse
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

reverse
Contributor

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

0 Karma

niketn
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_*
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

reverse
Contributor

results would be dynamic.. first column

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

reverse
Contributor

diff is the output

0 Karma

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

reverse
Contributor

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...