Maintenance schedules in UIM are not getting deleted after the active window
Article ID: 199507
Updated On:
Products
Issue/Introduction
If i schedule a maintenance window for a server in UIM for next 4 hours after that the server is coming out from maintenance and there are no servers part of that maintenance window. It will be present for ever even after few months. Can we get these schedules deleted. These are creating issues for the spectrum-UIM integration.
Environment
Release : 9.1.0
Component : UIM MAINTENANCE MODE
Resolution
From the maintenance_mode release notes:
Introduced a new key "purge_maintenance_window_history_interval" in maintenance_mode.cfg in <setup> section which will delete expired windows from maintenance_window_history table. It is disabled by default. To enable it, purge_maintenance_window_history_interval (a new configuration) is to be set to a whole integer in the maintenance mode probe configuration under setup (for example, 1, which means the task would run every one hour). If the task is run, all the expired maintenance window entries would get deleted. To disable the task, it has to be reset to -1.
You can use these statements for maintenance cleanup to clean them up immediately.
--- SCRIPT ---
--- Cleanup of maintenance windows and schedules
--- Cleanup of empty schedules
DECLARE @message varchar(100);
DECLARE @cs AS NVARCHAR(500);
DECLARE devicecursor CURSOR for
SELECT [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID] from [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE] left join [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS] on
[CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS].[SCHEDULE_ID] = [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID] where [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS].[CS_ID] IS NULL;
open devicecursor
fetch next from devicecursor
into @cs
while @@FETCH_STATUS = 0
BEGIN
print ' '
select @message = '-- DELETING SCHEDULE ID: ' + @cs + ' from MAINTENANCE_SCHEDULE'
print @message
delete from [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE] where [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID] = @cs;
select @message = 'DELETING MAINTENANCE WINDOWS connected to schedule_id: ' + @cs + ' from MAINTENANCE_WINDOW'
delete from [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[SCHEDULE_ID] = @cs;
print @message
FETCH NEXT FROM devicecursor
into @cs
END
CLOSE devicecursor;
DEALLOCATE devicecursor;
--- Cleanup of maintenance schedules older than 60 days
DECLARE @message2 varchar(100);
DECLARE @cs2 AS NVARCHAR(500);
DECLARE plancursor CURSOR for
SELECT [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID]
FROM [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE] left join
[CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS] on
[CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS].[SCHEDULE_ID] = [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID]
left join
[CA_UIM].[dbo].[CM_COMPUTER_SYSTEM] on
[CA_UIM].[dbo].[CM_COMPUTER_SYSTEM].[CS_ID] = [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS].[CS_ID]
where [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[RECURRENCE_PATTERN] = 0
and [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[RECURRENCE_PERIOD] = 0
and [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[START_TIME] < DATEADD(day, -60, GETDATE())
and [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[DURATION] < 86400;
open plancursor
fetch next from plancursor into @cs2
while @@FETCH_STATUS = 0
BEGIN
print ' '
select @message2 = '-- Deleting member(s) from schedule_id: ' + @cs2 + ' from MAINTENANCE_SCHEDULE_MEMBERS';
print @message2
delete from [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS] where [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE_MEMBERS].[SCHEDULE_ID] = @cs2;
select @message2 = 'DELETING MAINTENANCE WINDOWS connected to schedule_id: ' + @cs2 + ' from MAINTENANCE_WINDOW'
print @message2
delete from [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[SCHEDULE_ID] = @cs2;
select @message2 = '-- DELETING SCHEDULE ID: ' + @cs2 + ' from MAINTENANCE_SCHEDULE'
print @message2
delete from [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE] where [CA_UIM].[dbo].[MAINTENANCE_SCHEDULE].[SCHEDULE_ID] = @cs2;
FETCH NEXT FROM plancursor
into @cs2
END
CLOSE plancursor;
DEALLOCATE plancursor;
--- Cleanup of old maintenance WINDOWS
DECLARE @message3 varchar(100);
DECLARE @wi AS NVARCHAR(500);
DECLARE windowcursor CURSOR for
SELECT [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[WINDOW_ID] FROM [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where SCHEDULE_ID IS NULL;
open windowcursor
fetch next from windowcursor into @wi
while @@FETCH_STATUS = 0
BEGIN
print ' '
select @message3 = '-- Deleting window: ' + @wi + ' without a schedule defined'
print (message continues in next comment)
delete from [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[WINDOW_ID] = @wi;
FETCH NEXT FROM windowcursor
into @wi
END
CLOSE windowcursor;
DEALLOCATE windowcursor;
DECLARE @message4 varchar(100);
DECLARE @wi2 AS NVARCHAR(500);
DECLARE windowcursor2 CURSOR for
SELECT [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[WINDOW_ID] FROM [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[END_TIME] < DATEADD(day, -60, GETDATE());
open windowcursor2
fetch next from windowcursor2 into @wi2
while @@FETCH_STATUS = 0
BEGIN
print ' '
select @message4 = '-- Deleting window: ' + @wi2 + ' as window passed 60 days ago'
print @message4
delete from [CA_UIM].[dbo].[MAINTENANCE_WINDOW] where [CA_UIM].[dbo].[MAINTENANCE_WINDOW].[WINDOW_ID] = @wi2;
FETCH NEXT FROM windowcursor2
into @wi2
END
CLOSE windowcursor2;
DEALLOCATE windowcursor2;
Additional Information
As of maintenance_mode v20.10, add the purge_maintenance_window_history_interval which can be set to 1 (which is 1 hour).
Feedback
