API Usage Statistics

Products

• CA API Gateway

Issue/Introduction

How do you get the usage statistics for APIs?  The purpose is to identify APIs that have not receieved any requests.

Is there a query in the database or the steps to get this information?

Note: The need is to create/export a report that specifies the list of APIs including the ones that have not received any requests in more than three months.

Environment

All supported versions of the API Gateway

Resolution

There is a database query that can check if there have been any hits on an API in the past 3 months. If the hits_total column is 0, then the API hasn't processed traffic in the past 3 months.

select `p`.`name` AS `api_name`,sum(`sm`.`attempted`) AS `hits_total`,sum(`sm`.`completed`) AS `hits_success`,(sum(`sm`.`attempted`) - sum(`sm`.`completed`)) AS `hits_total_errors` from (`service_metrics` `sm` join `published_service` `p`) where (`sm`.`published_service_goid` = `p`.`goid`) and `sm`.`period_start` between unix_timestamp('YYYY-MM-DD')*1000 and unix_timestamp('YYYY-MM-DD')*1000 group by `api_name`;

For example, if you want to check between October 2024 to January 2025:

select `p`.`name` AS `api_name`,sum(`sm`.`attempted`) AS `hits_total`,sum(`sm`.`completed`) AS `hits_success`,(sum(`sm`.`attempted`) - sum(`sm`.`completed`)) AS `hits_total_errors` from (`service_metrics` `sm` join `published_service` `p`) where (`sm`.`published_service_goid` = `p`.`goid`) and `sm`.`period_start` between unix_timestamp('2024-10-01')*1000 and unix_timestamp('2025-01-01')*1000 group by `api_name`;

NOTE: If you have purged your service metrics table, the results may not be 100% accurate.