APM CEM how to stop hourly defect aggregation happening repeatedly

Document ID : KB000111652
Last Modified Date : 20/08/2018
Show Technical Document Details
Issue:
The hourly aggregation always starts processing defects from Dec 5, 2017 11:00 onwards.
Currently there are no defects in database, so it always ends with 0 rows.

[INFO] [Thread-196398] [Manager.com.timestock.tess.services.scheduler.DefectAggregationTask] [main] [com.timestock.tess.services.scheduler.DefectAggregationTask] aggregating defects from 5-Dec-2017 11:00
[INFO] [Thread-196398] [Manager.com.timestock.tess.services.scheduler.DefectAggregationTask] [main] [com.timestock.tess.services.scheduler.DefectAggregationTask] aggregating defects from 5-Dec-2017 12:00
[INFO] [Thread-196398] [Manager.com.timestock.tess.services.scheduler.DefectAggregationTask] [main] [com.timestock.tess.services.scheduler.DefectAggregationTask] aggregating defects from 5-Dec-2017 13:00
[INFO] [Thread-196398] [Manager.com.timestock.tess.services.scheduler.DefectAggregationTask] [main] [com.timestock.tess.services.scheduler.DefectAggregationTask] aggregating defects from 5-Dec-2017 14:00
[INFO] [Thread-196398] [Manager.com.timestock.tess.services.scheduler.DefectAggregationTask] [main] [com.timestock.tess.services.scheduler.DefectAggregationTask] aggregating defects from 5-Dec-2017 15:00...

Every hour it will go back to try to process defects from December 5th
Environment:
Was detected in APM 9.7
Cause:
The aggregation process had stopped around the time of the error. The cause of that was not investigated but can relate to prolonged loss of connectivity to database.

The point at which aggregation stopped can be checked by running queries on the database
select max(ts_occur_date) from ts_defects_interval

select cast(min(ts_occur_date) as timestamp) from ts_defects;
 
Dates in the example went back to December 5th
Resolution:
The solution in this case is to update the ts_occur_date to a recent time in the ts_defects_interval table.
The MOM and collector running TIM Collection Service need to be stopped before doing this.

So an update to just before 9 a.m on 3rd August 2018 would look like this

update ts_defects_interval set ts_occur_date='2018-8-03 08:59:59.999+00' where ts_id=(select max(ts_id) from ts_defects_interval)

The date can be as recent as desired, but not a future date.

If there are defects in the ts_defects_interval table, they will not be aggregated, but new defects to come in after the chosen date will then be aggregated.



 
Additional Information:
If there is a concern about the data, a support case should be opened with the following information
Logs from all Enterprise Managers in the cluster
A backup of the APM database if possible, otherwise output of these tables:

ts_defect_interval,
ts_defect_aggregation_log

and output of the two select statements listed in the document