Deleting scheduled Altiris tasks from SQL

3 May

I needed to delete around 50 scheduled tasks from several machines in Altiris because something went wrong in on of the first jobs.

It would have better if the jobs were configured to fail on error and not continue but they weren’t.

Deleting the jobs from the Altiris console is very, very, slow. First the console asks for confirmation (after showing the hourglass for a long time):

Then the actual delete can take a few minutes and then the next server and so on.

I decided to delete the tasks directly from SQL.

I know this is not preferred but I think in the end it’s safe enough because I found a stored procedure called del_event_schedule which looks like this:

ALTER procedure [dbo].[del_event_schedule]
   @schedule_id         int
as
   begin transaction

   delete from event_schedule where schedule_id = @schedule_id

   if (@@error != 0)
      rollback transaction
   else
      commit transaction

ALTER procedure [dbo].[del_event_schedule]

@schedule_id int

begin transaction

delete from event_schedule where schedule_id = @schedule_id

if (@@error != 0)

rollback transaction

else

commit transaction

So al it does is a (transacted) delete from the table.

First I created a query that selects the current scheduled tasks:

SELECT computer.name, event.name
FROM dbo.event_schedule 
INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id
INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id
WHERE dbo.computer.computer_name like 'VCTXA070%'
AND dbo.event_schedule.status_code is null
AND event_schedule.start_time is not null

SELECT computer.name, event.name

FROM dbo.event_schedule

INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id

INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id

WHERE dbo.computer.computer_name like 'VCTXA070%'

AND dbo.event_schedule.status_code is null

AND event_schedule.start_time is not null

This selects the computer name and task name for all computers that match VCTXA070% that have not yet started (status_code is null).

The start_time check prevents deleting recurring tasks like this one:

Since this gave me the correct results I deleted them:

DELETE FROM event_schedule
FROM dbo.event_schedule 
INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id
INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id
WHERE dbo.computer.computer_name like 'VCTXA070%'
AND dbo.event_schedule.status_code is null
AND event_schedule.start_time is not null

DELETE FROM event_schedule

FROM dbo.event_schedule

INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id

INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id

WHERE dbo.computer.computer_name like 'VCTXA070%'

AND dbo.event_schedule.status_code is null

AND event_schedule.start_time is not null

If you want to delete completed tasks as well you can just leave out the status_code check:

DELETE FROM event_schedule
FROM dbo.event_schedule 
INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id
INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id
WHERE dbo.computer.computer_name like 'VCTXA070%'
AND event_schedule.start_time is not null

DELETE FROM event_schedule

FROM dbo.event_schedule

INNER JOIN dbo.computer ON dbo.computer.computer_id = dbo.event_schedule.computer_id

INNER JOIN dbo.event ON dbo.event_schedule.event_id = dbo.event.event_id

WHERE dbo.computer.computer_name like 'VCTXA070%'

AND event_schedule.start_time is not null

Please consider donating something (even a small amount is ok) to support this site and my work:

Filed under: SQL Server, VMWare

One Response for "Deleting scheduled Altiris tasks from SQL"

Mario May 12th, 2012 at 20:03 1
Thanx man very useful post

Entries (RSS)
Comments (RSS)

Remko Weijnen's Blog (Remko's Blog)

Deleting scheduled Altiris tasks from SQL

Related

One Response for "Deleting scheduled Altiris tasks from SQL"

Leave a reply

Profile

Top Posts

Recent Comments

Featured Downloads

Blogroll

Categories

Archives