topic Re: How to calculate the difference between the first and the last row for each page in search results? in Splunk Search

How to calculate the difference between the first and the last row for each page in search results?

reverse — Wed, 22 Apr 2020 17:26:36 GMT

My search produced the following CSV:

Date Page_1      Page_2       Page_3       Page_4        Page_5        Page_6....
1-Jan      1       2           3            4            5            6
2-Jan      10       20           3            4            5            6
..
..
..
22-Apr      100       200           3000           7654            86895           76476

How can I calculate the difference between the first and the last row for every page? Please help.

Re: How to calculate the difference between the first and the last row for each page in search results?

manjunathmeti — Wed, 22 Apr 2020 19:07:51 GMT

Hi @reverse,

Try this:

| eventstats first(Page*) as first_Page*, last(Page*) as last_Page* 
| foreach Page* 
    [ eval diff_<<FIELD>> = last_<<FIELD>> - first_<<FIELD>>] 
| table Date, Page*, diff_Page*

Sample query:

| makeresults 
| eval _raw="Date,Page_1,Page_2,Page_3,Page_4,Page_5,Page_6
1-Jan,1,2,3,4,5,6
2-Jan,10,20,3,4,5,6
22-Apr,100,200,3000,7654,86895,76476" 
| multikv forceheader=1 
| eventstats first(Page*) as first_Page*, last(Page*) as last_Page* 
| foreach Page* 
    [ eval diff_<<FIELD>> = last_<<FIELD>> - first_<<FIELD>>] 
| table Date, Page*, diff_Page*

Re: How to calculate the difference between the first and the last row for each page in search results?

reverse — Thu, 23 Apr 2020 02:23:46 GMT

thank you 🙂

Re: How to calculate the difference between the first and the last row for each page in search results?

reverse — Wed, 30 Sep 2020 05:04:45 GMT

@manjunathmeti .. what if there is no pattern in the first row ...

rather than Page_1 Page_2 Page_3 Page_4 Page_5 Page_6....

it is ANJ, JFJ,YFYU,FFJH,FYFUY

Re: How to calculate the difference between the first and the last row for each page in search results?

manjunathmeti — Thu, 23 Apr 2020 07:04:09 GMT

Then you should use exact field names:

| eventstats first(ANJ) as first_ ANJ, last(ANJ) as last_ ANJ,  first(JFJ) as first_ JFJ, last(JFJ) as last_ JFJ, .....
| foreach ANJ, JFJ, YFYU,......
     [ eval diff_<<FIELD>> = last_<<FIELD>> - first_<<FIELD>>] 
| table Date, ANJ, JFJ,YFYU,FFJH,FYFUY, diff_*

Re: How to calculate the difference between the first and the last row for each page in search results?

reverse — Thu, 23 Apr 2020 18:41:27 GMT

thank you 🙂