Are there known DB commands to get dashboard metrics from the CA Layer7 API Gateway?

Products

• CA API Gateway

Issue/Introduction

 Are there known DB commands to get dashboard metrics?

Environment

Release : 10.1

Resolution

This is one way to get metrics directly from the db,

# mysql ssg

mysql> select ps.name, from_unixtime(sm.start_time/1000, '%Y %D %M %H:%i:%s') as start, from_unixtime(sm.end_time/1000, '%Y %D %M %H:%i:%s') as End, sm.attempted, sm.completed from service_metrics sm join published_service ps on sm.published_service_goid = ps.goid where start_time/1000 > UNIX_TIMESTAMP(NOW() - INTERVAL 3 MONTH);

For a summary type of view you can group by service and sum the hits,

mysql> select ps.routing_uri as URL, sum(sm.attempted) as attempted, sum(sm.comp
leted) as completed from service_metrics sm join published_service ps on sm.publ
ished_service_goid = ps.goid where start_time/1000 > UNIX_TIMESTAMP(NOW() - INTE
RVAL 3 MONTH) and attempted <> 0 group by ps.routing_uri;

My Results from the SUM/Group above:

+--------------+-----------+-----------+
| URL          | attempted | completed |
+--------------+-----------+-----------+
| /r2          |        13 |         6 |
| /MyServ   |        17 |         6 |
| /Pass        |        78 |        67 |
| /echo/*      |         1 |         1 |
| /Some        |         7 |         7 |
| /restman/*   |        25 |        10 |

This interval 3 month can be changed to 1 day or whatever you wish for the time period in the above queries.