Automation examples

Example: Trigger to deduplicate the status table

create or replace trigger deduplication
group default_triggers
priority 1
comment 'Deduplication processing for ALERTS.STATUS'
before reinsert on alerts.status
for each row
begin
set old.Tally = old.Tally + 1;
set old.LastOccurrence = new.LastOccurrence;
set old.StateChange = getdate();
set old.InternalLast = getdate();
set old.Summary = new.Summary;
set old.AlertKey = new.AlertKey;
if ((old.Severity = 0) and (new.Severity > 0))
  then set old.Severity = new.Severity;
end if;
end;

Example: Trigger to deduplicate the details table

create or replace trigger
deduplicate_details
group default_triggers
priority 1
comment 'Deduplicate rows on alerts.details'
before reinsert on alerts.details
for each row
begin
cancel; -- Do nothing. Allow the row to be discarded
end;

Example: Trigger to clean the details table

create or replace trigger
clean_details_table
group default_triggers
priority 1
comment 'Housekeeping cleanup of ALERTS.DETAILS' every 60 seconds
begin
delete from alerts.details
  where Identifier not in (select Identifier from alerts.status);
end;

Example: Trigger to set the alerts table StateChange column

create or replace trigger state_change
group default_triggers
priority 1
comment 'State change processing for ALERTS.STATUS'
before update on alerts.status
for each row
begin
 set new.StateChange = getdate;
end;

Example: Trigger to delete clear rows

create or replace trigger delete_clears
group default_triggers
priority 1
comment 'Delete cleared alerts over 2 minutes old every 60 seconds'
every 60 seconds
begin
delete from alerts.status where Severity = 0 and StateChange < (getdate() - 120);
end;

Example: Trigger to send e-mail notifications for critical alerts

create or replace trigger mail_on_critical
group default_triggers
enabled false
priority 1
comment 'Send email about critical alerts that are 
unacknowledged after 30 minutes. NOTE This tool is 
UNIX specific unless an equivalent NT mailer is available.'
every 10 seconds
begin
				for each row critical in alerts.status where critical.Severity = 5 and 
				  critical.Grade < 2 and critical.Acknowledged = 0 and 
				  critical.LastOccurrence <= ( getdate() - (60*30) )
				begin
				  execute send_email( critical.Node, critical.Severity, 'Netcool Email', 
				  'root@localhost', critical.Summary, 'localhost');
				  update alerts.status via critical.Identifier set Grade=2;
				end;
end;

create or replace procedure send_email
(in node character(255), in severity integer, in subject character(255),
 in email character(255), in summary character(255), in hostname character(255))
executable '$NCHOME/omnibus/utils/nco_mail' host 'hostname' user 0 group 0
arguments '\'' + node + '\'', severity, '\'' + subject + '\'',
'\'' + email + '\'', '\'' + summary + '\'';

This example also shows how to pass text strings to an executable. Strings must be enclosed in quotation marks, and the quotation marks must be escaped with backslashes. All quotation marks in this example are single quotation marks.

Example: Trigger to truncate a file

When a user-defined or system signal is raised, this signal trigger truncates a file. This trigger is useful if you want files to be truncated when the ObjectServer starts, for example if files were archived after the last shutdown and are now no longer needed.

create trigger example_t
group default_triggers
priority 1
on signal example_signal
begin
    alter file example_file truncate;
end;

Where example_t is the name of the trigger, example_signal is the name of the signal, and example_file is the name of the file.

Example: Procedure to insert a journal entry for triggers

create or replace trigger 
trigger_name
group default_triggers
priority 10
before delete on alerts.status
for each row
begin
execute jinsert( old.Serial, %user.user_id, getdate(), 'string');
end;

In this automation, trigger_name is a variable representing the name of the trigger, and string is the journal entry text.

Example: Trigger to redirect client connections from one ObjectServer to another

You can use the redirect trigger to set the primary ObjectServer at run time in a failover pair setup. The following SQL script tells the clients to connect to a different acting primary ObjectServer.

As shown in the example, you can change which ObjectServer is the acting primary by running the set_acting_primary procedure against the ObjectServer you want to be the primary, and running the unset_acting_primary procedure against the ObjectServer that is currently the acting primary.

create table alerts.failover persistent
(
	Identifier	integer primary key,
	LastFailover	time,
	Reason		varchar(255),
	Summary		varchar(255)
);
insert into alerts.failover values (1, 0, '');
go

create trigger pre_connect_redirect_to_primary
group connection_watch
debug false
enabled true
priority 1
comment 'Redirect select clients to ActingPrimary'
on signal pre_connect
begin
	if (%signal.can_redirect = true and 
		get_prop_value('ActingPrimary') %= 'FALSE' and
		%signal.username != 'root' and
		%signal.process not in ('isql', 'GET_LOGIN_TOKEN', 'IMPACT', 'WEBGUI') and
		%signal.description <> 'failover_gate')
	then
		if (getservername = 'AGG_P')
		then
			alter system redirect connection 'AGG_B_PRIM';
		else
			alter system redirect connection 'AGG_P_PRIM';
		end if;
	end if;
end;
go

create trigger primary_toggle
group gateway_triggers
enabled true
priority 1
comment 'primary_only triggers should be active on ActingPrimary only'
on signal alter_property
when %signal.name = 'ActingPrimary'
begin
	if (get_prop_value('ActingPrimary') %= 'FALSE')
	then
		execute procedure automation_disable;
	
		for each row this_connection in catalog.connections where
			this_connection.AppName not in ('isql', 'GET_LOGIN_TOKEN', 'IMPACT', 'WEBGUI') and
			this_connection.AppDescription <> 'failover_gate'
		begin
			alter system drop connection this_connection.ConnectionID;
		end;
	else
		execute procedure automation_enable;
	end if;
end;
go

create procedure set_acting_primary()
begin
	update alerts.failover set 
		Reason = 'By user', 
		LastFailover = getdate,
		Summary = 'Became ActingPrimary';

	alter system set 'ActingPrimary' = true;
end;
go

create procedure unset_acting_primary()
begin
	update alerts.failover set 
		Reason = 'By user', 
		LastFailover = getdate,
		Summary = 'No longer ActingPrimary';

	alter system set 'ActingPrimary' = false;
end;
go

create trigger gateway_counterpart_down
group gateway_triggers
debug false
enabled true
priority 1
comment 'Countpart is down'
on signal gw_counterpart_down
when (get_prop_value('ActingPrimary') %= 'FALSE')
begin
	update alerts.failover set 
		Reason = 'CounterPartDown', 
		LastFailover = getdate,
		Summary = 'Became ActingPrimary while counterpart is down';

	alter system set 'ActingPrimary' = true;
end;
go

create trigger resync_finished
group gateway_triggers
debug false
enabled true
priority 1
comment 'Cease to be primary after true primary comes back online'
on signal gw_resync_finish
when (get_prop_value('ActingPrimary') %= 'TRUE')
begin
	for each fail in alerts.failover where fail.Reason = 'CounterPartDown'
	begin
		update alerts.failover set 
			Reason = 'CounterPartup', 
			LastFailover = getdate,
			Summary = 'No longer ActingPrimary after counterpart retstored';

		alter system set 'ActingPrimary' = false;
	end;
end;
go