How to remove CEM reports not seen in the GUI

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

We had some CEM reports being sent to our management. Last month we noticed that the recipients were receiving duplicate reports.We tried removing and recreating the report. However, two of the same reports are still being sent.  There are no duplicate reports visible under my reports section that seem to be scheduled. How can we get rid of the duplicate reports?

 

Environment

All APM releases with CEM reports.

Resolution

Need to clear up some unwanted reports from APM db tables as covered in  https://communities.ca.com/message/241986007?et=watches.email.thread

Here are some sequence of steps to try before opening a case
Approach #1 : Remove the  explicit report from the database tables 
1) Run the SQL query -- select * from ts_report_defs; select * from ts_cached_reports;
2) Find the row with the report needing to be removed. 
    Alternative: select * from ts_report_def_param_values where ts_value = 'report1 name'; 

2) Delete the row with a SQL command something like shown below.
   delete from ts_cached_reports where ts_id='nnnnnnn';

Approach #2: Review and remove selected scheduled reports

Review the cron triggers for ANY report running daily or the scheduled time  and see which report it corresponds to in ts_report_defs and ts_report_def_param_values. 
The information can be found in 
qrtz_cron_triggers 
qrtz_triggers 
qrtz_job_details

The cron trigger format is detailed in http://www.quartz-scheduler.org/documentation/quartz-1.x/tutorials/crontrigger 

Approach #3: Clear all tables
This deletes all reports and their scheduling information. It is included here for completeness but is not recommended since the issue may still persist afterwards.
delete from qrtz_cron_triggers; 
delete from qrtz_triggers; 
delete from qrtz_job_details ;
delete from ts_report_defs;
delete from ts_report_def_param_values;


Then recreate these tables through the GUI


 

Additional Information

  Restarting the database clearing all in cache and completely recreating the tables may also help but should be done after case review by CA Support