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?

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 %"
Tags (5)
1 Solution
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 %"
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 %"
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.

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
Communicator

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

New Member

Build 6.25!

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!

Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!