Splunk Search

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

FGAnders
Explorer

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.

FGAnders_0-1696337358407.png

Thank you, 

Labels (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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
Explorer

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

SOC4Kafka - New Kafka Connector Powered by OpenTelemetry

The new SOC4Kafka connector, built on OpenTelemetry, enables the collection of Kafka messages and forwards ...

Your Voice Matters! Help Us Shape the New Splunk Lantern Experience

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Building Momentum: Splunk Developer Program at .conf25

At Splunk, developers are at the heart of innovation. That’s why this year at .conf25, we officially launched ...