DESCRIPTION FOR STORED PROCEDURE
This procedure will enter monitoring blackout periods for all instances being monitored in DPA with a customizable where clause. (Perhaps you want to delimit the query by instance type, IP, etc.) You enter the date and time in the format as suggested and the script will translate it by day and hour. This is useful for if you have regularly scheduled maintenance by server type, IP, or any other criteria we document in COND.
Example: EXEC AutomatedMonBlackout ‘2014-05-27 20:00’,’2014-05-28 22:00’ will start the blackout period on Wednesdays at 22:00 and stop the blackout period on Tuesdays at 20:00.
Note: The stored procedure needs to be created in your Ignite Repository and ran from there as the Ignite repository owner. You may have to change the schema to match your instance before the script will execute.
STORED PROCEDURE
GO
/****** Object: StoredProcedure [dbo].[AutomatedMonBlackout] Script Date: 5/28/2014 1:37:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- AutomatedMonBlackout PROC
--
-- EXECUTE THIS SCRIPT AS THE IGNITE REPOSITORY OWNER.
-- PARMS '@StartBlackout', '@EndBlackout'
-- The script inserts all COND values into the CONBLACKOUT table.
-- You can remove the values by either truncate table conblackout or through the gui.
-- There is a commented out where clause that you can adjust so you can specify types of RBDM to stop monitoring, all of this is obviously configurable as you see fit.
-- The script will convert the date to a day of the week and 24 hour format.
-- EXAMPLE:
--
-- EXEC AutomatedMonBlackout '2014-05-27 20:00','2014-05-28 22:00'
-- truncate table conblackout
Create PROC [dbo].[AutomatedMonBlackout]
(@StartBlackout datetime, @EndBlackout datetime)
AS
BEGIN
DECLARE @dbid nvarchar(1000);
Declare @STARTz varchar(30);
Declare @ENDz varchar(30);
Set @STARTz = (select Datepart(DW, @EndBlackout-1)) * (60*24)+(select Datepart(hh, @EndBlackout)) * (60) + (select Datepart(mi, @EndBlackout))
Set @ENDz = (select Datepart(DW, @StartBlackout-1)) * (60*24)+(select Datepart(hh, @StartBlackout)) * (60) + (select Datepart(mi, @StartBlackout))
DECLARE InstanceGatherCursor CURSOR FAST_FORWARD FOR
select id from cond with (nolock)
/*
where db_type =
'SQL SERVER'
--'Oracle'
--'Sybase'
--'DB2'
*/
OPEN InstanceGatherCursor;
FETCH NEXT FROM InstanceGatherCursor INTO @dbid;
DECLARE @Sql nvarchar(max);
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql =
N'Insert into CONBLACKOUT
select
isnull((select max(ScheduleID) + 1 from CONBLACKOUT with (nolock)),1) as ''SCHEDULEID'',
'+@STARTz+', '+@ENDz+', '+@dbid+''
EXEC(@sql)
FETCH NEXT FROM InstanceGatherCursor INTO @dbid;
END
CLOSE InstanceGatherCursor;
DEALLOCATE InstanceGatherCursor;
END
DESCRIPTION FOR MANUAL RUN
This procedure is the manual version for the one above if you don't want to have something stored on your SQL Server or need to quickly adjust what times you want Ignite to not monitor without logging into the application.
/*
Use the commented out top query below to retrieve the DBID of the instance you wish to manually enter a monitoring blackout date for. You can see what is
currently in the blackout table with the 2nd query. The date gets converted to the day of the week. So if you enter 2014-05-27 20:00 as the start time
for monitoring, it knows Tuesdays at 20:00 it will start the blackout period.
select ID from cond where Name =
select * from CONBLACKOUT
*/
Declare @StartBlackout datetime;
Declare @EndBlackout Datetime;
Declare @DBID smallint;
set @StartBlackout = '2014-05-27 20:00'
set @EndBlackout = '2014-05-11 00:00'
set @DBID = '1'
Insert into CONBLACKOUT
select
isnull((select max(ScheduleID) + 1 from CONBLACKOUT with (nolock)),1) as 'SCHEDULEID',
(select Datepart(DW, @EndBlackout-1)) * (60*24)+(select Datepart(hh, @EndBlackout)) * (60) + (select Datepart(mi, @EndBlackout)) as 'STOP_MOW',
(select Datepart(DW, @StartBlackout-1)) * (60*24)+(select Datepart(hh, @StartBlackout)) * (60) + (select Datepart(mi, @StartBlackout)) as 'START_MOW',
@DBID as 'DBID'