Rem drv:
Rem
Rem $Header: default_notification_rules.sql 30-aug-2005.16:17:27 yaofeng Exp $
Rem
Rem default_notification_rules.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem default_notification_rules.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem yaofeng 08/30/05 - target type change
Rem gsbhatia 07/18/05 - Fix repmgr header
Rem gsbhatia 07/03/05 - New repmgr header impl
Rem rzazueta 11/01/04 - Add EM_REPOS_USER parameter
Rem yaofeng 10/27/04 - group support
Rem yaofeng 10/22/04 - API change
Rem yaofeng 09/02/04 - cleanup
Rem yaofeng 08/27/04 - notification rule API change
Rem yaofeng 09/23/03 - metric column change
Rem yaofeng 12/20/02 - support key values
Rem tjaiswal 10/02/02 - Changes for out of box notification rules
Rem tjaiswal 09/22/02 - Add more host metrics
Rem tjaiswal 09/14/02 - Changes to default notification rules
Rem tjaiswal 09/11/02 - tjaiswal_outofbox_notification_rules
Rem tjaiswal 09/10/02 - Created
Rem
Rem
Rem This script requires the following arguments
Rem 1. Name of the repository user
Rem
DEFINE EM_REPOS_USER = "&1"
Rem
Rem Create default notification rules for types - host, emrep, agent
Rem
BEGIN
DECLARE
current_user VARCHAR2(128);
current_em_user VARCHAR2(128);
-- variables for default notification rules
rule_name VARCHAR2(64);
rule_owner VARCHAR2(128);
rule_desc VARCHAR2(256);
rule_public NUMBER(1);
want_clears SMP_EMD_INTEGER_ARRAY;
want_warnings SMP_EMD_INTEGER_ARRAY;
want_critical_alerts SMP_EMD_INTEGER_ARRAY;
want_warning_succeed SMP_EMD_INTEGER_ARRAY;
want_warning_problem SMP_EMD_INTEGER_ARRAY;
want_critical_succeed SMP_EMD_INTEGER_ARRAY;
want_critical_problem SMP_EMD_INTEGER_ARRAY;
rule_policies SMP_EMD_STRING_ARRAY;
rule_job_names SMP_EMD_STRING_ARRAY;
rule_job_owners SMP_EMD_STRING_ARRAY;
rule_job_types SMP_EMD_STRING_ARRAY;
want_tgt_up NUMBER;
want_tgt_down NUMBER;
want_tgt_unreachable_start NUMBER;
want_tgt_unreachable_end NUMBER;
want_tgt_metric_err_start NUMBER;
want_tgt_metric_err_end NUMBER;
want_tgt_blkout_started NUMBER;
want_tgt_blkout_ended NUMBER;
target_type VARCHAR2(64);
rule_targets_array SMP_EMD_STRING_ARRAY;
rule_mnames_array SMP_EMD_STRING_ARRAY;
rule_mcolumns_array SMP_EMD_STRING_ARRAY;
rule_mindexes_array SMP_EMD_NVPAIR_ARRAY;
user_email VARCHAR2(128);
send_email NUMBER(1);
ignore_rca NUMBER(1);
rule_device_names SMP_EMD_STRING_ARRAY;
rule_device_types SMP_EMD_INTEGER_ARRAY;
device_not_avail_list SMP_EMD_STRING_ARRAY;
email_gateway_setup NUMBER;
BEGIN
-- Save the currently logged in user
current_user := mgmt_user.get_current_em_user();
-- Set em user context to EM_REPOS_USER. The default notification
-- rules will be owned by this user.
setemusercontext('&EM_REPOS_USER', MGMT_USER.OP_SET_IDENTIFIER);
current_em_user := mgmt_user.get_current_em_user();
rule_owner := current_em_user;
dbms_output.put_line( 'current em user: ' || current_em_user );
-- no email to be assigned with the rules at this stage ...
send_email := 0;
ignore_rca := 0;
-- no devices to be assigned with the rules ...
rule_device_names := SMP_EMD_STRING_ARRAY();
rule_device_types := SMP_EMD_INTEGER_ARRAY();
-- create rule "Host Availability and Critical States" for all targets
-- of type host
BEGIN
-- initialize the values
rule_name := 'Host Availability and Critical States';
rule_desc := 'System-generated notification rule for monitoring Hosts'' availability and critical metric statuses.';
rule_public := 1;
target_type := MGMT_GLOBAL.G_HOST_TARGET_TYPE;
rule_targets_array := SMP_EMD_STRING_ARRAY();
rule_targets_array.extend( 1 );
rule_targets_array( 1 ) := '%';
rule_mnames_array := SMP_EMD_STRING_ARRAY();
rule_mnames_array.extend( 12 );
rule_mnames_array( 1 ) := 'Filesystems';
rule_mnames_array( 2 ) := 'Load';
rule_mnames_array( 3 ) := 'Load';
rule_mnames_array( 4 ) := 'Response';
rule_mnames_array( 5 ) := 'DiskActivity';
rule_mnames_array( 6 ) := 'DiskActivity';
rule_mnames_array( 7 ) := 'Load';
rule_mnames_array( 8 ) := 'DiskActivity';
rule_mnames_array( 9 ) := 'Load';
rule_mnames_array( 10 ) := 'Load';
rule_mnames_array( 11 ) := 'Network';
rule_mnames_array( 12 ) := 'Load';
rule_mcolumns_array := SMP_EMD_STRING_ARRAY();
rule_mcolumns_array.extend( 12 );
rule_mcolumns_array( 1 ) := 'pctAvailable';
rule_mcolumns_array( 2 ) := 'cpuUtil';
rule_mcolumns_array( 3 ) := 'memUsedPct';
rule_mcolumns_array( 4 ) := 'Status';
rule_mcolumns_array( 5 ) := 'DiskActivityavserv';
rule_mcolumns_array( 6 ) := 'DiskActivityavwait';
rule_mcolumns_array( 7 ) := 'cpuIOWait';
rule_mcolumns_array( 8 ) := 'DiskActivitybusy';
rule_mcolumns_array( 9 ) := 'cpuLoad';
rule_mcolumns_array( 10 ) := 'pgScanRate';
rule_mcolumns_array( 11 ) := 'totalThroughput';
rule_mcolumns_array( 12 ) := 'swapUtil';
rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY();
rule_mindexes_array.extend( rule_mcolumns_array.COUNT );
FOR i in 1..rule_mindexes_array.COUNT
LOOP
rule_mindexes_array(i) := SMP_EMD_NVPAIR(
target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i),
'%');
END LOOP;
want_clears := SMP_EMD_INTEGER_ARRAY();
want_warnings := SMP_EMD_INTEGER_ARRAY();
want_critical_alerts := SMP_EMD_INTEGER_ARRAY();
want_warning_succeed := SMP_EMD_INTEGER_ARRAY();
want_warning_problem := SMP_EMD_INTEGER_ARRAY();
want_critical_succeed := SMP_EMD_INTEGER_ARRAY();
want_critical_problem := SMP_EMD_INTEGER_ARRAY();
want_clears.extend( 12 );
want_warnings.extend( 12 );
want_critical_alerts.extend( 12 );
want_warning_succeed.extend( 12 );
want_warning_problem.extend( 12 );
want_critical_succeed.extend( 12 );
want_critical_problem.extend( 12 );
for i in 1..want_clears.COUNT
LOOP
want_clears( i ) := 0;
want_warnings( i ) := 0;
want_critical_alerts( i ) := 1;
want_warning_succeed( i ) := 0;
want_warning_problem( i ) := 0;
want_critical_succeed( i ) := 0;
want_critical_problem( i ) := 0;
END LOOP;
rule_policies := SMP_EMD_STRING_ARRAY();
rule_job_names := SMP_EMD_STRING_ARRAY();
rule_job_owners := SMP_EMD_STRING_ARRAY();
rule_job_types := SMP_EMD_STRING_ARRAY();
want_tgt_up := 0;
want_tgt_down := 0;
want_tgt_unreachable_start := 1;
want_tgt_unreachable_end := 1;
want_tgt_metric_err_start := 0;
want_tgt_metric_err_end := 0;
want_tgt_blkout_started := 0;
want_tgt_blkout_ended := 0;
dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user );
mgmt_preferences.create_notification_rule(
rule_name,
rule_owner,
rule_desc,
rule_public,
target_type,
rule_targets_array,
null,
want_tgt_up,
want_tgt_down,
want_tgt_unreachable_start,
want_tgt_unreachable_end,
want_tgt_metric_err_start,
want_tgt_metric_err_end,
want_tgt_blkout_started,
want_tgt_blkout_ended,
ignore_rca,
rule_mnames_array,
rule_mcolumns_array,
rule_mindexes_array,
want_clears,
want_critical_alerts,
want_warnings,
want_warning_succeed,
want_warning_problem,
want_critical_succeed,
want_critical_problem,
rule_policies,
null,
null,
null,
null,
rule_job_names,
rule_job_owners,
rule_job_types,
null,
null,
null,
null,
null,
send_email,
rule_device_names,
rule_device_types,
device_not_avail_list,
email_gateway_setup );
dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM );
END;
-- create rule "Agents Unreachable" for all targets of type agent
BEGIN
-- initialize the values
rule_name := 'Agents Unreachable';
rule_desc := 'System-generated notification rule for monitoring Agents who lose contact with the Management Service due to network problems, host problems or Agents going down.';
rule_public := 1;
target_type := MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
rule_targets_array := SMP_EMD_STRING_ARRAY();
rule_targets_array.extend( 1 );
rule_targets_array( 1 ) := '%';
rule_mnames_array := SMP_EMD_STRING_ARRAY();
rule_mnames_array.extend( 1 );
rule_mnames_array( 1 ) := 'Response';
rule_mcolumns_array := SMP_EMD_STRING_ARRAY();
rule_mcolumns_array.extend( 1 );
rule_mcolumns_array( 1 ) := 'Status';
rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY();
rule_mindexes_array.extend( rule_mcolumns_array.COUNT );
FOR i in 1..rule_mindexes_array.COUNT
LOOP
rule_mindexes_array(i) := SMP_EMD_NVPAIR(
target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i),
'%');
END LOOP;
want_clears := SMP_EMD_INTEGER_ARRAY();
want_warnings := SMP_EMD_INTEGER_ARRAY();
want_critical_alerts := SMP_EMD_INTEGER_ARRAY();
want_warning_succeed := SMP_EMD_INTEGER_ARRAY();
want_warning_problem := SMP_EMD_INTEGER_ARRAY();
want_critical_succeed := SMP_EMD_INTEGER_ARRAY();
want_critical_problem := SMP_EMD_INTEGER_ARRAY();
want_clears.extend( 1 );
want_warnings.extend( 1 );
want_critical_alerts.extend( 1 );
want_warning_succeed.extend( 1 );
want_warning_problem.extend( 1 );
want_critical_succeed.extend( 1 );
want_critical_problem.extend( 1 );
for i in 1..want_clears.COUNT
LOOP
want_clears( i ) := 0;
want_warnings( i ) := 0;
want_critical_alerts( i ) := 0;
want_warning_succeed( i ) := 0;
want_warning_problem( i ) := 0;
want_critical_succeed( i ) := 0;
want_critical_problem( i ) := 0;
END LOOP;
rule_policies := SMP_EMD_STRING_ARRAY();
rule_job_names := SMP_EMD_STRING_ARRAY();
rule_job_owners := SMP_EMD_STRING_ARRAY();
rule_job_types := SMP_EMD_STRING_ARRAY();
want_tgt_up := 0;
want_tgt_down := 0;
want_tgt_unreachable_start := 1;
want_tgt_unreachable_end := 1;
want_tgt_metric_err_start := 0;
want_tgt_metric_err_end := 0;
want_tgt_blkout_started := 0;
want_tgt_blkout_ended := 0;
dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user );
mgmt_preferences.create_notification_rule(
rule_name,
rule_owner,
rule_desc,
rule_public,
target_type,
rule_targets_array,
null,
want_tgt_up,
want_tgt_down,
want_tgt_unreachable_start,
want_tgt_unreachable_end,
want_tgt_metric_err_start,
want_tgt_metric_err_end,
want_tgt_blkout_started,
want_tgt_blkout_ended,
ignore_rca,
rule_mnames_array,
rule_mcolumns_array,
rule_mindexes_array,
want_clears,
want_critical_alerts,
want_warnings,
want_warning_succeed,
want_warning_problem,
want_critical_succeed,
want_critical_problem,
rule_policies,
null,
null,
null,
null,
rule_job_names,
rule_job_owners,
rule_job_types,
null,
null,
null,
null,
null,
send_email,
rule_device_names,
rule_device_types,
device_not_avail_list,
email_gateway_setup );
dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM );
END;
-- create rule "Repository Operations Availability" for all targets of
-- type OMS and Repository target
BEGIN
-- initialize the values
rule_name := 'Repository Operations Availability';
rule_desc := 'System-generated notification rule for monitoring the availability of the DBMS jobs that are part of the Management Repository.';
rule_public := 1;
target_type := MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE;
rule_targets_array := SMP_EMD_STRING_ARRAY();
rule_targets_array.extend( 1 );
rule_targets_array( 1 ) := '%';
rule_mnames_array := SMP_EMD_STRING_ARRAY();
rule_mnames_array.extend( 1 );
rule_mnames_array( 1 ) := 'DBMS_Job_Status';
rule_mcolumns_array := SMP_EMD_STRING_ARRAY();
rule_mcolumns_array.extend( 1 );
rule_mcolumns_array( 1 ) := 'jobUpDown';
rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY();
rule_mindexes_array.extend( rule_mcolumns_array.COUNT );
FOR i in 1..rule_mindexes_array.COUNT
LOOP
rule_mindexes_array(i) := SMP_EMD_NVPAIR(
target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i),
'%');
END LOOP;
want_clears := SMP_EMD_INTEGER_ARRAY();
want_warnings := SMP_EMD_INTEGER_ARRAY();
want_critical_alerts := SMP_EMD_INTEGER_ARRAY();
want_warning_succeed := SMP_EMD_INTEGER_ARRAY();
want_warning_problem := SMP_EMD_INTEGER_ARRAY();
want_critical_succeed := SMP_EMD_INTEGER_ARRAY();
want_critical_problem := SMP_EMD_INTEGER_ARRAY();
want_clears.extend( 1 );
want_warnings.extend( 1 );
want_critical_alerts.extend( 1 );
want_warning_succeed.extend( 1 );
want_warning_problem.extend( 1 );
want_critical_succeed.extend( 1 );
want_critical_problem.extend( 1 );
for i in 1..want_clears.COUNT
LOOP
want_clears( i ) := 0;
want_warnings( i ) := 0;
want_critical_alerts( i ) := 1;
want_warning_succeed( i ) := 0;
want_warning_problem( i ) := 0;
want_critical_succeed( i ) := 0;
want_critical_problem( i ) := 0;
END LOOP;
rule_policies := SMP_EMD_STRING_ARRAY();
rule_job_names := SMP_EMD_STRING_ARRAY();
rule_job_owners := SMP_EMD_STRING_ARRAY();
rule_job_types := SMP_EMD_STRING_ARRAY();
want_tgt_up := 0;
want_tgt_down := 0;
want_tgt_unreachable_start := 0;
want_tgt_unreachable_end := 0;
want_tgt_metric_err_start := 0;
want_tgt_metric_err_end := 0;
want_tgt_blkout_started := 0;
want_tgt_blkout_ended := 0;
dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user );
mgmt_preferences.create_notification_rule(
rule_name,
rule_owner,
rule_desc,
rule_public,
target_type,
rule_targets_array,
null,
want_tgt_up,
want_tgt_down,
want_tgt_unreachable_start,
want_tgt_unreachable_end,
want_tgt_metric_err_start,
want_tgt_metric_err_end,
want_tgt_blkout_started,
want_tgt_blkout_ended,
ignore_rca,
rule_mnames_array,
rule_mcolumns_array,
rule_mindexes_array,
want_clears,
want_critical_alerts,
want_warnings,
want_warning_succeed,
want_warning_problem,
want_critical_succeed,
want_critical_problem,
rule_policies,
null,
null,
null,
null,
rule_job_names,
rule_job_owners,
rule_job_types,
null,
null,
null,
null,
null,
send_email,
rule_device_names,
rule_device_types,
device_not_avail_list,
email_gateway_setup );
dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM );
END;
-- create rule "Agent Upload Problems" for all targets of
-- type OMS and Repository target
BEGIN
-- initialize the values
rule_name := 'Agent Upload Problems';
rule_desc := 'System-generated notification rule for monitoring Agents who may have problems uploading data to the Management Service.';
rule_public := 1;
target_type := MGMT_GLOBAL.G_AGENT_TARGET_TYPE;
rule_targets_array := SMP_EMD_STRING_ARRAY();
rule_targets_array.extend( 1 );
rule_targets_array( 1 ) := '%';
rule_mnames_array := SMP_EMD_STRING_ARRAY();
rule_mnames_array.extend( 1 );
rule_mnames_array( 1 ) := 'Targets_not_uploading';
rule_mcolumns_array := SMP_EMD_STRING_ARRAY();
rule_mcolumns_array.extend( 1 );
rule_mcolumns_array( 1 ) := 'count';
rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY();
rule_mindexes_array.extend( rule_mcolumns_array.COUNT );
FOR i in 1..rule_mindexes_array.COUNT
LOOP
rule_mindexes_array(i) := SMP_EMD_NVPAIR(
target_type || '|' || rule_mnames_array(i) || '|' || rule_mcolumns_array(i),
'%');
END LOOP;
want_clears := SMP_EMD_INTEGER_ARRAY();
want_warnings := SMP_EMD_INTEGER_ARRAY();
want_critical_alerts := SMP_EMD_INTEGER_ARRAY();
want_warning_succeed := SMP_EMD_INTEGER_ARRAY();
want_warning_problem := SMP_EMD_INTEGER_ARRAY();
want_critical_succeed := SMP_EMD_INTEGER_ARRAY();
want_critical_problem := SMP_EMD_INTEGER_ARRAY();
want_clears.extend( 1 );
want_warnings.extend( 1 );
want_critical_alerts.extend( 1 );
want_warning_succeed.extend( 1 );
want_warning_problem.extend( 1 );
want_critical_succeed.extend( 1 );
want_critical_problem.extend( 1 );
for i in 1..want_clears.COUNT
LOOP
want_clears( i ) := 0;
want_warnings( i ) := 0;
want_critical_alerts( i ) := 1;
want_warning_succeed( i ) := 0;
want_warning_problem( i ) := 0;
want_critical_succeed( i ) := 0;
want_critical_problem( i ) := 0;
END LOOP;
rule_policies := SMP_EMD_STRING_ARRAY();
rule_job_names := SMP_EMD_STRING_ARRAY();
rule_job_owners := SMP_EMD_STRING_ARRAY();
rule_job_types := SMP_EMD_STRING_ARRAY();
want_tgt_up := 0;
want_tgt_down := 0;
want_tgt_unreachable_start := 0;
want_tgt_unreachable_end := 0;
want_tgt_metric_err_start := 0;
want_tgt_metric_err_end := 0;
want_tgt_blkout_started := 0;
want_tgt_blkout_ended := 0;
dbms_output.put_line( 'Creating rule: ' || rule_name || ' for user: ' || current_em_user );
mgmt_preferences.create_notification_rule(
rule_name,
rule_owner,
rule_desc,
rule_public,
target_type,
rule_targets_array,
null,
want_tgt_up,
want_tgt_down,
want_tgt_unreachable_start,
want_tgt_unreachable_end,
want_tgt_metric_err_start,
want_tgt_metric_err_end,
want_tgt_blkout_started,
want_tgt_blkout_ended,
ignore_rca,
rule_mnames_array,
rule_mcolumns_array,
rule_mindexes_array,
want_clears,
want_critical_alerts,
want_warnings,
want_warning_succeed,
want_warning_problem,
want_critical_succeed,
want_critical_problem,
rule_policies,
null,
null,
null,
null,
rule_job_names,
rule_job_owners,
rule_job_types,
null,
null,
null,
null,
null,
send_email,
rule_device_names,
rule_device_types,
device_not_avail_list,
email_gateway_setup );
dbms_output.put_line( 'email gateway setup: ' || email_gateway_setup );
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line( 'Exception occurred while creating rule: ' || rule_name || ' for user: ' || current_em_user || ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM );
END;
-- now reset the user
setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);
EXCEPTION
WHEN OTHERS
THEN
setemusercontext(current_user, MGMT_USER.OP_SET_IDENTIFIER);
END;
END;
/