Preventing core tables to grow beyond limit

The hk_row_counter trigger monitors the following core tables to prevent them exceeding the predefined size: alerts.status, alerts.journal, and alerts.details. The trigger periodically checks the size of the tables and deletes data from any of the tables where the set threshold is exceeded. As a result, the tables do not grow beyond the set number of rows.

1. Optional: If not already available, create the master.properties table on your primary Aggregation ObjectServer using the following command:
   • $OMNIHOME/bin/nco_sql -server Primary Aggregation ObjectServer -user root -password password < $OMNIHOME/extensions/housekeeping/create_master.properties.sql
   • "%OMNIHOME%\bin\isql" -S Primary Aggregation ObjectServer -U root -i %OMNIHOME%\extensions\housekeeping\create_master.properties.sql

   For example:

   $OMNIHOME/bin/nco_sql -server AGG_P -user root -password netcool < $OMNIHOME/extensions/housekeeping/create_master.properties.sql

2. Optional: Enable the default corrective actions to take for the tables, or add your own. To enable the default actions, uncomment the following lines for the respective table as follows:

   Make a copy of $OMNIHOME/extensions/housekeeping/rowcountmonitor.sql file, and using your copy, uncomment the lines that are not all upper case:

   • For the alerts.status table actions:

     ------------------------------------------
     		-- alerts.status REMEDIAL ACTION GOES HERE
     		------------------------------------------
     		-- DEFAULT:
     		-- GRADUALLY LOWER SEVERITY OF EVENTS LESS THAN 5
     		-- WHICH WILL EVENTUALLY RESULT IN DELETION
     		-- - EXCLUDE Type 2 RESOLUTION EVENTS
     		-- - EXCLUDE SELF-MONITORING EVENTS
     		-- - EXCLUDE SYNTHETIC PARENT EVENTS
     
     		-- THE BELOW SQL IS PROVIDED AS AN EXAMPLE - USE WITH CARE!
     
     --		-- ITERATE OVER ALL TARGET EVENTS
     --		for each row thisrow in alerts.status where
     --			thisrow.Type != 2 and
     --			thisrow.Class != 99999 and
     --			thisrow.Severity in (1, 2, 3, 4) and
     --			thisrow.AlertGroup  not in (
     --				'SiteNameParent',
     --				'ScopeIDParent',
     --				'Synthetic Event - Parent')
     --		begin
     --
     --			-- ADD A JOURNAL ENTRY TO INDICATE THE EVENT WAS
     --			-- DE-ESCALATED BY THIS TRIGGER
     --			EXECUTE jinsert(thisrow.Serial, 0, getdate(),
     --				'Event de-escalated from ' + to_char(thisrow.Severity) +
     --				' to ' + to_char(thisrow.Severity - 1) +
     --				' by trigger: housekeeping_row_counter');
     --
     --			-- LOWER THE SEVERITY BY ONE
     --			set thisrow.Severity = thisrow.Severity - 1;
     --
     --		end;

   • For the alerts.journal table actions:

     ------------------------------------------
     		-- alerts.details REMEDIAL ACTION GOES HERE
     		------------------------------------------
     		-- DEFAULT:
     		-- DELETE JOURNALS OLDER THAN 5 DAYS (432000 SECONDS)
     
     		-- THE BELOW SQL IS PROVIDED AS AN EXAMPLE - USE WITH CARE!
     
     --		delete from alerts.journal where Serial in (
     --			select Serial from alerts.status where Severity < 5);

   • For the alerts.details table actions:

     ------------------------------------------
     		-- alerts.details REMEDIAL ACTION GOES HERE
     		------------------------------------------
     		-- DEFAULT:
     		-- DELETE ALL DETAILS FOR EVENTS LESS THAN CRITICAL IN SEVERITY
     
     		-- THE BELOW SQL IS PROVIDED AS AN EXAMPLE - USE WITH CARE!
     
     --		delete from alerts.details where Identifier in (
     --			select Identifier from alerts.status where Severity < 5);

3. Install the trigger into your primary Aggregation ObjectServers using the rowcountmonitor.sql command, or the copy of it you edited it in the previous step:
   • $OMNIHOME/bin/nco_sql -server Primary Aggregation ObjectServer -user root -password password < $OMNIHOME/extensions/housekeeping/rowcountmonitor.sql
   • "%OMNIHOME%\bin\isql" -S Primary Aggregation ObjectServer -U root -i %OMNIHOME%\extensions\housekeeping\rowcountmonitor.sql

   For example:

   $OMNIHOME/bin/nco_sql -server AGG_P -user root -password netcool < $OMNIHOME/extensions/housekeeping/rowcountmonitor.sql

   Note: You can remove the trigger using the same procedure and running the $OMNIHOME/extensions/housekeeping/rowcountmonitor_remove.sql scripts.
4. The default threshold values for the number of rows are stored in the master.properties table. You can modify the default values using the Netcool/OMNIbus Administrator , for more information see Using Netcool/OMNIbus Administrator to configure ObjectServers .

   Table 1. Default threshold values for the number of rows

   Core table name	Threshold	Property name in master.properties
   alerts.status	100 000 rows	HKThresholdStatus
   alerts.journal	150 000 rows	HKThresholdJournal
   alerts.details	150 000 rows	HKThresholdDetails

5. Enable the hk_row_counter trigger and the housekeeping_triggers trigger group in one of the following ways:
   • Using the Netcool/OMNIbus Administrator : Go to the Automation tab, click Triggers, then right-click hk_row_counter, and select Edit Trigger to select Enabled. Click OK to save. To enable the housekeeping_triggers trigger group, go to the Automation tab, click Trigger Groups, and follow the same procedure.

     For more information, see Using Netcool/OMNIbus Administrator to configure ObjectServers.

   • Using the nco_sql command prompt: You can also enable individual triggers using the ALTER TRIGGER command, and enable trigger groups using the ALTER TRIGGER GROUP command. For example, to enable the trigger and then the trigger group:

     1> alter trigger hk_row_counter set enabled TRUE;
     2> go
     (0 rows affected)
     1> 

     1> alter trigger group housekeeping_triggers set enabled TRUE;
     2> go
     (0 rows affected)
     1> 

     For more information see ALTER TRIGGER and ALTER TRIGGER GROUP.

   Note: The trigger group and the individual housekeeping triggers are disabled by default. Only enable the housekeeping triggers in a live production environment after successful load and functional testing is completed in a representative development or test environment.