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

[reverse]

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.

[manjunathmeti · Accepted solution]

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*

[reverse]

thank you 🙂

[reverse]

@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

[manjunathmeti]

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

[reverse]

thank you 🙂