Splunk Search

Calculating hourly percentage rates for multiple sources

mhamano
Explorer

I am trying to calculate hourly decline percentage rates for several different payment gateways.

The percentage is based on hourly decline count / total transaction count for that gateway * 100.

My current query is only able to pull the data for 1 gateway and looks like this:

gateway=firstdata errorType!=null event=transactionCompleted |bucket _time span=1h | eval hour=strftime(_time,"%H") | stats count(eval(success="false")) as declined, count as total by month | eval percent_declined=(declined / total * 100)

Hour |percent_declined
00 19.62

01 18.54
02 18.75

Ideally, I would want the report to look like:
Hour |PayPal| FirstData | Stripe | etc.......
00 20.02 18.94 15.214
01 19.45 17.12 15.84
02 19.62 18.54 15.44

I was thinking that this might have to use timechart but hitting a wall. The only thing that I know which the query has to include is gateway!=test which will pull up the data for all the gateways instead of one in particular.

Any guidance would would be deeply appreciated!

0 Karma

woodcock
Esteemed Legend

Like this:

gateway!=test errorType!=null event=transactionCompleted |bucket _time span=1h | eval hour=strftime(_time,"%H") | stats count(eval(success="false")) AS declined count AS total BY hour gateway | eval percent_declined=100 * declined / total | chart first(*) AS * OVER hour BY gateway
0 Karma

maciep
Champion

Trying to play with some "fake" data over here. I wonder if maybe chart could be helpful here? I'm thinking you should add gateway to your stats by clause. Then calculate the percent declined. And then from that chart over time by gateway.

Something like this maybe?

gateway!=test errorType!=null event=transactionCompleted 
| bucket _time span=1h 
| eval hour=strftime(_time,"%H") 
| stats count(eval(success="false")) as declined, count as total by hour gateway
| eval percent_declined=(declined / total * 100)
| chart avg(percent_declined) as percent_declined over hour by gateway
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...