Missing data in CEM Analysis Graphs and Reports for Timeframe "Today", "Custom Hour", "Previous Hour", but other timeframes based on Day, Week, Month do show data. ERROR: Select failed for ts_st_ts_us_int

Document ID : KB000048152
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

When hourly data is missing in CEM Analysis Graphs and reports, but daily, weekly, or monthly data is available, it may indicate that there is a problem with the hourly aggregation task. The task may fail to run when there is corruption in specific APM database tables. This article provides knowledge on how to manually repair corrupt hourly interval tables.

Solution:

The root cause of missing hourly interval data is often a database table corruption for the last aggregated date value in the hourly (*_INT) tables/partitions.

NOTE: The hourly stats aggregation task runs on the Enterprise Manager which is running the Tim Collection Service (the TIM Collector) and not on the Enterprise Manager running the Stats Aggregation Service when the Stats Aggregation Service has been configured to run on a separate Enterprise Manager.

The hourly stats aggregation task runs at the top of each hour. If the TIM Collector log, [EM_HOME]/logs/IntroscopeEnterpriseManager.log, contains errors similar to those shown below after the top of each hour, the problem is due to corrupt values for the last aggregation date value in the hourly tables/partitions.

PostgreSQL APM Database:
[ERROR] [StatsCollector.Thread1] [Manager.com.timestock.tess.util.DbUtils] Select failed for ts_st_ts_us_int_<date>
org.postgresql.util.PSQLException: ERROR: relation "ts_st_ts_us_int_<date>" does not exist

Oracle APM Database:
[ERROR] [StatsCollector.Thread1] [Manager.com.timestock.tess.util.DbUtils] Select failed for ts_st_ts_us_int partition (ts_st_ts_us_int_<date>)
java.sql.SQLSyntaxErrorException: ORA-02149: Specified partition does not exist

To resolve the problem, run the following UPDATE SQL for the 7 hourly (*_INT) tables against the relevant database/schema at the bottom of the hour. By waiting until the bottom of the hour, you can avoid having to stop and restart the Enterprise Manager to perform these updates.

The SQL statements here will reset the last aggregated date to 11.00PM the previous night.

PostgreSQL APM Database:
UPDATE ts_st_ts_all_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_ts_usgrp_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_tsgrp_all_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_tsgrp_us_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_tsgrp_usgrp_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_tu_all_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';
UPDATE ts_st_tu_usgrp_int SET ts_last_aggregated_row = DATE_TRUNC('day', now()) - INTERVAL '1 hour';

Oracle APM Database:
UPDATE ts_st_ts_all_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_ts_usgrp_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_tsgrp_all_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_tsgrp_us_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_tsgrp_usgrp_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_tu_all_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;
UPDATE ts_st_tu_usgrp_int SET ts_last_aggregated_row = ROUND(current_timestamp-1/2, 'DD') - 1/24;

At the next top of the hour, the errors in the TIM Collector log should no longer occur, and once the hourly aggregation has completed, hourly data for the current day should be displayed in the CEM Analysis Graphs & stats reports.

If you require further assistance with applying the SQL or have a similar problem for different timeframes that includes Day, Week, Month, please raise a new case on MyCA/Support Online so that a Support Engineer can provide more detailed advice.