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.
All supported versions of the API Gateway
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.