Can we use delta command to get a difference between current & previous value which have the same unique id?

user_13053727 0
Viewed 266

I'm trying to find a difference between current & previous value of AOV (Avg value) which have the same unique ID (country). Right now my query gets the difference but doesn't respect the unique IDs. Any help or suggestion would be great!!

Query

index=test source=orders_prd_*
| dedup message.orderNo
| bin _time span=1h
| stats avg(message.orderTotal) as AOV by country _time | delta AOV AS Diff | eval OrderPercentage=(((Diff)/AOV)*100)

Output: Output

splunk-query splunk
1 Answers

Try streamstats in place of delta. With a window of 2 events, the range function is the same as delta.

index=test source=orders_prd_*
| dedup message.orderNo
| bin _time span=1h
| stats avg(message.orderTotal) as AOV by country _time 
| streamstats window=2 range(AOV) AS Diff by country
| eval OrderPercentage=(((Diff)/AOV)*100)

Since range is not giving the desired results, you can use delta, but you'll have to jump through some hoops to respect country changes.

index=test source=orders_prd_*
| dedup message.orderNo
| bin _time span=1h
| stats avg(message.orderTotal) as AOV by country _time
| delta AOV AS Diff 
```Count the results by country.```
| streamstats reset_on_change=1 count by country
```When the count is 1 then we have a new country
   so clear the Diff field.```
| eval Diff=if(count==1, null(), Diff)
| eval OrderPercentage=(((Diff)/AOV)*100)
| fields - count
user_22274200
Related