Is it possible to close an incident outside of using the CEM UI - e.g. using SQL direct to the APM database.

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

Description:

This requirement arose from a user seeing a very large number of defects generated everyday for an incident.

Due to the large number of defects, it was taking a very long time to open the incident in the UI to be able to close it.

Solution:

It is possible to use SQL to close the incident, although care should be taken as direct use of UPDATE SQL against the APM database is not officially supported.

The relevant APM database table/column is:

  • Table: ts_biz_events

  • Table Column: ts_status.

The ts_status column values have these meanings for the incident status:

  • 1 = Close

  • 2 = Open

  • 3=Pending

  • 4=Agedout

Examples of SQL:

To find the IDs of all open incidents:
select ts_id from ts_biz_events where ts_status = 2;

To close all open incidents:
update ts_biz_events set ts_status = 1 where ts_status =2;

To close a specific incident ID:
update ts_biz_events set ts_status = 1 where ts_id=ID;

To complete the process, the Tim Collection Service EM Collector also needs to be restarted to update its database cache.

To automate these steps, a shell script could be designed to execute the required SQL commands followed by a restart of the EM running the TIM Collection Service.

The script could then be scheduled to run at a suitable downtime e.g. every midnight, via cron (Unix/Linux) or Task Manager (Windows).