We will use the “Test alert” as example code, as its only purpose is to generate alarms and warnings, but uses many underlying functions. We go through it in steps, and include the full routine at the end.
For historical reasons some code will refer to terms like “task” or “check”. Task is jobs without status, and check is jobs with status.
As checks are an expansion of a task, both tasks and checks are found in the dbw_tasks table, but only checks are in the dbw_checks table. The dbw_checks table only keeps additional data not stored in dbw_tasks.
create procedure dbw_test_alert @taskID INT The main procedure gets the taskid as input when it gets executed. This is used to lookup in the underlying framework to get hold of parameters and updating status.
as
begin
set nocount on
declare @status intIt is typical to declare an int value for the return status. If this job wants to update status, this argument needs to be part of the call of the dbw_updateCheckValues procedure. 0 is OK, 1 is Warning and 2 is Alarm.
declare @now_date datetime
declare @exec_info VARCHAR(1000)At the end of this procedure we call “dbw_updateCheckValues taskID, status, @exec_info”. This routine writes data in the underlying framework. This data is then again displayed in the software, and used for alerts.
We declare the exec_info variable. The data in this variable will end up as the details field on the job in the monitoring module.
We continue with general code for this procedure..
declare @last_run_alarm datetime
declare @between_alarm int
declare @send_alarm int
declare @alarm_message varchar(1000)
declare @alarm_disable int
declare @last_run_warning datetime
declare @between_warning int
declare @send_warning int
declare @warning_message varchar(1000)
declare @warning_disable int
declare @ok_message varchar(1000)These are just other declarations for the function of this job.
BEGIN TRYThis is to run the procedure with exception handling
set @status = 0
set @now_date = getdate()
set @send_alarm = 0
set @alarm_disable = 0
set @send_warning = 0
set @warning_disable = 0These is some variables used in the function of this job.
select @between_warning = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between warnings') and TASK_ID=@taskID
select @between_alarm = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between alarms') and TASK_ID=@taskID
select @alarm_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Alarm message') and TASK_ID=@taskID
select @warning_message= VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Warning message') and TASK_ID=@taskID
select @ok_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Ok message') and TASK_ID=@taskID
select @warning_disable = convert(int,CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable warnings') and TASK_ID=@taskID
select @alarm_disable =convert(int, CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable alarms') and TASK_ID=@taskIDThis is how we get data from parameters. The configurable parameters in the job, are stored in the framework in the “DBW_PARAMETERS” table. We retrieve values from this by using the parameter name and the task_id we got at execution time. “DBW_TASKS” table provides the mapping between task_id and the name of the job.
select @last_run_alarm = max(histr_date) from dbw_test_alert_history where type_message like @alarm_message
select @last_run_warning = max(histr_date) from dbw_test_alert_history where type_message like @warning_message
if @last_run_alarm is null set @last_run_alarm = getdate() - 9999
if @last_run_warning is null set @last_run_warning = getdate() - 9999
if @now_date > @last_run_warning + @between_warning/24./60.
begin
if @send_alarm = 0
begin
if @warning_disable = 0
begin
set @send_warning = 1
end
end
end
if @now_date > @last_run_alarm + @between_alarm/24./60.
begin
if @send_warning = 0
begin
if @alarm_disable = 0
begin
set @send_alarm = 1
end
end
end
if @send_warning > 0
begin
insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @warning_message)
set @status = 1
set @exec_info = @warning_message
end
if @send_alarm > 0
begin
insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @alarm_message)
set @status = 2
set @exec_info = @alarm_message
end
if @send_warning = 0
begin
if @send_alarm = 0
begin
set @status = 0
set @exec_info = @ok_message
end
end
delete from dbw_test_alert_history where histr_date < getdate() -30This is the main function section of the job.
exec dbw_updateCheckValues @taskID, @status, @exec_infoThis call is essential to update the result of the job. dbw_updateCheckValues takes the task_id(which we get as input when this procedure is executed), an int, here status (which can be 0 for OK, 1 for Warning, and 2 for Alarm), and a string, here exec_info where we add short detailed information of the status of the job. Keep this short, under 1000 characters.
END TRY
BEGIN CATCH
set @exec_info = 'Exception, ' + ERROR_MESSAGE()
exec dbw_updateCheckValues @taskID, 0 , @exec_info
END CATCH
end It is recommended, for stability, to add exception handling to the job, in case something goes wrong. Here we try to add some information about the exception, as part of the details, so it will be visible in the graphical interface.
That is the layout of a main procedure on MS SQL Server. All the jobs provided are open, and its possible to read the code to get inpiration to new jobs.
Complete code
create procedure dbw_test_alert @taskID INT
as
begin
set nocount on
declare @status int
declare @now_date datetime
declare @exec_info VARCHAR(1000)
declare @last_run_alarm datetime
declare @between_alarm int
declare @send_alarm int
declare @alarm_message varchar(1000)
declare @alarm_disable int
declare @last_run_warning datetime
declare @between_warning int
declare @send_warning int
declare @warning_message varchar(1000)
declare @warning_disable int
declare @ok_message varchar(1000)
BEGIN TRY
set @status = 0
set @now_date = getdate()
set @send_alarm = 0
set @alarm_disable = 0
set @send_warning = 0
set @warning_disable = 0
select @between_warning = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between warnings') and TASK_ID=@taskID
select @between_alarm = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between alarms') and TASK_ID=@taskID
select @alarm_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Alarm message') and TASK_ID=@taskID
select @warning_message= VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Warning message') and TASK_ID=@taskID
select @ok_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Ok message') and TASK_ID=@taskID
select @warning_disable = convert(int,CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable warnings') and TASK_ID=@taskID
select @alarm_disable =convert(int, CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable alarms') and TASK_ID=@taskID
select @last_run_alarm = max(histr_date) from dbw_test_alert_history where type_message like @alarm_message
select @last_run_warning = max(histr_date) from dbw_test_alert_history where type_message like @warning_message
if @last_run_alarm is null set @last_run_alarm = getdate() - 9999
if @last_run_warning is null set @last_run_warning = getdate() - 9999
if @now_date > @last_run_warning + @between_warning/24./60.
begin
if @send_alarm = 0
begin
if @warning_disable = 0
begin
set @send_warning = 1
end
end
end
if @now_date > @last_run_alarm + @between_alarm/24./60.
begin
if @send_warning = 0
begin
if @alarm_disable = 0
begin
set @send_alarm = 1
end
end
end
if @send_warning > 0
begin
insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @warning_message)
set @status = 1
set @exec_info = @warning_message
end
if @send_alarm > 0
begin
insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @alarm_message)
set @status = 2
set @exec_info = @alarm_message
end
if @send_warning = 0
begin
if @send_alarm = 0
begin
set @status = 0
set @exec_info = @ok_message
end
end
delete from dbw_test_alert_history where histr_date < getdate() -30
exec dbw_updateCheckValues @taskID, @status, @exec_info
END TRY
BEGIN CATCH
set @exec_info = 'Exception, ' + ERROR_MESSAGE()
exec dbw_updateCheckValues @taskID, 0 , @exec_info
END CATCH
end End of procedure
Post your comment on this topic.