Layer7 Hourly report via MYSQL

Products

• CA API Gateway

Issue/Introduction

The hourly report for routing success and routing failure numbers

Environment

Release : 10.1

Resolution

The sample query is for the hourly report for "2022-10-30"

select FROM_UNIXTIME(period_start/1000, '%d-%m-%y %H:%i:%s') Datetime, sum(completed) "Success requests", sum(attempted-completed) "Failure requests", sum(attempted) Total from service_metrics where resolution= 1 and period_start >= unix_timestamp("2022-10-30")*1000 and period_start < unix_timestamp("2022-10-31")*1000 group by period_start order by period_start;

 

Samle output:

+-------------------+------------------+------------------+-------+
| Datetime          | Success requests | Failure requests | Total |
+-------------------+------------------+------------------+-------+
| 30-10-22 00:00:00 |               60 |                0 |    60 |
| 30-10-22 01:00:00 |               60 |                0 |    60 |

...

 

Note: the hourly metrics won't be kept too long time, as it's supposed to be merged to daily metrics. The "merged" hourly data will be deleted, only non-merged data can be find in database. So, for hourly report, it's better to generate the report every day, and the date range is set to yesterday.