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!

Splunk Lantern | Spotlight on Security: Adoption Motions, War Stories, and More

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

Splunk Cloud | Empowering Splunk Administrators with Admin Config Service (ACS)

Greetings, Splunk Cloud Admins and Splunk enthusiasts! The Admin Configuration Service (ACS) team is excited ...

Tech Talk | One Log to Rule Them All

One log to rule them all: how you can centralize your troubleshooting with Splunk logs We know how important ...