Calculating hourly percentage rates for multiple sources

[mhamano]

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!

[woodcock]

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

[maciep]

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