Possible to find different of two amounts from different events but already within a sort by time span?

[FGAnders]

Hi,

Is it somehow possible to find difference between two or more amounts from different events when the events are sorted by time span of 20s.

index=Prod123 methodType='WITHDRAW' currency='GBP'  jurisdiction=UK transactionAmount>=3 | fieldformat _time = strftime(_time, "%Y-%m-%d %H:%M:%S") | bin span=20s _time | search transactionAmount=* | stats list(transactionAmount) as Total, list(currency) as currency, list(_time) as Time, dc(customerId) as Users by _time | fieldformat Time = strftime(Time, "%Y-%m-%d %H:%M:%S") | fieldformat _time = strftime(_time, "%Y-%m-%d %H:%M:%S") | search Users>=2 | sort - Time

I would like to have it show the difference between the Totals. Like the first one Total 3.8 and 11.2.
Is it possible to make it work somehow or would it be better with streamstats and window? 

I was thinking about using sum or avg as an option also.

Thank you, 

[bowesmana · Accepted solution]

You have lots of unnecessary fieldformat statements.

I would suggest

index=Prod123 methodType='WITHDRAW' currency='GBP' jurisdiction=UK transactionAmount>=3 
| search transactionAmount=* 
| bin span=20s _time 
| stats list(transactionAmount) as Total, list(currency) as currency, dc(customerId) as Users by _time 
| where Users>=2 
| eval diff=tonumber(mvindex(Total, 0)) - tonumber(mvindex(Total, 1))
| sort - _time

you don't need list(_time) as Time because it will contain the same values as your split by _time field as you a binning _time

You don't need fieldformat for _time as that is how _time is already displayed.

[FGAnders]

Hi,

Thank you for your help and suggestion.

Sadly _time without the fieldformat does not come out it in the format. 

This solution works thank you very much.

[yuanliu]

Instead of asking volunteers to speculate what you mean by reverse engineering from complex SPL and screenshot, please illustrate some data in text (anonymize as necessary), explain key characteristics of dataset, illustrate desired results in text, and explain the logic between data and desired results.