How to search the difference of first and last value of an extracted field across a 120 day time range and calculate the percentage?

[dean1]

I'm trying to build a search to show the difference of the field total across a 120 day interval. The search I have below works, but its taking the min and max number across the whole 120 day interval, finding the difference, then computing a %. I need the search to take the first value on day 1, the last value on day 120, and show the difference and compute the percentage...

For the time interval, I was just using the time picker....

Search:

| stats max(total) as Max, min(total) as Min, range(total) as Difference | eval Difference_Percent = Difference / Max * 100 | rename Difference_Percent as "Growth %"

[justinatpnnl · Accepted solution]

It seems like earliest() and latest() should do the trick for you:

| stats latest(total) as Latest, earliest(total) as Earliest 
| eval Difference=Latest - Earliest, Difference_Percent = Difference / Latest * 100 
| rename Difference_Percent as "Growth %"

[dean1]

Thankyou! The earliest() latest() command calculates the difference of the numbers Im looking for ( first and last), however the % calculation is bombing out for some reason ?... appears to be right after the "," in the SPL Query...

⚠ Error in 'eval' command: The operator at ', Difference_Percent = Difference / Latest * 100' is invalid.

[dean1]

Splunk seems to be happy with this :

index=my_summary_index source="my_source"  | stats latest(total) as Latest, earliest(total) as Earliest  | eval Difference=Latest - Earliest | eval Difference_Percent = Difference / Latest * 100

[justinatpnnl]

Odd! Are you running an older version of Splunk? It must not like combining the eval statements.

[dean1]

Build 6.25!

[justinatpnnl]

That explains it. Looking at the differences in the docs, tt looks like it was added in 6.3.0. I'll have to keep that in mind when suggesting queries. Thanks!