SQL queries to remove scheduled jobs when unable to view Scheduled Jobs list

Products

Webtrends Analytics 8.x

Issue

Due to an error in one of the lines, the Scheduled Jobs list is unable to be viewed.

Cause

This can occur if someone attempted to run a SQL query to create the scheduled jobs instead of using the user interface.

Resolution

Due to the number of linked tables, the contents of delete wt_taskstatus must first be deleted, then the specific jobs by job ID in linked tables.

It is possible to delete all jobs of a certain type. In the wt_sched.dbo.wt_eventtype table, for a particular type of event, a query similar to the following will remove all events of that type. For example, the following query deletes all reset events:

delete from wt_sched.dbo.wt_taskstatus where taskchronicleid in (select taskchronicleid from wt_sched.dbo.wt_taskchronicle where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′));
delete from wt_sched.dbo.wt_taskchronicle where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);
delete from wt_sched.dbo.wt_parameter where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);
delete from wt_sched.dbo.wt_eventstatus where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);
delete from wt_sched.dbo.wt_monitoralertresolvehistory where monitorresultid in (select monitorresultid from wt_sched.dbo.wt_monitorresult where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′));
delete from wt_sched.dbo.wt_monitorresult where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);
delete from wt_sched.dbo.wt_schedule where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);
delete from wt_sched.dbo.wt_event where eventid in (select eventid from wt_sched.dbo.wt_event where eventtypeid=’128′);

To only delete a list of events, use something similar to the following:

delete from wt_sched.dbo.wt_taskstatus where taskchronicleid in (19,20,21);
delete from wt_sched.dbo.wt_taskchronicle where eventid in (19,20,21);
delete from wt_sched.dbo.wt_parameter where eventid in (19,20,21);
delete from wt_sched.dbo.wt_eventstatus where eventid in (19,20,21);
delete from wt_sched.dbo.wt_monitoralertresolvehistory where monitorresultid in (select monitorresultid from wt_sched.dbo.wt_monitorresult where eventid in (19,20,21));
delete from wt_sched.dbo.wt_monitorresult where eventid in (19,20,21);
delete from wt_sched.dbo.wt_schedule where eventid in (19,20,21);
delete from wt_sched.dbo.wt_event where eventid in (19,20,21);

In this case, the comma separated list includes the event ids to be removed.

Use a query similar to the following to get a list of event ids:

select eventid,description from wt_sched.dbo.wt_event where description like ‘%NAME%’;

In the above query, NAME should be part of the event name, i.e., the profile name or the kind of job that is running such as Deleted Data Cleanup. Note that % is needed around the value to be matched to denote it as a substring.