Analytics not generating

Document ID : KB000123070
Last Modified Date : 17/12/2018
Show Technical Document Details
Issue:
Every time the generate report from Portal dashboard  following error is logged... 


ERROR (http-nio-37080-exec-20:) - [Layer7Reports general] -- com.l7tech.portal.reports.ReportGenerationException: Error retrieving result set: Table 'lrsdata.api_key_or_method_usage_view' doesn't exist 
Environment:
API Portal 3.x
Cause:
'lrsdata.api_key_or_method_usage_view' view missing in lrsdata database. 
Resolution:
Run the below query to create the missing view. 

use lrsdata; 

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')));