Splunk Search

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
New Member

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 %"
0 Karma
1 Solution

justinatpnnl
Communicator

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 %"

View solution in original post

justinatpnnl
Communicator

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 %"

View solution in original post

dean1
New Member

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.

0 Karma

dean1
New Member

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
0 Karma

justinatpnnl
Communicator

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

0 Karma

dean1
New Member

Build 6.25!

0 Karma

justinatpnnl
Communicator

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!

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.