Splunk Search

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

reverse
Contributor

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.

Tags (3)
0 Karma
1 Solution

manjunathmeti
Champion

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*

View solution in original post

manjunathmeti
Champion

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
Contributor

thank you 🙂

0 Karma

reverse
Contributor

@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

0 Karma

manjunathmeti
Champion

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
Contributor

thank you 🙂

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...