Portal 3.5 Analytic Reports Show No Data, but Data Seen in Database.

Document ID : KB000118521
Last Modified Date : 25/10/2018
Show Technical Document Details
Issue:
  • CA API Developer Portal ("Portal") 3.5 will not show any data in any reports accessed via Dashboard > Analytics.
  • Data is still updated in the database from the Gateway, no failures seen in the data transfer or database itself.
Environment:
  • Portal 3.5
Cause:
  • Root cause unconfirmed, but the theory is that this issue arose as the result of an incomplete or botched database migration between one Portal to another Portal. This would have been for the lrs and lrsdata databases on the Portal side, not on any databases from the Gateway.
  • In this case, it was determined that MySQL had views as tables when they should have remained as MySQL views instead.
Resolution:
  • Check the databases on the Portal and look for any possible tables created with "_view" in the name. If api_key_or_method_usage_view or api_usage_view exists, for example, then the following SQL commands should be run to resolve the issue. Be sure to take a full database backup or VM snapshot so there is a restore point in case these commands don't work in your environment.
drop table if exists api_key_or_method_usage_view;

drop view if exists api_key_or_method_usage_view;

create VIEW `api_key_or_method_usage_view` AS select `sm`.`goid` AS `metric_id`,`sm`.`uuid` AS `uuid`,`sm`.`published_service_goid` AS `api_id`,`p`.`name` AS `api_name`,`p`.`routing_uri` AS `api_uri`,`sm`.`resolution` AS `resolution`,`sm`.`period_start` AS `bin_start_time`,`sm`.`end_time` AS `bin_end_time`,`smd`.`front_min` AS `front_min`,`smd`.`front_max` AS `front_max`,`smd`.`front_sum` AS `front_sum`,`smd`.`back_min` AS `back_min`,`smd`.`back_max` AS `back_max`,`smd`.`back_sum` AS `back_sum`,`smd`.`attempted` AS `hits_total`,`smd`.`completed` AS `hits_success`,(`smd`.`attempted` - `smd`.`completed`) AS `hits_total_errors`,(`smd`.`attempted` - `smd`.`authorized`) AS `hits_policy_errors`,(`smd`.`authorized` - `smd`.`completed`) AS `hits_routing_errors`,(case when (`mcmk`.`mapping1_key` = 'API_KEY') then `mcmv`.`mapping1_value` when (`mcmk`.`mapping2_key` = 'API_KEY') then `mcmv`.`mapping2_value` when (`mcmk`.`mapping3_key` = 'API_KEY') then `mcmv`.`mapping3_value` when (`mcmk`.`mapping4_key` = 'API_KEY') then `mcmv`.`mapping4_value` when (`mcmk`.`mapping5_key` = 'API_KEY') then `mcmv`.`mapping5_value` end) AS `api_key`,(case when (`mcmk`.`mapping1_key` = 'API_METHOD') then `mcmv`.`mapping1_value` when (`mcmk`.`mapping2_key` = 'API_METHOD') then `mcmv`.`mapping2_value` when (`mcmk`.`mapping3_key` = 'API_METHOD') then `mcmv`.`mapping3_value` when (`mcmk`.`mapping4_key` = 'API_METHOD') then `mcmv`.`mapping4_value` when (`mcmk`.`mapping5_key` = 'API_METHOD') then `mcmv`.`mapping5_value` end) AS `api_method` from ((((`published_service` `p` join `service_metrics` `sm`) join `service_metrics_details` `smd`) join `message_context_mapping_values` `mcmv`) join `message_context_mapping_keys` `mcmk`) where ((`sm`.`published_service_goid` = `p`.`goid`) and (`sm`.`goid` = `smd`.`service_metrics_goid`) and (`smd`.`mapping_values_goid` = `mcmv`.`goid`) and (`mcmv`.`mapping_keys_goid` = `mcmk`.`goid`) and ((`mcmk`.`mapping1_key` = 'API_KEY') or (`mcmk`.`mapping2_key` = 'API_KEY') or (`mcmk`.`mapping3_key` = 'API_KEY') or (`mcmk`.`mapping4_key` = 'API_KEY') or (`mcmk`.`mapping5_key` = 'API_KEY')) and ((`mcmk`.`mapping1_key` = 'API_METHOD') or (`mcmk`.`mapping2_key` = 'API_METHOD') or (`mcmk`.`mapping3_key` = 'API_METHOD') or (`mcmk`.`mapping4_key` = 'API_METHOD') or (`mcmk`.`mapping5_key` = 'API_METHOD')));

drop table if exists api_usage_view;

drop view if exists api_usage_view;

create VIEW `api_usage_view` AS select `sm`.`goid` AS `metric_id`,`sm`.`uuid` AS `uuid`,`sm`.`published_service_goid` AS `api_id`,`p`.`name` AS `api_name`,`p`.`routing_uri` AS `api_uri`,`sm`.`resolution` AS `resolution`,`sm`.`period_start` AS `bin_start_time`,`sm`.`end_time` AS `bin_end_time`,`sm`.`front_min` AS `front_min`,`sm`.`front_max` AS `front_max`,`sm`.`front_sum` AS `front_sum`,`sm`.`back_min` AS `back_min`,`sm`.`back_max` AS `back_max`,`sm`.`back_sum` AS `back_sum`,`sm`.`attempted` AS `hits_total`,`sm`.`completed` AS `hits_success`,(`sm`.`attempted` - `sm`.`completed`) AS `hits_total_errors`,(`sm`.`attempted` - `sm`.`authorized`) AS `hits_policy_errors`,(`sm`.`authorized` - `sm`.`completed`) AS `hits_routing_errors` from (`service_metrics` `sm` join `published_service` `p`) where (`sm`.`uuid` = `p`.`uuid`);
Additional Information:
  • This has only been seen one time (at time of writing) by CA Support. If the same behaviour is seen but the tables do not match the example above, please contact CA Support for verification on whether this is the same issue or a different issue.