Rem drv: Rem Rem $Header: notification_pkgbodys.sql 18-jul-2007.18:39:38 minfan Exp $ Rem Rem notification_pkgbodys.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem notification_pkgbodys.sql Rem Rem DESCRIPTION Rem This is the implementation of the Notification Manager for Rem Oracle Enterprise Manager Rem NOTES Rem The Notification Manager is implemented using one DBMS JOB that calls Rem the CHECK_FOR_SEVERITIES procedure and another that calls the Rem CHECK_FOR_JOBS procedure. This procedures check for new notification Rem source events, match rules to them and queues notifications that are Rem delivered by one or more OMSs Rem Rem RCA: for 10gR2 RCA tasks are added to the set of notification tasks Rem handled by this subsystem. If the overhead of handling these Rem tasks impacts notifications adversely then they should be removed. Rem Rem MODIFIED (MM/DD/YY) Rem minfan 07/18/07 - bug 6260471 Rem denath 06/27/07 - Fix 5983944.proc ping devices modified for Rem checking device in ping status along with down Rem status and create a ping notification. Rem neearora 07/13/07 - Bug 6122386 Rem denath 07/05/07 - Bug fix 6082882.modified Rem queue_metric_notification. passed Rem violation.target_guid to devices cursor and use Rem in place of c.target_guid while joining with Rem MGMT_METRICS_COMPOSITE_KEYS.target_guid for Rem wildcard key value metrics to get picked up. Rem neearora 06/21/07 - added validations in set_rule_repeat Rem neearora 06/20/07 - bug 6142074 Rem minfan 06/15/07 - backport of bug 5852652 Rem shnavane 04/06/07 - Fix bug #5850762 Rem shnavane 06/13/07 - Backport shnavane_bug-5850762 from main Rem neearora 06/09/07 - bug 6122386 Rem neearora 05/26/07 - bug 6067642 Rem neearora 05/03/07 - added API to get set global repeat settings Rem smalathe 04/23/07 - Bug 5881705: Add a debug message Rem neearora 03/27/07 - added procedure QUEUE_NOTIF_INPUT Rem denath 07/26/06 - Fix Bug 5383506.Added check for policy violation Rem warning and information notifiications to be Rem sent in queue_metric_notifications. Rem denath 03/18/07 - Backport denath_bug-5383506 from main Rem neearora 06/24/06 - bug 5045575. Modify GET_JOB_STATE_CHANGE_DETAILS Rem to select target on the basis of execution id Rem neearora 07/20/06 - Backport neearora_bug-5045575 from main Rem rmaggarw 06/25/06 - bug5347519: sort by checkuser Rem rmaggarw 07/10/06 - Backport rmaggarw_bug-5347519 from main Rem neearora 10/21/05 - Added support for java notifications Rem neearora 10/21/05 - Added support for java notifications Rem rmaggarw 11/28/05 - use this_date instead of sysdate in Rem check_for_severities Rem rmaggarw 12/05/05 - Backport rmaggarw_bug-4764102 from main Rem dcawley 11/25/05 - Backport dcawley_bug-4607171 from main Rem dcawley 09/13/05 - Default job type Rem dcawley 11/25/05 - Backport dcawley_bug-4602660 from main Rem dcawley 10/17/05 - Change summary message Rem dcawley 11/25/05 - Remove check for OMS Rem dcawley 09/24/05 - Remove plsql check when queuing Rem dcawley 09/09/05 - Improve performance of rule matching Rem dcawley 08/31/05 - Get job type label Rem dcawley 08/05/05 - Use metric name for CAs not label Rem dcawley 08/04/05 - Include occurred timestamp for jobs and cas Rem dcawley 08/04/05 - Add logging Rem dcawley 08/03/05 - Fix index for getting keys Rem rmaggarw 07/29/05 - remove commit from failover Rem dcawley 07/13/05 - Add callback for cleaning up keys Rem smalathe 07/13/05 - Bug4487356: Use Select count Rem scgrover 07/07/05 - add extended sql trace Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem dcawley 06/30/05 - Include aborted state for ca Rem dcawley 06/21/05 - Change annotation for ca Rem dcawley 06/16/05 - Do not include rca in summary Rem dcawley 04/04/05 - Fix sql injection problems Rem dcawley 03/24/05 - Set additional gateway parameters Rem dcawley 03/24/05 - Sepcify escape character for rule matching Rem dcawley 03/22/05 - Fix cleanup Rem dcawley 03/15/05 - Support wild cards for composites Rem jriel 03/15/05 - queue rca task with normal type Rem dcawley 03/10/05 - Reorder columns Rem pkantawa 03/09/05 - exclude jobs in delete pending state Rem dcawley 02/24/05 - Increase queue name size Rem dcawley 02/24/05 - Change get repository owner Rem dcawley 02/24/05 - Fix timezone problem when logging delivery times Rem gsbhatia 02/13/05 - updating repmgr header Rem rzazueta 02/04/05 - Prepend queue_name with schema name Rem gsbhatia 02/07/05 - updating repmgr header Rem ramalhot 02/02/05 - changed signature for handle_target_deleted Rem dcawley 01/28/05 - Use policy name Rem dcawley 01/11/05 - Allow registration without e-mail addresses Rem ktlaw 01/11/05 - add repmgr header Rem dcawley 01/11/05 - Rename callback Rem dcawley 01/06/05 - Add target deletion callback Rem dcawley 01/10/05 - Upper case job details Rem dcawley 01/05/05 - Fix test routine for plsql method Rem dcawley 12/22/04 - Truncate minutes when checking schedule Rem dcawley 12/20/04 - Catch errors with timezone Rem dcawley 12/20/04 - Fix rule for rca Rem dcawley 12/16/04 - Add RCA notification type Rem dcawley 12/14/04 - Include policy flags when matching rules Rem dcawley 12/09/04 - Change annotation values Rem dcawley 12/07/04 - Delete job configs Rem jriel 11/18/04 - don't filter RCA during cleanup Rem dcawley 11/16/04 - Pass back nlsid for violation details Rem dcawley 10/28/04 - Cleanup old administrative messages Rem dcawley 10/27/04 - Add api for deleting a specific configuration Rem jriel 10/11/04 - only handle new severities Rem dcawley 10/11/04 - RCA changes to rule Rem jriel 09/28/04 - add proc to queue rca directly Rem dcawley 09/27/04 - Queue corrective action Rem dcawley 09/15/04 - Switch to violations table Rem dcawley 09/01/04 - Use job status buckets Rem jriel 08/31/04 - add target guid to notification for RCA Rem dcawley 08/19/04 - Register OS of OMS Rem dcawley 08/11/04 - Do not notify user's without target priv Rem jriel 08/06/04 - add queue rca Rem jriel 07/20/04 - add RCA Rem dcawley 07/22/04 - Allow states per metric Rem dcawley 07/21/04 - Change target table for jobs Rem dcawley 07/15/04 - Add corrective actions Rem dcawley 07/12/04 - Add procedure for getting job state details Rem dcawley 07/07/04 - Increase user name size Rem dcawley 06/29/04 - Remove tracing Rem dcawley 06/14/04 - Changes for 10gR2 Rem aholser 05/13/04 - enable method performance logging Rem dcawley 04/15/04 - remove send mail Rem dcawley 03/25/04 - Add timezone to schedule Rem yaofeng 02/07/04 - device name length Rem yaofeng 02/05/04 - use target name Rem dcawley 01/27/04 - Fix schedule for when Sunday is first day Rem dcawley 01/26/04 - Log problems with metric versioning Rem dcawley 12/19/03 - Remove retry recirds Rem dcawley 11/25/03 - Do not send emails when there is no schedule Rem yaofeng 11/12/03 - show date in NLS format Rem yaofeng 11/11/03 - don't use . * Rem dcawley 10/16/03 - Fix host label lookup Rem dcawley 09/25/03 - Add key column name Rem dcawley 09/18/03 - Add key value guid for plsql notifications Rem dcawley 09/08/03 - Use timezone_region Rem dcawley 09/03/03 - Annotations should be target timezone Rem dcawley 08/27/03 - Use upper case for schedule owner Rem dcawley 08/27/03 - Clear old parameters when editing a command Rem rpinnama 07/25/03 - Rem rpinnama 07/25/03 - Move queue initialization to post_creation Rem dcawley 07/23/03 - Log device specific delivery times Rem dcawley 07/18/03 - Change annotations Rem dcawley 07/09/03 - Move queue initialization Rem dcawley 06/26/03 - Add UNREGISTER_OMS Rem dcawley 06/25/03 - Check for missing guids Rem dcawley 05/21/03 - Update for moving delivery to OMS Rem CREATE OR REPLACE PACKAGE BODY EMD_NOTIFICATION IS -- Queues sizes, these are read from MGMT_PARAMETERS NUM_EMAIL_QUEUES number := DEFAULT_NOTIFY_EMAIL_QUEUES; NUM_OSCMD_QUEUES number := DEFAULT_NOTIFY_OSCMD_QUEUES; NUM_PLSQL_QUEUES number := DEFAULT_NOTIFY_PLSQL_QUEUES; NUM_SNMP_QUEUES number := DEFAULT_NOTIFY_SNMP_QUEUES; NUM_RCA_QUEUES number := DEFAULT_NOTIFY_RCA_QUEUES; NUM_JAVA_QUEUES number := DEFAULT_NOTIFY_JAVA_QUEUES; G_DEV_PING_STATE_TIMEOUT number := DEF_DEV_PING_STATE_TIMEOUT; -- A cache of device availability. Entries are of the form: -- AVAILABLE_DEVICES SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); UNAVAILABLE_DEVICES SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); -- Work out what monday is using a well known data, so schedule starts -- can be calculated MONDAY NUMBER := TO_CHAR(TO_DATE('05-01-2004', 'dd-mm-yyyy'), 'D'); -- Performance data NUM_EMAILS_QUEUED number := 0; NUM_OSCMDS_QUEUED number := 0; NUM_PLSQLS_QUEUED number := 0; NUM_SNMPS_QUEUED number := 0; NUM_JAVA_QUEUED number := 0; TOTAL_ENQUEUE_TIME number := 0; NUM_JOB_EMAILS_QUEUED number := 0; NUM_JOB_OSCMDS_QUEUED number := 0; NUM_JOB_PLSQLS_QUEUED number := 0; NUM_JOB_SNMPS_QUEUED number := 0; NUM_JOB_JAVA_QUEUED number := 0; NUM_RCAS_QUEUED number := 0; TOTAL_JOB_ENQUEUE_TIME number := 0; OLDERR number := 0; OLDERRTIME DATE; G_RECURSIVE_CLEANUP BOOLEAN := FALSE; -- Repository owner G_REPOSITORY_OWNER VARCHAR2(30) := NULL; G_QUEUE_NAME VARCHAR2(50) := NULL; G_NOTIFY_INPUT_Q_NAME VARCHAR2(50) := NULL; -- Logging context G_LOG_CONTEXT VARCHAR2(15) := 'NOTIFICATION'; -- The escape character for matching strings in rules G_ESC VARCHAR2(1) := '\'; FUNCTION GET_MILLISEC_FROM_INTERVAL(i INTERVAL DAY TO SECOND) RETURN NUMBER IS duration_sec NUMBER; BEGIN duration_sec := (extract (day from i)) * 24 * 60 * 60 + (extract (hour from i)) * 60 * 60 + (extract (minute from i)) * 60 + (extract (second from i)); return duration_sec * 1000; END GET_MILLISEC_FROM_INTERVAL; FUNCTION IS_WINDOWS_PROGRAM(v_program IN VARCHAR2) RETURN BOOLEAN IS l_program VARCHAR2(512); BEGIN l_program := UPPER(v_program); -- Check if this is a Windows program IF INSTR(l_program, '\') > 0 OR SUBSTR(l_program, 2, 1) = ':' OR INSTR(l_program, '.BAT') > 0 OR INSTR(l_program, '.CMD') > 0 OR INSTR(l_program, '.EXE') > 0 THEN RETURN TRUE; END IF; RETURN FALSE; END IS_WINDOWS_PROGRAM; -- PURPOSE -- Procedure to set a configuration value PROCEDURE SET_CONFIG_VALUE(p_name IN VARCHAR2, p_value IN VARCHAR2) IS BEGIN UPDATE mgmt_parameters SET parameter_value = p_value WHERE parameter_name = p_name; IF (SQL%ROWCOUNT = 0) THEN INSERT INTO mgmt_parameters (parameter_name, parameter_value) VALUES (p_name, p_value); END IF; END SET_CONFIG_VALUE; -- PURPOSE -- Function to check the availabilty of a device based on its notification -- schedule FUNCTION DEVICE_AVAILABLE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_time_to_check IN DATE DEFAULT SYSDATE) RETURN BOOLEAN IS dev_type NUMBER; sd DATE; disable_s DATE; disable_e DATE; num_wks NUMBER := 1; hour NUMBER; day NUMBER; adj_hour NUMBER; available VARCHAR2(1); log_msg VARCHAR2(512); cache_entry VARCHAR2(256); timezone_rgn VARCHAR2(64); adj_time_to_check DATE := v_time_to_check; BEGIN -- Do not check schedule for non-email devices BEGIN SELECT type INTO dev_type FROM MGMT_NOTIFY_DEVICES WHERE device_name = v_device_name AND profile_name = v_profile_name; IF dev_type <> LONG_EMAIL_DEVICE_TYPE AND dev_type <> SHORT_EMAIL_DEVICE_TYPE THEN RETURN TRUE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- No device found so return false RETURN FALSE; END; -- Check the cache BEGIN SELECT column_value INTO cache_entry FROM TABLE(CAST(AVAILABLE_DEVICES AS SMP_EMD_STRING_ARRAY)) WHERE column_value = v_device_name || v_profile_name; RETURN TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN SELECT column_value INTO cache_entry FROM TABLE(CAST(UNAVAILABLE_DEVICES AS SMP_EMD_STRING_ARRAY)) WHERE column_value = v_device_name || v_profile_name; RETURN FALSE; EXCEPTION WHEN NO_DATA_FOUND THEN -- this is the first time checking this device's schedule NULL; END; END; -- Get the start date, length and disable dates of the user's schedule BEGIN SELECT start_date, num_weeks, disable_start, disable_end, timezone_region INTO sd, num_wks, disable_s, disable_e, timezone_rgn FROM MGMT_NOTIFY_SCHEDULES WHERE schedule_owner = v_profile_name AND schedule_name = ' '; EXCEPTION WHEN NO_DATA_FOUND THEN -- No schedule found so return false RETURN FALSE; END; -- Adjust the time to check based on the user's timezone adj_time_to_check := MGMT_GLOBAL.ADJUST_TZ(v_time_to_check, TO_CHAR(SYSTIMESTAMP,'TZR'), timezone_rgn); -- Make sure we are after the start date IF adj_time_to_check < sd THEN UNAVAILABLE_DEVICES.EXTEND; UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || v_profile_name; RETURN FALSE; END IF; -- Make sure the schedule is not disabled IF SYSDATE >= disable_s AND SYSDATE <= disable_e THEN UNAVAILABLE_DEVICES.EXTEND; UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || v_profile_name; RETURN FALSE; END IF; -- Get the day of the start date. Note Sunday is 1, Monday 2 etc. The UI -- always shows Monday as the first day even if the start date happens to -- be a Tuesday. So the effective start date of a schedule is the preceeding -- Monday since the first 24 characters of the schedule array in -- MGMT_NOTIFY_DEV_SCHEDULES will be for the Monday day := TO_CHAR(sd, 'D'); IF day >= MONDAY THEN sd := sd - (day - MONDAY); ELSE sd := sd - (7 - (MONDAY - day)); END IF; -- Remove the minutes from the time to check to avoid any rounding errors adj_time_to_check := TRUNC(adj_time_to_check, 'HH'); -- Get the hour hour := ROUND((adj_time_to_check - sd) * 24); -- If the current hour is greater than the number of hours in the schedule IF hour > (num_wks * 7 * 24) THEN adj_hour := mod(hour, (num_wks * 7 * 24)) + 1; ELSE adj_hour := hour + 1; END IF; BEGIN -- Check the schedule for the device SELECT SUBSTR(schedule, adj_hour, 1) INTO available FROM MGMT_NOTIFY_DEV_SCHEDULES WHERE schedule_owner = v_profile_name AND schedule_name = ' ' AND device_name = v_device_name AND device_owner = v_profile_name; IF available = 'Y' THEN AVAILABLE_DEVICES.EXTEND; AVAILABLE_DEVICES(AVAILABLE_DEVICES.COUNT) := v_device_name || v_profile_name; RETURN TRUE; ELSE UNAVAILABLE_DEVICES.EXTEND; UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || v_profile_name; RETURN FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- This device does not exist in the schedule UNAVAILABLE_DEVICES.EXTEND; UNAVAILABLE_DEVICES(UNAVAILABLE_DEVICES.COUNT) := v_device_name || v_profile_name; RETURN FALSE; END; END DEVICE_AVAILABLE; -- PURPOSE -- Function used for testing DEVICE_AVAILABLE FUNCTION IS_DEVICE_AVAILABLE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_time_to_check IN DATE) RETURN BOOLEAN IS BEGIN UNAVAILABLE_DEVICES := SMP_EMD_STRING_ARRAY(); AVAILABLE_DEVICES := SMP_EMD_STRING_ARRAY(); RETURN DEVICE_AVAILABLE(v_device_name, UPPER(v_profile_name), v_time_to_check); END IS_DEVICE_AVAILABLE; -- PURPOSE -- Function to read a configuration value FUNCTION GET_CONFIG_VALUE(pname IN VARCHAR2, default_value IN VARCHAR2) RETURN VARCHAR2 IS value VARCHAR2(256); BEGIN SELECT parameter_value INTO value FROM MGMT_PARAMETERS WHERE parameter_name = pname; RETURN value; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN default_value; END GET_CONFIG_VALUE; -- PURPOSE -- To initialize the queue table and get the number of queues which -- can be using in mapping user/object combinations to a queue -- -- NOTES -- The number of queues remains fixed once they have been initialized. To -- modify the number of queues, the SET_NUMBER_QUEUES procedure must be -- called which results in the temporary disabling of the queues while --- reassignment tables place PROCEDURE INITIALIZE_QUEUES IS BEGIN -- Get the number of queues SELECT COUNT(qname) INTO NUM_EMAIL_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'EMAIL%'; IF NUM_EMAIL_QUEUES = 0 THEN NUM_EMAIL_QUEUES := GET_CONFIG_VALUE(NOTIFY_EMAIL_QUEUES, DEFAULT_NOTIFY_EMAIL_QUEUES); -- Insert the queue names FOR i IN 1..NUM_EMAIL_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('EMAIL' || i); END LOOP; END IF; SELECT COUNT(qname) INTO NUM_OSCMD_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'OSCMD%'; IF NUM_OSCMD_QUEUES = 0 THEN NUM_OSCMD_QUEUES := GET_CONFIG_VALUE(NOTIFY_OSCMD_QUEUES, DEFAULT_NOTIFY_OSCMD_QUEUES); FOR i IN 1..NUM_OSCMD_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('OSCMD' || i); END LOOP; END IF; SELECT COUNT(qname) INTO NUM_PLSQL_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'PLSQL%'; IF NUM_PLSQL_QUEUES = 0 THEN NUM_PLSQL_QUEUES := GET_CONFIG_VALUE(NOTIFY_PLSQL_QUEUES, DEFAULT_NOTIFY_PLSQL_QUEUES); FOR i IN 1..NUM_PLSQL_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('PLSQL' || i); END LOOP; END IF; SELECT COUNT(qname) INTO NUM_SNMP_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'SNMP%'; IF NUM_SNMP_QUEUES = 0 THEN NUM_SNMP_QUEUES := GET_CONFIG_VALUE(NOTIFY_SNMP_QUEUES, DEFAULT_NOTIFY_SNMP_QUEUES); FOR i IN 1..NUM_SNMP_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('SNMP' || i); END LOOP; END IF; SELECT COUNT(qname) INTO NUM_JAVA_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'JAVA%'; IF NUM_JAVA_QUEUES = 0 THEN NUM_JAVA_QUEUES := GET_CONFIG_VALUE(NOTIFY_JAVA_QUEUES, DEFAULT_NOTIFY_JAVA_QUEUES); FOR i IN 1..NUM_JAVA_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('JAVA' || i); END LOOP; END IF; -- for 10gR2 RCA will only support a single queue. This is due to the fact -- that RCA tasks may have to be synchronized under some circumstances and -- there is not support to do this in 10gR2 across OMSs, so a single RCA -- queue forces all tasks to run on a single OMS SELECT COUNT(qname) INTO NUM_RCA_QUEUES FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'RCA%'; IF NUM_RCA_QUEUES = 0 THEN NUM_RCA_QUEUES := GET_CONFIG_VALUE(NUM_RCA_QUEUES, DEFAULT_NOTIFY_RCA_QUEUES); FOR i IN 1..NUM_RCA_QUEUES LOOP INSERT INTO MGMT_NOTIFY_QUEUES (qname) VALUES ('RCA' || i); END LOOP; END IF; COMMIT; END INITIALIZE_QUEUES; -- PURPOSE -- To get the name of a queue to which the notification will be assigned -- -- NOTES -- Queueing is done based on the user and object GUID (target, policy or job) -- of the notification. Queues have a name of the form: -- -- -- -- where device-type is EMAIL, OSCMD, SNMP or PLSQL and number is the queue -- number which is based on values defined in MGMT_PARAMETERS for queue -- sizes FUNCTION GET_QUEUE(device_type_in IN NUMBER, device_owner_in IN VARCHAR2, source_guid_in IN RAW) RETURN VARCHAR2 IS qname VARCHAR2(30); qnumber NUMBER := 0; windows_os_qs SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); BEGIN -- Check the device type so the start of the qname can be set IF device_type_in IN (EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE) THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_EMAIL_QUEUES); RETURN 'EMAIL' || qnumber; ELSIF device_type_in = PROGRAM_DEVICE_TYPE THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_OSCMD_QUEUES); RETURN 'OSCMD' || qnumber; ELSIF device_type_in = WINDOWS_PROGRAM_DEVICE_TYPE THEN SELECT qname BULK COLLECT INTO windows_os_qs FROM MGMT_NOTIFY_QUEUES WHERE qname LIKE 'OSCMD%' AND windows = 1; IF windows_os_qs IS NULL OR windows_os_qs.COUNT = 0 THEN RETURN NULL; END IF; -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, windows_os_qs.COUNT); RETURN windows_os_qs(qnumber); ELSIF device_type_in = PLSQL_DEVICE_TYPE THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_PLSQL_QUEUES); RETURN 'PLSQL' || qnumber; ELSIF device_type_in = SNMP_DEVICE_TYPE THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_SNMP_QUEUES); RETURN 'SNMP' || qnumber; ELSIF device_type_in = RCA_DEVICE_TYPE THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_RCA_QUEUES); RETURN 'RCA' || qnumber; ELSIF device_type_in = JAVA_DEVICE_TYPE THEN -- Get the hash of the user and source_guid qnumber := DBMS_UTILITY.GET_HASH_VALUE(device_owner_in || source_guid_in, 1, NUM_JAVA_QUEUES); RETURN 'JAVA' || qnumber; END IF; RETURN NULL; END GET_QUEUE; -- PURPOSE -- Procedure to queue a notification. -- -- NOTES -- PROCEDURE QUEUE(qname_in IN VARCHAR2, notification_in IN MGMT_NOTIFY_NOTIFICATION) IS enq_options dbms_aq.enqueue_options_t; qMsg_properties dbms_aq.message_properties_t; qMsg_handle RAW(16); recipient sys.aq$_agent; BEGIN -- Set up the qname as the recipient name recipient := sys.aq$_agent(qname_in, NULL, 0); qMsg_properties.recipient_list(1) := recipient; -- Add the message to the queue DBMS_AQ.ENQUEUE(queue_name => G_QUEUE_NAME, enqueue_options => enq_options, message_properties => qMsg_properties, payload => notification_in, msgid => qMsg_handle); EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'QUEUE(' || qname_in || '/' || notification_in.device_name || '/' || notification_in.device_owner || ') ' || SQLERRM, 'ORA', notification_in.source_guid); END QUEUE; PROCEDURE QUEUE_READY(qnames_in IN SMP_EMD_STRING_ARRAY, qtimeout_in IN NUMBER, qname_out OUT VARCHAR2) IS agents dbms_aq.aq$_agent_list_t; agent sys.aq$_agent; BEGIN -- in standalone mode where is no dbms job to process the data -- of MGMT_NOTIFY_INPUT_Q. calling CHECK_FOR_SEVERITIES from -- listen call to process repeat notifications. IF (EMD_MAINTENANCE.IS_CENTRAL_MODE = 0) THEN CHECK_FOR_SEVERITIES(); END IF; FOR i IN qnames_in.FIRST..qnames_in.LAST LOOP agents(i) := sys.aq$_agent(qnames_in(i), G_QUEUE_NAME, 0); END LOOP; -- This raises an ORA-25254 exception if there is nothing in the queue DBMS_AQ.LISTEN (agents, qtimeout_in, agent); qname_out := agent.name; END QUEUE_READY; PROCEDURE DEQUEUE(qname_in IN VARCHAR2, qtimeout_in IN NUMBER, qmsg_id_out OUT RAW, notification_out OUT MGMT_NOTIFY_NOTIFICATION) IS dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; BEGIN dq_options.consumer_name := qname_in; dq_options.wait := qtimeout_in; dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; -- Dequeue a notification DBMS_AQ.DEQUEUE(queue_name => G_QUEUE_NAME, dequeue_options => dq_options, message_properties => qMsg_properties, payload => notification_out, msgid => qmsg_id_out); END DEQUEUE; -- PURPOSE -- Procedure to queue an RCA update task -- NOTE: this is used during regression runs only!! -- PROCEDURE QUEUE_RCA_TASK(target_guid_in IN RAW, severity_guid_in IN RAW, rca_type_in IN NUMBER, rca_mode_in IN VARCHAR2) IS qMsg_properties dbms_aq.message_properties_t; qMsg MGMT_NOTIFY_NOTIFICATION; qname VARCHAR2(30); BEGIN qname := GET_QUEUE(RCA_DEVICE_TYPE, 'RCA', target_guid_in); qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, rca_type_in, severity_guid_in, 'RCA', 'RCA', RCA_DEVICE_TYPE, rca_mode_in, RAWTOHEX(target_guid_in), qname, 0); QUEUE(qname, qMsg); END QUEUE_RCA_TASK; -- PURPOSE -- Procedure to queue an RCA update task -- PROCEDURE QUEUE_RCA_UPDATE(target_guid_in IN RAW, severity_guid_in IN RAW, event_guid_in IN RAW, delay_in IN NUMBER) IS qMsg_properties dbms_aq.message_properties_t; qMsg MGMT_NOTIFY_NOTIFICATION; qname VARCHAR2(30); rca_type VARCHAR2(30); BEGIN qname := GET_QUEUE(RCA_DEVICE_TYPE, 'RCA', target_guid_in); IF delay_in = 0 THEN rca_type := 'RCA_IMMEDIATE'; ELSE rca_type := 'RCA_NORMAL'; END IF; IF severity_guid_in IS NULL THEN qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, RCA_RETRY_EVENT, event_guid_in, 'RCA', 'RCA', RCA_DEVICE_TYPE, rca_type, RAWTOHEX(target_guid_in), qname, 0); ELSE qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, RCA_RETRY_SEVERITY, severity_guid_in, 'RCA', 'RCA', RCA_DEVICE_TYPE, rca_type, RAWTOHEX(target_guid_in), qname, 0); END IF; QUEUE_WITH_DELAY(qname, qMsg, delay_in); END QUEUE_RCA_UPDATE; -- PURPOSE -- Procedure to queue a task with a delay -- -- NOTES -- PROCEDURE QUEUE_WITH_DELAY(qname_in IN VARCHAR2, notification_in IN MGMT_NOTIFY_NOTIFICATION, delay_in IN NUMBER) IS enq_options dbms_aq.enqueue_options_t; qMsg_properties dbms_aq.message_properties_t; qMsg_handle RAW(16); recipient sys.aq$_agent; BEGIN -- Set up the qname as the recipient name recipient := sys.aq$_agent(qname_in, NULL, 0); qMsg_properties.recipient_list(1) := recipient; qMsg_properties.delay := delay_in; -- Add the message to the queue DBMS_AQ.ENQUEUE(queue_name => G_QUEUE_NAME, enqueue_options => enq_options, message_properties => qMsg_properties, payload => notification_in, msgid => qMsg_handle); EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'QUEUE_WITH_DELAY(' || qname_in || '/' || notification_in.device_name || '/' || notification_in.device_owner || ') ' || SQLERRM, 'ORA', notification_in.source_guid); END QUEUE_WITH_DELAY; PROCEDURE CHK_AND_QUEUE_REP_NOTIF(p_sev_guid_in IN RAW, p_rule_guid IN RAW, p_curr_repeat_count NUMBER) IS l_repeat_enabled NUMBER(1); l_rep_frequency NUMBER; l_max_rep_count NUMBER; l_ack NUMBER(1); l_violation_type NUMBER(1); l_cnt NUMBER(1); l_source_type NUMBER(1); l_qname VARCHAR2(30); l_target_guid RAW(16); l_notif_status NUMBER; l_violation_level NUMBER; tgt_is_service NUMBER := 0; l_num_emails NUMBER := 0; l_tz_region VARCHAR2(64); qMsg MGMT_NOTIFY_NOTIFICATION; l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE; CURSOR devices IS SELECT DISTINCT d.profile_name, d.device_name, d.type, d.status, d.program, r.owner, r.rule_name FROM mgmt_notify_rules r, mgmt_notify_devices d, mgmt_notify_notifyees n WHERE r.rule_guid = p_rule_guid AND r.rule_name = n.rule_name AND r.owner = n.owner AND n.device_name = d.device_name AND n.profile_name = d.profile_name AND d.type IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE); BEGIN BEGIN GET_GLOBAL_REPEAT_SETTINGS(l_repeat_enabled, l_rep_frequency, l_max_rep_count); IF(l_repeat_enabled = 0) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Global repeat setting is turned off. ' || ' No more repeat notificaion will be send' , 'CHK_AND_QUEUE_REP_NOTIF'); END IF; RETURN; END IF; IF(p_curr_repeat_count > l_max_rep_count) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Global repeat setting count is less the current repeat count. ' || ' No more repeat notificaion will be send' , 'CHK_AND_QUEUE_REP_NOTIF'); END IF; RETURN; END IF; SELECT acknowledged, violation_type, target_guid, notification_status INTO l_ack, l_violation_type, l_target_guid, l_notif_status FROM mgmt_violations WHERE violation_guid = p_sev_guid_in; IF(l_ack = 1) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Severity is already acknowledge. ' || ' No more repeat notificaion will be send' , 'CHK_AND_QUEUE_REP_NOTIF'); END IF; RETURN; END IF; IF(l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY) THEN SELECT count(*) INTO l_cnt FROM mgmt_current_availability WHERE severity_guid = p_sev_guid_in; ELSE SELECT count(*) INTO l_cnt FROM mgmt_current_violation WHERE violation_guid = p_sev_guid_in; END IF; IF(l_cnt = 0) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Severity state has changed. ' || ' No more repeat notificaion will be send' , 'CHK_AND_QUEUE_REP_NOTIF'); END IF; RETURN; END IF; IF l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY THEN l_source_type := POLICY_VIOLATION; ELSE l_source_type := METRIC_SEVERITY; END IF; -- Check if this is an RCA notification SELECT target_type INTO l_target_type FROM mgmt_targets WHERE target_guid = l_target_guid; tgt_is_service := mgmt_service.implements_service_interface(l_target_type); IF tgt_is_service = 1 AND l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY AND l_notif_status IN (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE, MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE) AND l_violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_source_type := RCA_COMPLETED_SEVERITY; END IF; FOR device IN devices LOOP IF DEVICE_AVAILABLE(device.device_name, device.profile_name) THEN -- Get the queue name for this user/object combination l_qname := GET_QUEUE(device.type, device.profile_name, l_target_guid); IF l_qname IS NOT NULL THEN qMsg := MGMT_NOTIFY_NOTIFICATION(REPEAT_NOTIFICATION, IS_NOT_PING, l_source_type, p_sev_guid_in, device.device_name, device.profile_name, device.type, device.rule_name, device.owner, l_qname, p_curr_repeat_count); QUEUE(l_qname, qMsg); NUM_EMAILS_QUEUED := NUM_EMAILS_QUEUED + 1; l_num_emails := l_num_emails + 1; END IF; ELSE IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO( device.device_name || ' Email device is not avaliable.' || ' Repeat notificaion will not be send' , 'CHK_AND_QUEUE_REP_NOTIF'); END IF; END IF; END LOOP; IF(l_num_emails > 0) THEN SELECT TIMEZONE_REGION INTO l_tz_region FROM mgmt_targets WHERE target_guid = l_target_guid; INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY, p_sev_guid_in, mgmt_global.sysdate_tzrgn(l_tz_region), 'NOTIFICATION', '', l_num_emails || ' repeat email notifications will be sent '); END IF; IF(p_curr_repeat_count < l_max_rep_count) THEN queue_repeat_notif_input(p_sev_guid_in, p_rule_guid, p_curr_repeat_count + 1, l_rep_frequency); END IF; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || ' CHK_AND_QUEUE_REP_NOTIF(' || p_sev_guid_in || '/' || p_rule_guid || '/' || p_curr_repeat_count || ') ' || SQLERRM, 'ORA', p_sev_guid_in); END; END CHK_AND_QUEUE_REP_NOTIF; -- PURPOSE -- Procedure to find the devices that need to be notified for a severity -- occurrence and queue the notifications -- PROCEDURE QUEUE_METRIC_NOTIFICATIONS ( v_violation IN MGMT_VIOLATIONS%ROWTYPE, v_ca_state_change IN MGMT_JOB_STATE_CHANGES%ROWTYPE DEFAULT NULL ) IS qMsg MGMT_NOTIFY_NOTIFICATION; tguid RAW(16); tgt_name MGMT_TARGETS.target_name%TYPE; tgt_type MGMT_TARGETS.target_type%TYPE; tz_region VARCHAR2(64); met_name VARCHAR2(64); met_col VARCHAR2(64); pname VARCHAR2(64); dname VARCHAR2(132); qname VARCHAR2(30); origUser VARCHAR2(256); checkUser VARCHAR2(256); lastUser VARCHAR2(256); has_priv NUMBER := 0; is_response NUMBER := 0; qstart DATE; num_emails NUMBER := 0; num_oscmds NUMBER := 0; num_plsql NUMBER := 0; num_snmp NUMBER := 0; num_java NUMBER := 0; tgt_is_service NUMBER := 0; t DATE; l_device_type NUMBER := 0; l_source_type NUMBER := 0; l_source_id RAW(16) := v_violation.violation_guid; l_annotation_type NUMBER := MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY; l_annotation_msg VARCHAR2(256) := 'The following notifications will be sent: '; l_ca_state NUMBER := -1; l_notification_type NUMBER := NORMAL_NOTIFICATION; l_error_message VARCHAR2(256); l_rule_guid RAW(16); l_repeat NUMBER(1); l_repeat_enabled NUMBER(1); l_rep_frequency NUMBER; l_max_rep_count NUMBER; l_ack NUMBER(1); l_tz_region VARCHAR2(64); l_rule_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_distinct_rules MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); CURSOR devices(tguid RAW, tname VARCHAR2, ttype VARCHAR2, mname VARCHAR2, mcol VARCHAR2, kval VARCHAR2, violation_level NUMBER, is_response_metric NUMBER, ca_state NUMBER, is_service NUMBER) IS SELECT /*+ ORDERED PUSH_SUBQ */ DISTINCT d.profile_name, d.device_name, d.type, d.status, d.program, cfg.owner, cfg.rule_name, DECODE(d.type, SHORT_EMAIL_DEVICE_TYPE,d.profile_name, LONG_EMAIL_DEVICE_TYPE ,d.profile_name, cfg.owner) checkUser FROM (SELECT rule_name, owner FROM MGMT_NOTIFY_RULE_CONFIGS c WHERE ((ttype LIKE c.target_type ESCAPE G_ESC AND tname LIKE c.target_name ESCAPE G_ESC) OR (c.target_guid IS NOT NULL AND EXISTS (SELECT 1 FROM MGMT_FLAT_TARGET_ASSOC a, MGMT_TARGETS t WHERE a.source_target_guid = c.target_guid AND a.is_membership = 1 AND a.assoc_target_guid = t.target_guid AND t.target_type = c.target_type AND t.target_name = tname))) AND mname LIKE c.metric_name ESCAPE G_ESC AND mcol LIKE c.metric_column ESCAPE G_ESC AND ((kval LIKE c.key_value ESCAPE G_ESC) OR (c.key_value <> ' ' AND EXISTS (SELECT 1 FROM mgmt_metrics_composite_keys k WHERE k.composite_key = kval AND NVL(k.key_part1_value, ' ') LIKE c.key_part_1 ESCAPE G_ESC AND NVL(k.key_part2_value, ' ') LIKE c.key_part_2 ESCAPE G_ESC AND NVL(k.key_part3_value, ' ') LIKE c.key_part_3 ESCAPE G_ESC AND NVL(k.key_part4_value, ' ') LIKE c.key_part_4 ESCAPE G_ESC AND NVL(k.key_part5_value, ' ') LIKE c.key_part_5 ESCAPE G_ESC AND k.target_guid = tguid))) AND ((ca_state = -1 AND ( -- The following conditions should only match violation related to -- metric severities and policies (violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START AND want_target_unreachable_start = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR AND want_target_unreachable_end = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START AND want_target_blackout_start = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END AND want_target_blackout_end = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_START AND ((want_target_metric_err_start = 1 AND is_response_metric = 1) OR (want_target_metric_err_start = 2 AND is_response_metric = 0) OR (want_target_metric_err_start = 3))) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_END AND ((want_target_metric_err_end = 1 AND is_response_metric = 1) OR (want_target_metric_err_end = 2 AND is_response_metric = 0) OR (want_target_metric_err_end = 3))) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND want_clears = 1 AND v_violation.violation_type <> 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND want_target_up = 1 AND v_violation.violation_type = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND want_warnings = 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_critical_alerts = 1 AND v_violation.violation_type <> 1) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_target_down = 1 AND v_violation.violation_type = 1 AND is_service = 0) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_target_down = 1 AND v_violation.violation_type = 1 AND is_service = 1 AND is_response_metric = 1 and ignore_rca = 1 AND v_violation.notification_status NOT IN (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE, MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE)) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_target_down = 1 AND v_violation.violation_type = 1 AND is_service = 1 AND is_response_metric = 1 and ignore_rca = 0 AND v_violation.notification_status IN (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE, MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE)) OR ((violation_level = MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL OR violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING OR violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL) AND want_policy_violations = 1 ) OR (violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR AND want_policy_clears = 1 ) ) ) OR (ca_state <> -1 AND ( -- The following conditions should only match violations related to -- corrective action executions (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND want_warning_job_succeeded = 1) OR (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_critical_job_succeeded = 1) OR (ca_state = MGMT_JOBS.STATUS_BUCKET_OK AND violation_level <> MGMT_GLOBAL.G_SEVERITY_CLEAR AND want_policy_job_succeeded = 1) OR (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND violation_level = MGMT_GLOBAL.G_SEVERITY_WARNING AND want_warning_job_problems = 1) OR (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL AND want_critical_job_problems = 1) OR (ca_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND violation_level <> MGMT_GLOBAL.G_SEVERITY_CLEAR AND want_policy_job_problems = 1) ) ) )) cfg, mgmt_notify_notifyees n, mgmt_notify_devices d WHERE n.rule_name = cfg.rule_name AND n.owner = cfg.owner AND ((d.device_name = n.device_name AND d.profile_name = n.profile_name) OR (n.device_name = ' ' AND d.profile_name = n.profile_name AND d.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) ) ) ORDER BY checkUser; BEGIN BEGIN GET_GLOBAL_REPEAT_SETTINGS(l_repeat_enabled, l_rep_frequency, l_max_rep_count); -- Get the target name and SELECT target_name, target_type, timezone_region INTO tgt_name, tgt_type, tz_region FROM MGMT_TARGETS WHERE target_guid = v_violation.target_guid; tgt_is_service := mgmt_service.implements_service_interface(tgt_type); EXCEPTION WHEN NO_DATA_FOUND THEN -- Severity contains an invalid target guid IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: invalid target: violation_guid = ' || v_violation.violation_guid || ' target_guid=' || v_violation.target_guid, CHECK_MODULE_NAME) ; END IF; RETURN; END; -- If this is a policy violation then get the policy name IF v_violation.violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY THEN l_source_type := POLICY_VIOLATION; BEGIN -- Get the policy_name SELECT policy_name INTO met_name FROM MGMT_POLICIES p WHERE policy_guid = v_violation.policy_guid; met_col := '%'; EXCEPTION WHEN NO_DATA_FOUND THEN -- Severity contains an invalid policy guid IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: invalid policy: violation_guid = ' || v_violation.violation_guid || ' policy_guid=' || v_violation.policy_guid, CHECK_MODULE_NAME) ; END IF; RETURN; END; ELSE l_source_type := METRIC_SEVERITY; BEGIN -- Get the metric name and column SELECT m.metric_name, m.metric_column INTO met_name, met_col FROM MGMT_TARGETS t, MGMT_METRICS m WHERE t.target_guid = v_violation.target_guid AND m.metric_guid = v_violation.policy_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN -- Severity contains an invalid metric guid IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: invalid metric: violation_guid = ' || v_violation.violation_guid || ' metric_guid' || v_violation.policy_guid, CHECK_MODULE_NAME) ; END IF; RETURN; WHEN TOO_MANY_ROWS THEN -- Get the metric name and column SELECT m.metric_name, m.metric_column INTO met_name, met_col FROM MGMT_TARGETS t, MGMT_METRICS m WHERE t.target_guid = v_violation.target_guid AND m.metric_guid = v_violation.policy_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND rownum < 2; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: duplicate metric: violation_guid = ' || v_violation.violation_guid || ' metric_guid=' || v_violation.policy_guid, CHECK_MODULE_NAME) ; END IF; -- Log an error mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, 'QUEUE_METRIC_NOTIFICATIONS found duplicate metric definition for ' || met_name || '/' || met_col || '/' || tgt_type, 'ORA', v_violation.violation_guid); RETURN; END; END IF; -- Check if this is the Response Metric IF met_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met_col = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN THEN is_response := 1; END IF; -- First get the currently logged in user. this user name will be used -- later for resetting the context back to this user origUser := mgmt_user.get_current_em_user(); -- Check if this is for a corrective action IF v_ca_state_change.state_change_guid IS NOT NULL THEN l_ca_state := v_ca_state_change.status_bucket; l_source_id := v_ca_state_change.state_change_guid; l_annotation_msg := 'The following notifications will be sent for the Corrective Action: '; -- Update the source type IF l_source_type = METRIC_SEVERITY THEN l_source_type := METRIC_CA_STATE_CHANGE; ELSIF l_source_type = POLICY_VIOLATION THEN l_source_type := POLICY_CA_STATE_CHANGE; END IF; END IF; -- Check if this is an RCA notification IF tgt_is_service = 1 AND is_response = 1 AND v_violation.notification_status IN (MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE, MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE) AND v_violation.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN l_source_type := RCA_COMPLETED_SEVERITY; END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: violation_guid = ' || v_violation.violation_guid || ' target=' || tgt_name || ' type=' || tgt_type || ' metric/policy=' || met_name || ' column=' || met_col || ' level=' || v_violation.violation_level || ' source_type=' || l_source_type || ' is_response=' || is_response || ' message=' || v_violation.message, CHECK_MODULE_NAME) ; END IF ; -- Get the devices that need to be notified FOR device IN devices(v_violation.target_guid, tgt_name, tgt_type, met_name, met_col, v_violation.key_value, v_violation.violation_level, is_response, l_ca_state, tgt_is_service) LOOP -- For use in error message pname := device.profile_name; dname := device.device_name; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: Match found. Rule=' || device.rule_name || ' Owner=' || device.owner || ' Method=' || dname || ' Owner=' || pname, CHECK_MODULE_NAME) ; END IF ; -- There is no need to check privileges for standalone mode -- since there is no VPD IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- For emails you need to make sure the user associated with the -- address has accees to the target. For all other notification methods -- you need to make sure the owner of the rule has access to the target -- since those methods are always owned by the Super User checkUser := device.checkUser; -- Only check if this user has not been checked before IF lastUser IS NULL OR lastUser <> checkUser THEN lastUser := checkUser; has_priv := 0; -- Check if the device owner has access to the target SETEMUSERCONTEXT(checkUser, MGMT_USER.OP_SET_IDENTIFIER); BEGIN -- the following line has been changed by dcawley to fix a problem -- with VPD policies not being applied correctly by the RDBMS. -- See bug 4143213 SELECT count(target_guid) INTO has_priv FROM MGMT_TARGETS WHERE target_guid = v_violation.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN -- Continue onto the next device since this device has no access to -- the target has_priv := 0; END; END IF; IF has_priv = 0 THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: ' || checkUser || ' has no access to ' || tgt_name || '/' || tgt_type, CHECK_MODULE_NAME) ; END IF ; GOTO end_device_loop; END IF; END IF; -- Check the notification schedule to see if the device is available IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR DEVICE_AVAILABLE(device.device_name, device.profile_name) THEN -- Check the status to make sure it is contactable IF device.status = DEVICE_UP THEN qstart := SYSDATE; IF device.type = PROGRAM_DEVICE_TYPE AND IS_WINDOWS_PROGRAM(device.program) THEN l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE; ELSE l_device_type := device.type; END IF; -- Get the queue name for this user/object combination qname := GET_QUEUE(l_device_type, device.profile_name, v_violation.target_guid); IF qname IS NOT NULL THEN -- Set up the message for the queue qMsg := MGMT_NOTIFY_NOTIFICATION(l_notification_type, IS_NOT_PING, l_source_type, l_source_id, device.device_name, device.profile_name, device.type, device.rule_name, device.owner, qname, 0); IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: Queueing notification for' || ' device_name=' || dname || ' owner=' || pname, CHECK_MODULE_NAME) ; END IF ; QUEUE(qname, qMsg); TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart); -- Update the local and overall stats IF device.type IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE) THEN NUM_EMAILS_QUEUED := NUM_EMAILS_QUEUED + 1; num_emails := num_emails + 1; ELSIF device.type = PROGRAM_DEVICE_TYPE THEN NUM_OSCMDS_QUEUED := NUM_OSCMDS_QUEUED + 1; num_oscmds := num_oscmds + 1; ELSIF device.type = SNMP_DEVICE_TYPE THEN NUM_SNMPS_QUEUED := NUM_SNMPS_QUEUED + 1; num_snmp := num_snmp + 1; ELSIF device.type = PLSQL_DEVICE_TYPE THEN NUM_PLSQLS_QUEUED := NUM_PLSQLS_QUEUED + 1; num_plsql := num_plsql + 1; ELSIF device.type = JAVA_DEVICE_TYPE THEN NUM_JAVA_QUEUED := NUM_JAVA_QUEUED + 1; num_java := num_java + 1; END IF; ELSE -- Could not find a queue to service notification. Must be an -- OS Command where no Windows OMSs are registered IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: No Windows OMS to handle OS Command', CHECK_MODULE_NAME) ; END IF ; LOG_HISTORY(l_annotation_type, v_violation.violation_guid, 'N', 'A Windows based Management Server is not registered ' || '(Rule Owner=' || device.owner || ', Rule Name=' || device.rule_name || ')', tz_region); END IF; ELSE -- The device is currently down so set this up for retry IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: Device down so setup for retry', CHECK_MODULE_NAME) ; END IF ; INSERT INTO mgmt_notify_requeue (source_guid, source_type, notification_type, device_name, device_owner, device_type, rule_name, rule_owner) VALUES (v_violation.violation_guid, l_source_type, RETRY_NOTIFICATION, device.device_name, device.profile_name, device.type, device.rule_name, device.owner); END IF; ELSE IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: ' || dname || ' is not in notification schedule', CHECK_MODULE_NAME) ; END IF ; END IF; IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the severity insert -- trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; -- if the source is not CA then check for repeat and severity is not for clear IF l_source_type NOT IN (METRIC_CA_STATE_CHANGE, POLICY_CA_STATE_CHANGE) AND v_violation.violation_level NOT IN (mgmt_global.g_severity_clear, mgmt_global.g_severity_unreachable_clear, mgmt_global.g_severity_blackout_start, mgmt_global.g_severity_blackout_end, mgmt_global.g_severity_error_end, mgmt_global.g_severity_unknown, mgmt_global.g_severity_informational) THEN SELECT rule_guid, repeat INTO l_rule_guid, l_repeat FROM mgmt_notify_rules WHERE rule_name = device.rule_name AND owner = device.owner; IF(l_repeat = 1 AND l_repeat_enabled = 1 AND l_max_rep_count > 0) THEN l_rule_guids.extend(1); l_rule_guids(l_rule_guids.COUNT) := l_rule_guid; END IF; END IF; << end_device_loop >> NULL; END LOOP; -- check if the severity should produce an RCA task; this requires that the -- target is a service and that the severity is for the Response-Status metric, -- in other words, that its a Service Down alert. IF tgt_is_service = 1 AND is_response = 1 AND v_violation.notification_status <> MGMT_GLOBAL.G_NOTIF_STATUS_RCA_CAUSE AND v_violation.notification_status <> MGMT_GLOBAL.G_NOTIF_STATUS_RCA_NO_CAUSE AND v_violation.violation_level = MGMT_GLOBAL.G_SEVERITY_CRITICAL THEN -- queue the RCA task qstart := SYSDATE; -- Get the queue name for this user/object combination qname := GET_QUEUE(RCA_DEVICE_TYPE, 'RCA', v_violation.target_guid); qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, RCA_SEVERITY, v_violation.violation_guid, 'RCA', 'RCA', RCA_DEVICE_TYPE, 'RCA_NORMAL', RAWTOHEX(v_violation.target_guid), qname, 0); QUEUE(qname, qMsg); IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: Queued RCA task', CHECK_MODULE_NAME) ; END IF ; TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart); END IF; IF num_emails > 0 OR num_oscmds > 0 OR num_plsql > 0 OR num_snmp > 0 OR num_java > 0 THEN t := mgmt_global.sysdate_tzrgn(tz_region); -- The message needs to be NLS'ed INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (l_annotation_type, v_violation.violation_guid, t, 'NOTIFICATION', '', l_annotation_msg || decode(num_emails, 0, '', num_emails || ' e-mail messages ') || decode(num_snmp, 0, '', num_snmp || ' SNMP traps ') || decode(num_plsql, 0, '', num_plsql || ' PL/SQL procedures ') || decode(num_oscmds, 0, '', num_oscmds || ' OS Commands ') || decode(num_java, 0, '', num_java || ' Java callbacks')); END IF; -- if l_rule_guids.COUNT > 0 then enqueue repeat signal for each rule IF l_rule_guids.COUNT > 0 THEN BEGIN SELECT distinct(l.COLUMN_VALUE) BULK COLLECT INTO l_distinct_rules FROM TABLE (CAST(l_rule_guids as MGMT_USER_GUID_ARRAY)) l; FOR i in 1 .. l_distinct_rules.COUNT LOOP l_rule_guid := l_distinct_rules(i); queue_repeat_notif_input(l_source_id, l_rule_guid, 1, l_rep_frequency); END LOOP; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'QUEUE_METRIC_NOTIFICATIONS : ' || ' Error while enquing repeat signal for ( ' || l_source_id || ', ' || l_rule_guid || ' ' || SQLERRM, 'ORA', l_source_id); END; END IF; -- Reset the context back to the original user SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER); IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; EXCEPTION WHEN OTHERS THEN IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger.(Commits/Rollbacks are not allowed in triggers) ROLLBACK; END IF; -- Reset the context back to the original user IF origUser IS NOT NULL THEN SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER); END IF; IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger.(Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QMN: Unexpected error ' || SQLERRM, CHECK_MODULE_NAME) ; END IF ; mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'QUEUE_METRIC_NOTIFICATIONS (' || pname || '/' || dname || ') ' || SQLERRM, 'ORA', v_violation.violation_guid); END QUEUE_METRIC_NOTIFICATIONS; -- PURPOSE -- Procedure to find the devices that need to be notified for a job state -- change and queue the notifications -- PROCEDURE QUEUE_JOB_NOTIFICATIONS(v_state_change IN MGMT_JOB_STATE_CHANGES%ROWTYPE) IS qMsg MGMT_NOTIFY_NOTIFICATION; tguid RAW(16); l_job_id RAW(16); tgt_guids MGMT_USER_GUID_ARRAY; tgt_matches MGMT_LONG_STRING_ARRAY; tgt_types MGMT_SHORT_STRING_ARRAY; tz_regions MGMT_SHORT_STRING_ARRAY; job_name VARCHAR2(64) := '%'; job_owner VARCHAR2(256) := '%'; job_type VARCHAR2(32) := '%'; pname VARCHAR2(64); dname VARCHAR2(132); qname VARCHAR2(30); origUser VARCHAR2(256); checkUser VARCHAR2(256); lastUser VARCHAR2(256); has_priv NUMBER := 0; qstart DATE; num_emails NUMBER := 0; num_oscmds NUMBER := 0; num_plsql NUMBER := 0; num_java NUMBER := 0; num_snmp NUMBER := 0; t DATE; l_device_type NUMBER := 0; l_error_message VARCHAR2(256); l_tz_rgn MGMT_TARGETS.timezone_region%TYPE; CURSOR devices(ttypes MGMT_SHORT_STRING_ARRAY, tname_type MGMT_LONG_STRING_ARRAY, jname VARCHAR2, jowner VARCHAR2, jtype VARCHAR2, job_state NUMBER) IS SELECT /*+ ORDERED PUSH_SUBQ */ DISTINCT d.profile_name, d.device_name, d.type, d.status, d.program, cfg.owner, cfg.rule_name FROM (SELECT rule_name, owner FROM MGMT_NOTIFY_JOB_RULE_CONFIGS c WHERE (c.target_name||';'||c.target_type IN (SELECT * FROM TABLE(CAST(tname_type AS MGMT_LONG_STRING_ARRAY))) OR (c.target_name = '%' AND c.target_type IN (SELECT * FROM TABLE(CAST(ttypes AS MGMT_SHORT_STRING_ARRAY)))) OR (c.target_guid IS NOT NULL AND EXISTS (SELECT 1 FROM MGMT_FLAT_TARGET_ASSOC a, MGMT_TARGETS t WHERE a.source_target_guid = c.target_guid AND a.is_membership = 1 AND a.assoc_target_guid = t.target_guid AND t.target_type = c.target_type AND t.target_name||';'||c.target_type IN (SELECT * FROM TABLE(CAST(tname_type AS MGMT_LONG_STRING_ARRAY))) ))) AND jname LIKE c.job_name ESCAPE G_ESC AND jowner LIKE c.job_owner ESCAPE G_ESC AND jtype LIKE c.job_type ESCAPE G_ESC AND ((job_state = MGMT_JOBS.STATUS_BUCKET_SCHEDULED AND want_job_scheduled = 1) OR (job_state = MGMT_JOBS.STATUS_BUCKET_RUNNING AND want_job_running = 1) OR (job_state = MGMT_JOBS.STATUS_BUCKET_SUSPENDED AND want_job_suspended = 1) OR (job_state = MGMT_JOBS.STATUS_BUCKET_OK AND want_job_succeeded = 1) OR (job_state = MGMT_JOBS.STATUS_BUCKET_PROBLEM AND want_job_problems = 1))) cfg, mgmt_notify_notifyees n, mgmt_notify_devices d WHERE n.rule_name = cfg.rule_name AND n.owner = cfg.owner AND ((d.device_name = n.device_name AND d.profile_name = n.profile_name) OR (n.device_name = ' ' AND d.profile_name = n.profile_name AND d.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) ) ); BEGIN BEGIN -- Get the target name and type SELECT DISTINCT job_tgt.target_guid, tgt.target_type, tgt.timezone_region, tgt.target_name||';'||tgt.target_type BULK COLLECT INTO tgt_guids, tgt_types, tz_regions, tgt_matches FROM MGMT_JOB_EXT_TARGETS job_tgt, MGMT_TARGETS tgt WHERE job_tgt.job_id = v_state_change.job_id AND tgt.target_guid = job_tgt.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN -- State change contains an invalid target guid IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: invalid target: state_change_guid = ' || v_state_change.state_change_guid, CHECK_MODULE_NAME) ; END IF; RETURN; END; if tz_regions.count>0 then -- Use Target TimeZone when available l_tz_rgn := tz_regions(1); else -- Use emdrep TimeZone for Targetless jobs BEGIN select TIMEZONE_REGION into l_tz_rgn from MGMT_TARGETS where target_type='oracle_emrep'; EXCEPTION WHEN NO_DATA_FOUND THEN l_tz_rgn := TO_CHAR(SYSTIMESTAMP,'TZR'); END ; end if; -- Get the job details BEGIN SELECT job_name, job_owner, job_type INTO job_name, job_owner, job_type FROM MGMT_JOB WHERE job_id = v_state_change.job_id AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING; EXCEPTION WHEN NO_DATA_FOUND THEN -- State change contains an invalid job id IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: invalid job: state_change_guid = ' || v_state_change.state_change_guid || ' job_id=' || v_state_change.job_id, CHECK_MODULE_NAME) ; END IF; RETURN; END; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: state_change_guid = ' || v_state_change.state_change_guid || ' job_id=' || v_state_change.job_id || ' execution_id=' || v_state_change.execution_id || ' newstate=' || v_state_change.newstate || ' status_bucket=' || v_state_change.status_bucket, CHECK_MODULE_NAME) ; END IF; -- First get the currently logged in user. this user name will be used -- later for resetting the context back to this user origUser := mgmt_user.get_current_em_user(); -- Check if the owner of the job wants to be notified BEGIN SELECT job_id INTO l_job_id FROM MGMT_JOB_NOTIFY_STATES WHERE job_id = v_state_change.job_id AND notify_state = v_state_change.status_bucket; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: the job owner ' || job_owner || ' has selected to be notified', CHECK_MODULE_NAME) ; END IF; -- Get the job owner's email addresses FOR oe IN (SELECT device_name, email_address, type, status FROM MGMT_NOTIFY_DEVICES WHERE profile_name = job_owner AND type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE)) LOOP -- Do we need to check if the owner has access to the target? IF DEVICE_AVAILABLE(oe.device_name, job_owner) THEN -- Check the status to make sure it is contactable IF oe.status = DEVICE_UP THEN qstart := SYSDATE; -- Get the queue name for this user/object combination qname := GET_QUEUE(oe.type, job_owner, v_state_change.job_id); IF qname IS NOT NULL THEN -- Set up the message for the queue qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, JOB_STATE_CHANGE, v_state_change.state_change_guid, oe.device_name, job_owner, oe.type, NULL, NULL, qname, 0); IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: Queuing notification to ' || oe.device_name, CHECK_MODULE_NAME) ; END IF; QUEUE(qname, qMsg); TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart); NUM_JOB_EMAILS_QUEUED := NUM_JOB_EMAILS_QUEUED + 1; num_emails := num_emails + 1; END IF; ELSE IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: ' || oe.device_name || ' is down', CHECK_MODULE_NAME) ; END IF; END IF; ELSE IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: ' || oe.device_name || ' is not in the notification schedule', CHECK_MODULE_NAME) ; END IF; END IF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; FOR device IN devices(tgt_types, tgt_matches, job_name, job_owner, job_type, v_state_change.status_bucket) LOOP -- For use in error message pname := device.profile_name; dname := device.device_name; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: Match found. Rule=' || device.rule_name || ' Owner=' || device.owner || ' Method=' || dname || ' Owner=' || pname, CHECK_MODULE_NAME) ; END IF ; -- There is no need to check privileges for standalone mode -- since there is no VPD IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- For emails you need to make sure the user associated with the -- address has accees to the target. For all other notification methods -- you need to make sure the owner of the rule has access to the target -- since those methods are always owned by the Super User IF device.type IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) THEN checkUser := device.profile_name; ELSE checkUser := device.owner; END IF; -- Only check if this user has not been checked before IF lastUser IS NULL OR lastUser <> checkUser THEN has_priv := 0; lastUser := checkUser; -- Check if the device owner has access to the target SETEMUSERCONTEXT(checkUser, MGMT_USER.OP_SET_IDENTIFIER); -- Get a count of the targets the user can see SELECT count(target_guid) INTO has_priv FROM MGMT_TARGETS WHERE target_guid IN (SELECT * FROM TABLE(CAST(tgt_guids AS MGMT_USER_GUID_ARRAY))); IF has_priv = 1 THEN BEGIN SELECT job_id INTO l_job_id FROM MGMT_JOB WHERE job_id = v_state_change.job_id AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING; has_priv := 1; EXCEPTION WHEN NO_DATA_FOUND THEN has_priv := 0; END; END IF; END IF; IF has_priv = 0 THEN -- Continue onto the next device since this device has no access to -- the target IF emdw_log.p_is_debug_set THEN DECLARE l_tgts VARCHAR2(4000); BEGIN FOR i IN tgt_guids.FIRST..tgt_guids.LAST LOOP l_tgts := l_tgts || tgt_guids(i) || ', '; END LOOP; emdw_log.debug('QJN: ' || checkUser || ' has no access to any of the job targets ' || l_tgts, CHECK_MODULE_NAME) ; EXCEPTION WHEN OTHERS THEN emdw_log.debug('QJN: ' || checkUser || ' has no access to any of the job targets', CHECK_MODULE_NAME) ; END; END IF ; GOTO end_device_loop; END IF; END IF; -- Check the notification schedule to see if the device is available IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR DEVICE_AVAILABLE(device.device_name, device.profile_name) THEN -- Check the status to make sure it is contactable IF device.status = DEVICE_UP THEN qstart := SYSDATE; IF device.type = PROGRAM_DEVICE_TYPE AND IS_WINDOWS_PROGRAM(device.program) THEN l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE; ELSE l_device_type := device.type; END IF; -- Get the queue name for this user/object combination qname := GET_QUEUE(l_device_type, device.profile_name, v_state_change.job_id); IF qname IS NOT NULL THEN -- Set up the message for the queue qMsg := MGMT_NOTIFY_NOTIFICATION(NORMAL_NOTIFICATION, IS_NOT_PING, JOB_STATE_CHANGE, v_state_change.state_change_guid, device.device_name, device.profile_name, device.type, device.rule_name, device.owner, qname, 0); IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: Queueing notification for' || ' device_name=' || dname || ' owner=' || pname, CHECK_MODULE_NAME) ; END IF ; QUEUE(qname, qMsg); TOTAL_ENQUEUE_TIME := TOTAL_ENQUEUE_TIME + (SYSDATE - qstart); -- Update the local and overall stats IF device.type IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE) THEN NUM_JOB_EMAILS_QUEUED := NUM_JOB_EMAILS_QUEUED + 1; num_emails := num_emails + 1; ELSIF device.type = PROGRAM_DEVICE_TYPE THEN NUM_JOB_OSCMDS_QUEUED := NUM_JOB_OSCMDS_QUEUED + 1; num_oscmds := num_oscmds + 1; ELSIF device.type = SNMP_DEVICE_TYPE THEN NUM_JOB_SNMPS_QUEUED := NUM_JOB_SNMPS_QUEUED + 1; num_snmp := num_snmp + 1; ELSIF device.type = PLSQL_DEVICE_TYPE THEN NUM_JOB_PLSQLS_QUEUED := NUM_JOB_PLSQLS_QUEUED + 1; num_plsql := num_plsql + 1; ELSIF device.type = JAVA_DEVICE_TYPE THEN NUM_JOB_JAVA_QUEUED := NUM_JOB_JAVA_QUEUED + 1; num_java := num_java + 1; END IF; ELSE -- Could not find a queue to service notification. Must be an -- OS Command where no Windows OMSs are registered IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: No Windows OMS to handle OS Command', CHECK_MODULE_NAME) ; END IF ; LOG_HISTORY(MGMT_GLOBAL.G_ANNOTATION_SOURCE_JOB, v_state_change.state_change_guid, 'N', 'A Windows based Management Server is not registered ' || '(Rule Owner=' || device.owner || ', Rule Name=' || device.rule_name || ')', l_tz_rgn); END IF; ELSE -- The device is currently down so set this up for retry IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: Device down so setup for retry', CHECK_MODULE_NAME) ; END IF ; INSERT INTO mgmt_notify_requeue (source_guid, source_type, notification_type, device_name, device_owner, device_type, rule_name, rule_owner) VALUES (v_state_change.state_change_guid, JOB_STATE_CHANGE, RETRY_NOTIFICATION, device.device_name, device.profile_name, device.type, device.rule_name, device.owner); END IF; ELSE IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: ' || dname || ' is not in notification schedule', CHECK_MODULE_NAME) ; END IF ; END IF; IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the severity insert -- trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; << end_device_loop >> NULL; END LOOP; IF num_emails > 0 OR num_oscmds > 0 OR num_plsql > 0 OR num_snmp > 0 OR num_java > 0 THEN t := mgmt_global.sysdate_tzrgn(l_tz_rgn); -- The message needs to be NLS'ed INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (JOB_STATE_CHANGE, v_state_change.state_change_guid, t, 'NOTIFICATION', '', 'The following notifications will be sent: ' || decode(num_emails, 0, '', num_emails || ' e-mail messages ') || decode(num_snmp, 0, '', num_snmp || ' SNMP traps ') || decode(num_plsql, 0, '', num_plsql || ' PL/SQL procedures ') || decode(num_oscmds, 0, '', num_oscmds || ' OS Commands ') || decode(num_java, 0, '', num_java || ' Java callbacks')); END IF; -- Reset the context back to the original user SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER); IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; EXCEPTION WHEN OTHERS THEN IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) ROLLBACK; END IF; -- Reset the context back to the original user SETEMUSERCONTEXT(origUser, MGMT_USER.OP_SET_IDENTIFIER); IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('QJN: Unexpected error ' || SQLERRM, CHECK_MODULE_NAME) ; END IF ; mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'QUEUE_JOB_NOTIFICATIONS (' || pname || '/' || dname || ') ' || SQLERRM, 'ORA', v_state_change.state_change_guid); END QUEUE_JOB_NOTIFICATIONS; -- -- PURPOSE -- This procedure gets a list of all devices that are currently in the -- down state and have pending notifications. It queues a notification that -- the OMS will attempt to delivery. If the delivery succeeds the OMS has -- to call the REQUEUE procedure -- PROCEDURE PING_DEVICES IS sguid RAW(16); hash_guid RAW(16); stype NUMBER; rname VARCHAR2(64); rowner VARCHAR2(256); qname VARCHAR2(30); qMsg MGMT_NOTIFY_NOTIFICATION; l_device_type NUMBER := 0; -- This only works for retries. It needs to be updated for escalation and -- repeat notifications CURSOR devices IS SELECT device_name, profile_name, type, program FROM mgmt_notify_devices d WHERE d.status = DEVICE_DOWN AND EXISTS (SELECT 1 FROM MGMT_NOTIFY_REQUEUE r WHERE r.device_name = d.device_name AND r.device_owner = d.profile_name AND (r.notification_type = RETRY_NOTIFICATION AND r.last_timestamp < SYSDATE - (r.num_requeues/1440))); BEGIN -- For each device that has notification that need to be retried FOR device IN devices LOOP IF device.type NOT IN (SHORT_EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE) OR DEVICE_AVAILABLE(device.device_name, device.profile_name) THEN -- Get the source_guid of the oldest record SELECT r1.source_guid, r1.source_type, r1.rule_name, r1.rule_owner INTO sguid, stype, rname, rowner FROM MGMT_NOTIFY_REQUEUE r1 WHERE r1.device_owner = device.profile_name AND r1.device_name = device.device_name AND r1.insertion_timestamp = (SELECT MIN(r2.insertion_timestamp) FROM MGMT_NOTIFY_REQUEUE r2 WHERE r2.device_owner = r1.device_owner AND r2.device_name = r1.device_name) AND ROWNUM = 1; BEGIN IF stype = JOB_STATE_CHANGE THEN SELECT job_id INTO hash_guid FROM MGMT_JOB_STATE_CHANGES WHERE state_change_guid = sguid; ELSE SELECT target_guid INTO hash_guid FROM MGMT_VIOLATIONS WHERE violation_guid = sguid; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- The source record is missing so remove this requeue record DELETE FROM MGMT_NOTIFY_REQUEUE WHERE device_name = device.device_name AND device_owner = device.profile_name AND source_guid = sguid AND rule_name = rname AND rule_owner = rowner; GOTO next_device; END; IF device.type = PROGRAM_DEVICE_TYPE AND IS_WINDOWS_PROGRAM(device.program) THEN l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE; ELSE l_device_type := device.type; END IF; -- Get the queue name for this user/object combination qname := GET_QUEUE(l_device_type, device.profile_name, hash_guid); IF qname IS NOT NULL THEN -- Try and deliver the notification qMsg := MGMT_NOTIFY_NOTIFICATION(RETRY_NOTIFICATION, IS_PING, stype, sguid, device.device_name, device.profile_name, device.type, rname, rowner, qname, 0); QUEUE(qname, qMsg); ELSE -- Could not find a queue to service notification. Must be an -- OS Command where no Windows OMSs are registered DELETE FROM MGMT_NOTIFY_REQUEUE WHERE device_name = device.device_name AND device_owner = device.profile_name AND source_guid = sguid AND rule_name = rname AND rule_owner = rowner; END IF; -- Update the device to indicate a ping notification has been queued so -- that no more are queued UPDATE MGMT_NOTIFY_DEVICES SET STATUS = DEVICE_BEING_PINGED, CONTACT_TIMESTAMP = SYSDATE WHERE device_name = device.device_name AND profile_name = device.profile_name; IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the severity insert -- trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; END IF; << next_device >> IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; END LOOP; -- Simple mark device as DOWN and update timestamp. UPDATE mgmt_notify_devices set status = DEVICE_DOWN, contact_timestamp = SYSDATE WHERE status = DEVICE_BEING_PINGED AND contact_timestamp < SYSDATE - ( G_DEV_PING_STATE_TIMEOUT/86400 ); IF (EMD_MAINTENANCE.IS_CENTRAL_MODE > 0) THEN -- Commit/Rollback only in central repository mode. -- In Standalone mode, this procedure is called in the -- severity insert trigger. (Commits/Rollbacks are not allowed in triggers) COMMIT; END IF; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'PING_DEVICES ' || SQLERRM); END PING_DEVICES; -- -- PURPOSE -- To requeue all notifications for a device because the device is now -- available again PROCEDURE REQUEUE(v_device_name IN VARCHAR2, v_device_owner IN VARCHAR2) IS qMsg MGMT_NOTIFY_NOTIFICATION; qname VARCHAR2(30); hash_guid RAW(16); ttl NUMBER := 1; l_program VARCHAR2(512); l_device_type NUMBER := 0; CURSOR retries IS SELECT source_guid, source_type, device_name, device_owner, device_type, rule_name, rule_owner, insertion_timestamp FROM mgmt_notify_requeue WHERE device_name = v_device_name AND device_owner = v_device_owner AND notification_type = RETRY_NOTIFICATION ORDER BY insertion_timestamp ASC; BEGIN -- Make sure to check the device is available IF NOT DEVICE_AVAILABLE(v_device_name, v_device_owner) THEN RETURN; END IF; -- Get the time to live of the notifications for this user SELECT notification_ttl INTO ttl FROM MGMT_NOTIFY_PROFILES WHERE profile_name = v_device_owner; FOR r IN retries LOOP -- Check if the entry has been in the table more than a day and if so -- remove it IF r.insertion_timestamp < SYSDATE - ttl THEN DELETE FROM mgmt_notify_requeue WHERE device_name = r.device_name AND device_owner = r.device_owner AND source_guid = r.source_guid AND rule_name = r.rule_name AND rule_owner = r.rule_owner; ELSE BEGIN IF r.source_type = JOB_STATE_CHANGE THEN SELECT job_id INTO hash_guid FROM MGMT_JOB_STATE_CHANGES WHERE state_change_guid = r.source_guid; ELSE SELECT target_guid INTO hash_guid FROM MGMT_VIOLATIONS WHERE violation_guid = r.source_guid; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- The source record is missing so remove this requeue record DELETE FROM MGMT_NOTIFY_REQUEUE WHERE device_name = r.device_name AND device_owner = r.device_owner AND source_guid = r.source_guid AND rule_name = r.rule_name AND rule_owner = r.rule_owner; GOTO next_retry; END; IF r.device_type = PROGRAM_DEVICE_TYPE THEN SELECT program INTO l_program FROM MGMT_NOTIFY_DEVICES WHERE device_name = r.device_name AND profile_name = r.device_owner; IF IS_WINDOWS_PROGRAM(l_program) THEN l_device_type := WINDOWS_PROGRAM_DEVICE_TYPE; ELSE l_device_type := r.device_type; END IF; ELSE l_device_type := r.device_type; END IF; -- Get the queue name for this user/object combination qname := GET_QUEUE(l_device_type, r.device_owner, hash_guid); IF qname IS NOT NULL THEN qMsg := MGMT_NOTIFY_NOTIFICATION(RETRY_NOTIFICATION, 0, r.source_type, r.source_guid, r.device_name, r.device_owner, r.device_type, r.rule_name, r.rule_owner, qname, 0); QUEUE(qname, qMsg); DELETE FROM mgmt_notify_requeue WHERE device_name = r.device_name AND device_owner = r.device_owner AND source_guid = r.source_guid AND rule_name = r.rule_name AND rule_owner = r.rule_owner; ELSE -- Could not find a queue to service notification. Must be an -- OS Command where no Windows OMSs are registered DELETE FROM MGMT_NOTIFY_REQUEUE WHERE device_name = r.device_name AND device_owner = r.device_owner AND source_guid = r.source_guid AND rule_name = r.rule_name AND rule_owner = r.rule_owner; END IF; END IF; << next_retry >> COMMIT; END LOOP; END REQUEUE; PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_NOTIFICATION_NAME, p_value); END DBMSJOB_EXTENDED_SQL_TRACE_ON; -- PURPOSE -- Main procedure to go through severity table, execute notification rules -- and queue notifications for delivery. This should be called from a -- DBMS_JOB that is running on a specified interval PROCEDURE CHECK_FOR_SEVERITIES IS job_id NUMBER; perf_start TIMESTAMP; sev_duration INTERVAL DAY TO SECOND; job_duration INTERVAL DAY TO SECOND; nSeverities NUMBER := 0; nStateChanges NUMBER := 0; total_queued NUMBER := 0; severity MGMT_VIOLATIONS%ROWTYPE; state_change MGMT_JOB_STATE_CHANGES%ROWTYPE; ca_severity MGMT_VIOLATIONS%ROWTYPE; guid_type VARCHAR2(1); notif_src_guid RAW(16); last_run DATE; this_run DATE; l_rule_guid RAW(16); l_repeat_count NUMBER; CURSOR job_ids IS SELECT job FROM USER_JOBS WHERE what = 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();'; BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_NOTIFICATION_NAME); EMDW_LOG.SET_CONTEXT(v_context_Type=>G_LOG_CONTEXT, v_context_Identifier=>'dbms_job'); BEGIN SELECT last_date, this_date INTO last_run, this_run from USER_JOBS WHERE what = 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES();'; EXCEPTION WHEN TOO_MANY_ROWS THEN -- There should never be more than one job running this routine OPEN job_ids; FETCH job_ids INTO job_id; CLOSE job_ids; DBMS_JOB.REMOVE(job_id); RETURN; WHEN OTHERS THEN BEGIN last_run := SYSDATE; this_run := SYSDATE; END; END; IF last_run IS NULL THEN -- It will only be NULL the first time the job runs last_run := SYSDATE; END IF; IF this_run IS NULL THEN -- It should never be null this_run := SYSDATE; END IF; -- Queue a notification for any device that is currently down that has -- pending retries. Note that this procedure will only -- attempt to queue one notification (i.e. if there are multiple retries, -- only one will be queued) and update the status accordingly. -- The procedure REQUEUE will be called from the OMS once the message has -- been delivered which will result in all pending notifications being -- queued PING_DEVICES(); perf_start := LOCALTIMESTAMP; sev_duration := perf_start - perf_start; job_duration := perf_start - perf_start; -- log job run details IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('(sysdate, last_run, this_run) = (' || to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') || ', ' || to_char(last_run, 'mm/dd/yyyy hh24:mi:ss') || ', ' || to_char(this_run, 'mm/dd/yyyy hh24:mi:ss') || ', ' || ')', 'check_for_severities'); END IF; -- Go through each severity record and apply the notification rules WHILE TRUE LOOP BEGIN perf_start := LOCALTIMESTAMP; dequeue_notif_input(notif_src_guid, guid_type, l_rule_guid, l_repeat_count); IF guid_type = GUID_TYPE_SEVERITY THEN nSeverities := nSeverities + 1; SELECT * INTO severity FROM mgmt_violations WHERE violation_guid = notif_src_guid; QUEUE_METRIC_NOTIFICATIONS(severity); ELSIF guid_type = GUID_TYPE_JOB_STATE_CHANGE THEN nStateChanges := nStateChanges + 1; SELECT * INTO state_change FROM mgmt_job_state_changes WHERE state_change_guid = notif_src_guid; IF state_change.type = JOB_STATE_CHANGE THEN QUEUE_JOB_NOTIFICATIONS(state_change); ELSE BEGIN SELECT * INTO ca_severity FROM MGMT_VIOLATIONS WHERE violation_guid = state_change.violation_guid; QUEUE_METRIC_NOTIFICATIONS(ca_severity, state_change); EXCEPTION WHEN NO_DATA_FOUND THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Severity record already deleted for state change job record : ' || notif_src_guid, 'check_for_severities'); END IF; END; END IF; ELSIF guid_type = GUID_TYPE_REP_SEVERITY THEN CHK_AND_QUEUE_REP_NOTIF(notif_src_guid, l_rule_guid, l_repeat_count); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Got NO_DATA_FOUND while processing the record notif_src_guid : ' || notif_src_guid || ' guid_type : ' || guid_type || ' Skipping this record', 'check_for_severities'); END IF; -- commit the dequeue operation COMMIT; WHEN OTHERS THEN -- exit the loop if there is no message in the queue IF SQLCODE = QUEUE_TIMEOUT THEN exit; ELSE mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'CHECK_FOR_SEVERITIES. ' || SQLERRM); -- commit the dequeue operation COMMIT; exit; END IF; END; IF guid_type = GUID_TYPE_SEVERITY THEN sev_duration := sev_duration + LOCALTIMESTAMP - perf_start; ELSE job_duration := job_duration + LOCALTIMESTAMP - perf_start; END IF; END LOOP; -- Log the stats for severities handled and number of notifications queued IF nSeverities > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, get_millisec_from_interval(sev_duration), SYSDATE, 'Y', PERF_NUM_SEVERITIES_CHECKED, nSeverities); END IF; total_queued := NUM_EMAILS_QUEUED + NUM_OSCMDS_QUEUED + NUM_PLSQLS_QUEUED + NUM_SNMPS_QUEUED + NUM_RCAS_QUEUED + NUM_JAVA_QUEUED; IF total_queued > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, (TOTAL_ENQUEUE_TIME * (24 * 60 * 60 * 1000)), SYSDATE, 'Y', PERF_NUM_QUEUED, total_queued); END IF; IF NUM_EMAILS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_EMAILS_QUEUED, NUM_EMAILS_QUEUED); END IF; IF NUM_OSCMDS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_OSCMDS_QUEUED, NUM_OSCMDS_QUEUED); END IF; IF NUM_PLSQLS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_PLSQLS_QUEUED, NUM_PLSQLS_QUEUED); END IF; IF NUM_SNMPS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_SNMPS_QUEUED, NUM_SNMPS_QUEUED); END IF; IF NUM_JAVA_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JAVA_QUEUED, NUM_JAVA_QUEUED); END IF; -- Log the stats for state changes handled and number of notifications queued IF nStateChanges > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, get_millisec_from_interval(sev_duration), SYSDATE, 'Y', PERF_NUM_JOBSTATES_CHECKED, nStateChanges); END IF; total_queued := NUM_JOB_EMAILS_QUEUED + NUM_JOB_OSCMDS_QUEUED + NUM_JOB_PLSQLS_QUEUED + NUM_JOB_SNMPS_QUEUED + NUM_JOB_JAVA_QUEUED; IF total_queued > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, (TOTAL_JOB_ENQUEUE_TIME * (24 * 60 * 60 * 1000)), SYSDATE, 'Y', PERF_NUM_JOB_QUEUED, total_queued); END IF; IF NUM_JOB_EMAILS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JOB_EMAILS_QUEUED, NUM_JOB_EMAILS_QUEUED); END IF; IF NUM_JOB_OSCMDS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JOB_OSCMDS_QUEUED, NUM_JOB_OSCMDS_QUEUED); END IF; IF NUM_JOB_PLSQLS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JOB_PLSQLS_QUEUED, NUM_JOB_PLSQLS_QUEUED); END IF; IF NUM_JOB_SNMPS_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JOB_SNMPS_QUEUED, NUM_JOB_SNMPS_QUEUED); END IF; IF NUM_JOB_JAVA_QUEUED > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0, SYSDATE, 'N', PERF_NUM_JOB_JAVA_QUEUED, NUM_JOB_JAVA_QUEUED); END IF; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; -- If we got a snapshot too old error because the query to get the -- severities ran for too long due to the amount of time spent -- processing and queuing notifications. Just return and the job -- will run again IF SQLCODE <> -1555 THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'CHECK_FOR_SEVERITIES. ' || SQLERRM); END IF; END CHECK_FOR_SEVERITIES; PROCEDURE CLEANUP_ADMIN_NOTIFICATIONS IS dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; admin_msgid RAW(16); qMsg MGMT_NOTIFY_NOTIFICATION; BEGIN -- NOTE: This should only be run once the MGMT_NOTIFY_QUEUES has -- been locked IF G_RECURSIVE_CLEANUP THEN RETURN; END IF; -- Cleanup any old admin messages for OMSs that have gone away. This -- should not be necessary if the failover callback is reliably issued -- for each OMS that goes down FOR n IN (SELECT DISTINCT msg_id, consumer_name FROM aq$mgmt_notify_qtable WHERE msg_state = 'READY' AND consumer_name LIKE 'ADM%' AND consumer_name NOT IN (SELECT 'ADM'||failover_id FROM MGMT_FAILOVER_TABLE)) LOOP dq_options.consumer_name := n.consumer_name; dq_options.msgid := n.msg_id; BEGIN DBMS_AQ.DEQUEUE(queue_name => G_QUEUE_NAME, dequeue_options => dq_options, message_properties => qMsg_properties, payload => qMsg, msgid => admin_msgid); EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; -- Unassign any queues for OMSs not in the failover table FOR oms IN (SELECT DISTINCT oms_id FROM MGMT_NOTIFY_QUEUES WHERE oms_id <> 0 AND oms_id NOT IN (SELECT failover_id FROM MGMT_FAILOVER_TABLE)) LOOP G_RECURSIVE_CLEANUP := TRUE; UNREGISTER_OMS(oms.oms_id); G_RECURSIVE_CLEANUP := FALSE; END LOOP; EXCEPTION WHEN OTHERS THEN G_RECURSIVE_CLEANUP := FALSE; raise; END CLEANUP_ADMIN_NOTIFICATIONS; PROCEDURE REGISTER_OMS(oms_id_in IN NUMBER, admin_qname_out OUT VARCHAR2, windows_in IN NUMBER DEFAULT 0) IS admin_qname VARCHAR2(30); nqueues NUMBER; nqueues_per_oms NUMBER; noms NUMBER; num_available NUMBER; num_reassigned NUMBER := 0; num_this_oms_reassigned NUMBER := 0; old_qname VARCHAR2(30); oms_ids SMP_EMD_INTEGER_ARRAY; qs_handled SMP_EMD_INTEGER_ARRAY; qMsg MGMT_NOTIFY_NOTIFICATION; device_types SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY('EMAIL%', 'OSCMD%', 'PLSQL%', 'SNMP%', 'JAVA%'); BEGIN admin_qname := 'ADM' || oms_id_in; admin_qname_out := admin_qname; -- Lock the queue table LOCK TABLE MGMT_NOTIFY_QUEUES IN EXCLUSIVE MODE; -- Cleanup any old admin messages for OMSs that have gone away. This -- should not be necessary if the failover callback is reliably issued -- for each OMS that goes down CLEANUP_ADMIN_NOTIFICATIONS; -- Get the OMS names and the number of queues they are handling SELECT oms_id, count(qname) nqs_handled BULK COLLECT INTO oms_ids, qs_handled FROM MGMT_NOTIFY_QUEUES WHERE oms_id <> 0 GROUP BY oms_id ORDER BY nqs_handled desc; noms := oms_ids.COUNT; -- If there are no OMSs registered then assign all the queues to this OMS IF noms = 0 THEN UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_id_in, windows = windows_in; FOR q IN (SELECT qname FROM MGMT_NOTIFY_QUEUES) LOOP qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, q.qname, oms_id_in, QUEUE_DEVICE_TYPE, NULL, NULL, admin_qname, 0); QUEUE(admin_qname, qMsg); END LOOP; COMMIT; RETURN; END IF; -- Get the total number of queues SELECT COUNT(qname) INTO nqueues FROM MGMT_NOTIFY_QUEUES; -- Work out how many queues there should be per OMS nqueues_per_oms := trunc(nqueues / (noms + 1)); IF nqueues_per_oms = 0 THEN COMMIT; RETURN; END IF; -- Go through each current OMS and update the queues it is handling FOR i IN oms_ids.FIRST..oms_ids.LAST LOOP -- If enough queues have been reassigned then finish IF num_reassigned >= nqueues_per_oms THEN COMMIT; RETURN; END IF; -- Work out how many queues this OMS can reassign num_available := qs_handled(i) - nqueues_per_oms; -- If it can spare more than necessary then limit it so a queue is not -- reassigned unnecessarily IF num_available > nqueues_per_oms THEN num_available := nqueues_per_oms; END IF; -- Make sure there are some available for reassigning IF num_available > 0 THEN -- Try and spread the different queue types across OMSs so that each -- one is handling a variety of device types num_this_oms_reassigned := 0; WHILE (num_this_oms_reassigned < num_available) LOOP FOR dt IN (SELECT column_value FROM (TABLE(CAST(device_types AS SMP_EMD_STRING_ARRAY)))) LOOP IF num_this_oms_reassigned = num_available THEN EXIT; END IF; BEGIN -- Get the qname being ressigned SELECT qname INTO old_qname FROM MGMT_NOTIFY_QUEUES WHERE oms_id = oms_ids(i) AND qname LIKE dt.column_value AND rownum < 2; -- Reassign the names UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_id_in, windows = windows_in WHERE qname = old_qname; -- Tell the OMS to stop monitoring the queue qMsg := MGMT_NOTIFY_NOTIFICATION(STOP_DEQUEUEING, 0, -1, NULL, old_qname, oms_ids(i), QUEUE_DEVICE_TYPE, NULL, NULL, 'ADM' || oms_ids(i), 0); QUEUE('ADM' || oms_ids(i), qMsg); qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, old_qname, oms_id_in, QUEUE_DEVICE_TYPE, NULL, NULL, admin_qname, 0); QUEUE(admin_qname, qMsg); -- Update total num_reassigned := num_reassigned + 1; num_this_oms_reassigned := num_this_oms_reassigned + 1; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END LOOP; END LOOP; END IF; END LOOP; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; END REGISTER_OMS; -- PURPOSE -- To unregister an OMS from the notification system so that its queues -- can be reassigned PROCEDURE UNREGISTER_OMS(oms_id_in IN NUMBER) IS BEGIN OMS_FAILOVER(oms_id_in, SYSDATE); END UNREGISTER_OMS; -- PURPOSE -- To handle failover when an OMS goes down PROCEDURE OMS_FAILOVER(oms_id_in IN NUMBER, last_timestamp_in IN DATE) IS noms NUMBER; num_reassigned NUMBER := 0; num_for_reassignment NUMBER := 0; new_qname VARCHAR2(30); oms_ids SMP_EMD_INTEGER_ARRAY; qs_handled SMP_EMD_INTEGER_ARRAY; is_windows SMP_EMD_INTEGER_ARRAY; qMsg MGMT_NOTIFY_NOTIFICATION; more_messages BOOLEAN := TRUE; admin_msgid RAW(16); BEGIN -- Remove any messages in the queue for this OMS's admin queue WHILE (more_messages) LOOP BEGIN DEQUEUE('ADM' || oms_id_in, 1, admin_msgid, qMsg); EXCEPTION WHEN OTHERS THEN more_messages := FALSE; END; END LOOP; -- Lock the queue table LOCK TABLE MGMT_NOTIFY_QUEUES IN EXCLUSIVE MODE; -- Unassign the queues for this OMS UPDATE MGMT_NOTIFY_QUEUES SET oms_id = 0, windows = 0 WHERE oms_id = oms_id_in; -- Cleanup any old admin messages for OMSs that have gone away. This -- should not be necessary if the failover callback is reliably issued -- for each OMS that goes down CLEANUP_ADMIN_NOTIFICATIONS; -- Get the OMS names and the number of queues they are handling SELECT oms_id, count(qname) nqs_handled, sum(windows) BULK COLLECT INTO oms_ids, qs_handled, is_windows FROM MGMT_NOTIFY_QUEUES WHERE oms_id <> 0 GROUP BY oms_id ORDER BY nqs_handled asc; noms := oms_ids.COUNT; -- If there are no OMSs registered then just return IF noms = 0 THEN RETURN; END IF; -- Get the total number of queues and the number for reassigment SELECT COUNT(qname) INTO num_for_reassignment FROM MGMT_NOTIFY_QUEUES WHERE oms_id = 0; WHILE num_reassigned < num_for_reassignment LOOP -- Go through each current OMS and assign it some queues FOR i IN oms_ids.FIRST..oms_ids.LAST LOOP IF num_reassigned = num_for_reassignment THEN RETURN; END IF; -- Get the qnames being ressigned SELECT qname INTO new_qname FROM MGMT_NOTIFY_QUEUES WHERE oms_id = 0 AND rownum < 2; -- Reassign the names IF is_windows(i) > 0 THEN UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_ids(i), windows = 1 WHERE qname = new_qname; ELSE UPDATE MGMT_NOTIFY_QUEUES SET oms_id = oms_ids(i), windows = 0 WHERE qname = new_qname; END IF; -- Tell the OMS to start servicing the new queue by sending a message -- on its admin queue qMsg := MGMT_NOTIFY_NOTIFICATION(START_DEQUEUEING, 0, -1, NULL, new_qname, oms_ids(i), QUEUE_DEVICE_TYPE, NULL, NULL, 'ADM' || oms_ids(i), 0); QUEUE('ADM' || oms_ids(i), qMsg); -- Update total num_reassigned := num_reassigned + 1; END LOOP; END LOOP; EXCEPTION WHEN OTHERS THEN MGMT_LOG.log_error(DELIVER_MODULE_NAME, SQLCODE, 'Error during failover: ' || SQLERRM); raise; END OMS_FAILOVER; -- PURPOSE -- Set the number of notification queues -- -- PARAMETERS -- PROCEDURE SET_NUMBER_QUEUES( v_num_email_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_EMAIL_QUEUES, v_num_oscmd_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_OSCMD_QUEUES, v_num_plsql_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_PLSQL_QUEUES, v_num_snmp_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_SNMP_QUEUES, v_num_java_qs IN NUMBER DEFAULT DEFAULT_NOTIFY_JAVA_QUEUES) IS BEGIN NULL; END SET_NUMBER_QUEUES; FUNCTION GET_PARAMETER_FORMATS(v_device_name IN VARCHAR2, v_device_owner IN VARCHAR2) RETURN MGMT_NOTIFY_PARAMETER_FORMATS IS formats MGMT_NOTIFY_PARAMETER_FORMATS; BEGIN -- Get the parameters that should be included in the notifications SELECT MGMT_NOTIFY_PARAMETER_FORMAT(parameter, abbreviated, timestamp_format) BULK COLLECT INTO formats FROM MGMT_NOTIFY_DEVICE_PARAMS WHERE device_name = v_device_name AND profile_name = v_device_owner AND parameter LIKE '$%' ORDER BY position; -- If no parameter customization has been done, then send all the parameters IF formats.COUNT = 0 THEN formats.extend(12); formats(1) := MGMT_NOTIFY_PARAMETER_FORMAT('$TARGET_NAME', 0, NULL); formats(2) := MGMT_NOTIFY_PARAMETER_FORMAT('$TARGET_TYPE', 0, NULL); formats(3) := MGMT_NOTIFY_PARAMETER_FORMAT('$HOST', 0, NULL); formats(4) := MGMT_NOTIFY_PARAMETER_FORMAT('$METRIC', 0, NULL); formats(5) := MGMT_NOTIFY_PARAMETER_FORMAT('$METRIC_DESCRIPTION', 0, NULL); formats(6) := MGMT_NOTIFY_PARAMETER_FORMAT('$KEY_VALUE', 0, NULL); formats(7) := MGMT_NOTIFY_PARAMETER_FORMAT('$TIMESTAMP', 0, NULL); formats(8) := MGMT_NOTIFY_PARAMETER_FORMAT('$SEVERITY', 0, NULL); formats(9) := MGMT_NOTIFY_PARAMETER_FORMAT('$MESSAGE', 0, NULL); formats(10) := MGMT_NOTIFY_PARAMETER_FORMAT('$RULE_NAME', 0, NULL); formats(11) := MGMT_NOTIFY_PARAMETER_FORMAT('$RULE_OWNER', 0, NULL); END IF; RETURN formats; END GET_PARAMETER_FORMATS; -- PURPOSE -- To get the label for a key column belonging to the metric in a severity -- FUNCTION GET_KEYS(v_metric_guid IN RAW, v_target_guid IN RAW, v_key_value_in IN VARCHAR2) RETURN MGMT_NOTIFY_COLUMNS IS kcol VARCHAR2(512); key VARCHAR2(64); pos NUMBER; lpos NUMBER; label VARCHAR2(64); nlsid VARCHAR2(64); nkeys NUMBER; checking boolean; keys MGMT_NOTIFY_COLUMNS := MGMT_NOTIFY_COLUMNS(); p1 VARCHAR2(256); p2 VARCHAR2(256); p3 VARCHAR2(256); p4 VARCHAR2(256); p5 VARCHAR2(256); key_val VARCHAR2(256); BEGIN IF v_key_value_in = ' ' THEN RETURN NULL; END IF; -- Get the metric name and column SELECT key_column INTO kcol FROM MGMT_TARGETS t, MGMT_METRICS m WHERE t.target_guid = v_target_guid AND m.metric_guid = v_metric_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' '); BEGIN -- Check it this is a composite key SELECT key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value INTO p1, p2, p3, p4, p5 FROM MGMT_METRICS_COMPOSITE_KEYS WHERE composite_key = v_key_value_in AND target_guid = v_target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN -- Not a composite key, so get the details of the key column SELECT m1.column_label, m1.column_label_nlsid INTO label, nlsid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1 WHERE t.target_guid = v_target_guid AND m.metric_guid = v_metric_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND t.target_type = m1.target_type AND m1.type_meta_ver = t.type_meta_ver AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ') AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ') AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ') AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ') AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ') AND m1.metric_name = m.metric_name AND m1.metric_column = kcol; IF label IS NULL OR label = ' ' THEN label := key; END IF; keys.extend; keys(1) := MGMT_NOTIFY_COLUMN(label, nlsid, v_key_value_in); RETURN keys; END; nkeys := 1; lpos := 0; checking := true; WHILE checking LOOP pos := INSTR(kcol,';', 1, nkeys); IF pos = 0 THEN checking := false; pos := LENGTH(kcol) + 1; END IF; key := SUBSTR(kcol, lpos+1, pos-lpos-1); SELECT m1.column_label, m1.column_label_nlsid INTO label, nlsid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1 WHERE t.target_guid = v_target_guid AND m.metric_guid = v_metric_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND t.target_type = m1.target_type AND m1.type_meta_ver = t.type_meta_ver AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ') AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ') AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ') AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ') AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ') AND m1.metric_name = m.metric_name AND m1.metric_column = key; IF nkeys = 1 THEN key_val := p1; ELSIF nkeys = 2 THEN key_val := p2; ELSIF nkeys = 3 THEN key_val := p3; ELSIF nkeys = 4 THEN key_val := p4; ELSIF nkeys = 5 THEN key_val := p5; END IF; IF label IS NULL OR label = ' ' THEN label := key; END IF; keys.extend; keys(keys.COUNT) := MGMT_NOTIFY_COLUMN(label, nlsid, key_val); lpos := pos; nkeys := nkeys + 1; END LOOP; RETURN keys; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END GET_KEYS; -- PURPOSE -- Procedure to specify one or more SMTP gateways -- PROCEDURE SET_EMAIL_GATEWAYS(v_gateways IN MGMT_MEDIUM_STRING_ARRAY, v_smtp_ports IN MGMT_INTEGER_ARRAY, v_email_address IN VARCHAR2, v_email_name IN VARCHAR2, v_smtp_user IN VARCHAR2 DEFAULT '', v_smtp_pwd IN VARCHAR2 DEFAULT '') IS BEGIN -- Make sure some gateways have been entered IF v_gateways IS NULL OR v_gateways.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR, MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR_MSG); END IF; DELETE FROM mgmt_notify_email_gateway; FOR i IN v_gateways.FIRST..v_gateways.LAST LOOP INSERT INTO mgmt_notify_email_gateway (MAIL_HOST, SMTP_PORT, EMAIL_ADDRESS, EMAIL_NAME, SMTP_USER, SMTP_PWD, PRECEDENCE) VALUES (v_gateways(i), v_smtp_ports(i), v_email_address, v_email_name, v_smtp_user, ENCRYPT(v_smtp_pwd), i); END LOOP; END SET_EMAIL_GATEWAYS; -- PURPOSE -- Procedure to specify a number of email gateways. -- PROCEDURE SET_EMAIL_GATEWAYS(v_gateways IN SMP_EMD_STRING_ARRAY, v_email_address IN VARCHAR2, v_email_name IN VARCHAR2) IS BEGIN -- Make sure some gateways have been entered IF v_gateways IS NULL OR v_gateways.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR, MGMT_GLOBAL.NOTIFICATION_NOGATEWAY_ERR_MSG); END IF; DELETE FROM mgmt_notify_email_gateway; FOR i IN v_gateways.FIRST..v_gateways.LAST LOOP INSERT INTO mgmt_notify_email_gateway (MAIL_HOST, EMAIL_ADDRESS, EMAIL_NAME, PRECEDENCE) VALUES (v_gateways(i), v_email_address, v_email_name, i); END LOOP; END SET_EMAIL_GATEWAYS; -- Procedure to add an administrator profile. This will raise an exception -- if there is already a profile with the same name PROCEDURE ADD_ADMIN_PROFILE(v_profile_name IN VARCHAR2, v_description IN VARCHAR2) IS BEGIN INSERT INTO mgmt_notify_profiles (PROFILE_NAME, DESCRIPTION) VALUES (UPPER(v_profile_name), v_description); END ADD_ADMIN_PROFILE; -- PURPOSE -- Procedure to add the name of an administrator profile that will serve -- as an escalation point. PROCEDURE SET_ESCALATION_PROFILE(v_profile_name IN VARCHAR2, v_escalation_profile_name IN VARCHAR2, v_escalation_interval IN NUMBER) IS BEGIN UPDATE MGMT_NOTIFY_PROFILES SET escalation_profile_name = UPPER(v_escalation_profile_name), escalation_interval = v_escalation_interval WHERE profile_name = v_profile_name; END SET_ESCALATION_PROFILE; -- PURPOSE -- Procedure to delete the name of an administrator profile that will serve -- as an escalation point. PROCEDURE DELETE_ESCALATION_PROFILE(v_profile_name IN VARCHAR2) IS BEGIN UPDATE MGMT_NOTIFY_PROFILES SET escalation_profile_name = '', escalation_interval = '' WHERE profile_name = UPPER(v_profile_name); END DELETE_ESCALATION_PROFILE; -- PURPOSE -- Procedure to add an email notification device to an administrator profile -- PROCEDURE ADD_EMAIL_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_email_address IN VARCHAR2, v_type IN NUMBER DEFAULT LONG_EMAIL_DEVICE_TYPE, v_description IN VARCHAR2 DEFAULT '') IS BEGIN BEGIN INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, EMAIL_ADDRESS, DEVICE_DESCRIPTION) VALUES (v_device_name, UPPER(v_profile_name), v_type, v_email_address, v_description); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END; END ADD_EMAIL_DEVICE; -- PURPOSE -- Procedure to change the email address of an existing email notification -- device that was added via ADD_EMAIL_DEVICE -- PROCEDURE EDIT_EMAIL_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_email_address IN VARCHAR2, v_type IN NUMBER DEFAULT LONG_EMAIL_DEVICE_TYPE, v_description IN VARCHAR2 DEFAULT '') IS dname VARCHAR2(132); BEGIN UPDATE MGMT_NOTIFY_DEVICES SET EMAIL_ADDRESS = v_email_address, DEVICE_DESCRIPTION = v_description, TYPE = v_type WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) AND TYPE IN (LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE) RETURNING DEVICE_NAME INTO dname; IF dname IS NULL THEN ROLLBACK; raise_application_error(-20002, v_profile_name || ' does not have an EMAIL device named ' || v_device_name || '!'); END IF; END EDIT_EMAIL_DEVICE; -- PURPOSE -- Procedure to add a program/script notification device to an administrator -- profile -- PROCEDURE ADD_PROGRAM_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_program IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '', v_args IN EMDSYSTEM_ARGS DEFAULT NULL) IS BEGIN INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, DEVICE_DESCRIPTION ) VALUES (v_device_name, UPPER(v_profile_name), PROGRAM_DEVICE_TYPE, v_program, v_description); If v_args IS NOT NULL and v_args.COUNT > 0 THEN FOR i IN v_args.FIRST..v_args.LAST LOOP INSERT INTO mgmt_notify_device_params (device_name, profile_name, parameter, position) VALUES (v_device_name, v_profile_name, v_args(i), i); END LOOP; END IF; END ADD_PROGRAM_DEVICE; -- PURPOSE -- Procedure to change the program/script of an existing notification device -- device that was added via ADD_PROGRAM_DEVICE -- PROCEDURE EDIT_PROGRAM_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_program IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '', v_args IN EMDSYSTEM_ARGS DEFAULT NULL) IS dname VARCHAR2(132); BEGIN UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_program, DEVICE_DESCRIPTION = v_description WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) AND TYPE = PROGRAM_DEVICE_TYPE RETURNING DEVICE_NAME INTO dname; IF dname IS NULL THEN ROLLBACK; raise_application_error(-20002, v_profile_name || ' does not have a PROGRAM device named ' || v_device_name || '!'); END IF; -- Always delete the parameters DELETE FROM MGMT_NOTIFY_DEVICE_PARAMS WHERE device_name = v_device_name AND profile_name = v_profile_name; If v_args IS NOT NULL and v_args.COUNT > 0 THEN FOR i IN v_args.FIRST..v_args.LAST LOOP INSERT INTO mgmt_notify_device_params (device_name, profile_name, parameter, position) VALUES (v_device_name, v_profile_name, v_args(i), i); END LOOP; END IF; END EDIT_PROGRAM_DEVICE; -- PURPOSE -- Procedure to add a java callback notification device to an administrator -- profile -- PARAMETERS -- V_DEVICE_NAME - the name of the device -- V_PROFILE_NAME - the name of the admin profile -- V_CLASS - this is the name of fully qualified class name that will be invoked -- on delivering the notification. Can be used to integrate -- other problem tracking systems -- V_DESCRIPTION - the description of the device PROCEDURE ADD_JAVA_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_class IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '', v_context IN MGMT_MEDIUM_STRING_TABLE DEFAULT NULL) IS l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER; BEGIN -- Ensure that the caller is a super-user. IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = 0 THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only superusers can call ADD_JAVA_DEVICE'); END IF; INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, DEVICE_DESCRIPTION ) VALUES (v_device_name, UPPER(v_profile_name), JAVA_DEVICE_TYPE, v_class, v_description); IF v_context IS NOT NULL and v_context.COUNT > 0 THEN FOR i IN v_context.FIRST..v_context.LAST LOOP INSERT INTO mgmt_notify_device_params (device_name, profile_name, parameter, position) VALUES (v_device_name, UPPER(v_profile_name), v_context(i), i); END LOOP; END IF; END ADD_JAVA_DEVICE; -- -- PROCEDURE: EDIT_JAVA_DEVICE -- -- PURPOSE: -- Modify the existing notification device of type Java. -- Update p_class, p_description and context for the given p_device_name and p_profile_name -- New context will override the previous context -- -- IN Parameters: -- v_device_name : Name of the notification device -- v_profile_name : The name of the admin profile -- v_class : Fully qualified name of the class implementing the notification interface -- v_description : Description for this device -- v_context : String array of context that will be passed to the notify method while delivering the notification. -- -- OUT Parameters: -- NONE -- PROCEDURE EDIT_JAVA_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_class IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '', v_context IN MGMT_MEDIUM_STRING_TABLE DEFAULT NULL) IS dname VARCHAR2(132); l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER; BEGIN -- Ensure that the caller is a super-user. IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = 0 THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only superusers can call MODIFY_JAVA_DEVICE'); END IF; UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_class, DEVICE_DESCRIPTION = v_description WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) AND TYPE = JAVA_DEVICE_TYPE RETURNING DEVICE_NAME INTO dname; IF dname IS NULL THEN ROLLBACK; raise_application_error(-20002, v_profile_name || ' does not have a JAVA device named ' || v_device_name || '!'); END IF; -- Always delete the parameters DELETE FROM MGMT_NOTIFY_DEVICE_PARAMS WHERE device_name = v_device_name AND profile_name = UPPER(v_profile_name); IF v_context IS NOT NULL and v_context.COUNT > 0 THEN FOR i IN v_context.FIRST..v_context.LAST LOOP INSERT INTO mgmt_notify_device_params (device_name, profile_name, parameter, position) VALUES (v_device_name, UPPER(v_profile_name), v_context(i), i); END LOOP; END IF; END EDIT_JAVA_DEVICE; -- PURPOSE -- Function to test a PL/SQL notification device to see if it can be -- called FUNCTION TEST_PLSQL_DEVICE(v_plsql_proc IN VARCHAR2, v_err_msg OUT VARCHAR2) RETURN NUMBER IS sev MGMT_NOTIFY_SEVERITY; jsc MGMT_NOTIFY_JOB; casc MGMT_NOTIFY_CORRECTIVE_ACTION; BEGIN -- Make sure the procedure is valid before trying to execute it IF NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_SEVERITY'), v_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_JOB'), v_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_CORRECTIVE_ACTION'), v_err_msg) THEN -- The procedure is not valid so return failure RETURN 0; END IF; BEGIN -- Try a severity first sev := MGMT_NOTIFY_SEVERITY('Filesystem Rule','SYSMAN', 'myhost.com', 'host', '-07:00', 'myhost.com', 'Filesystems', 'Size of the filesystem', 'Filesystem Size (MB)', '94', 'Mount Point', '/usr', SYS_GUID(), SYSDATE, 20, MGMT_GLOBAL.NOTIFICATION_PLSQL_TEST_MSG, SYS_GUID(), SYS_GUID(), SYS_GUID(), NULL); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)' USING sev; RETURN 1; EXCEPTION WHEN OTHERS THEN -- If the error is ORA-06576: not a valid function or procedure name IF SQLCODE = -6576 THEN v_err_msg := SQLERRM; RETURN 0; END IF; END; BEGIN -- Try a job notification jsc := MGMT_NOTIFY_JOB('Job Rule', 'SYSMAN', 'My Job', 'SYSMAN', 'OSCommand', 1, SYSDATE, SYS_GUID(), SYS_GUID(), SYS_GUID(), SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('myhost.com', 'host'))); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)' USING jsc; RETURN 1; EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN -- Try a corrective action notification casc := MGMT_NOTIFY_CORRECTIVE_ACTION ('Filesystem Rule', 'SYSMAN', 'Fix Filesystem', 'SYSMAN', 'OSCommand', 1, SYSDATE, SYS_GUID(), SYS_GUID(), SYS_GUID(), SMP_EMD_NVPAIR_ARRAY(SMP_EMD_NVPAIR('myhost.com', 'host')), 'Filesystems', 'Filesystem Size (MB)', '94', 25, 'Mount Point', '/usr', SYS_GUID(), NULL); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(v_plsql_proc) || '(:s)' USING casc; RETURN 1; EXCEPTION WHEN OTHERS THEN v_err_msg := SQLERRM; RETURN 0; END; END TEST_PLSQL_DEVICE; -- PURPOSE -- Procedure to add a PL/SQL notification device to an administrator -- profile PROCEDURE ADD_PLSQL_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_plsql_proc IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '') IS l_err_msg VARCHAR2(1024); BEGIN -- Make sure the procedure is valid before saving it IF NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_SEVERITY'), l_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_JOB'), l_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_CORRECTIVE_ACTION'), l_err_msg) THEN -- The procedure is not valid so raise an exception RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_err_msg); END IF; INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, PROGRAM, DEVICE_DESCRIPTION) VALUES (v_device_name, UPPER(v_profile_name), PLSQL_DEVICE_TYPE, v_plsql_proc, v_description); END ADD_PLSQL_DEVICE; -- PURPOSE -- Procedure to change the name of the PL/SQL procedure of an existing -- notification device that was added via ADD_PLSQL_DEVICE PROCEDURE EDIT_PLSQL_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_plsql_proc IN VARCHAR2, v_description IN VARCHAR2 DEFAULT '') IS dname VARCHAR2(132); l_err_msg VARCHAR2(1024); BEGIN -- Make sure the procedure is valid before saving it IF NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_SEVERITY'), l_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_JOB'), l_err_msg) AND NOT em_check.is_valid_signature(v_plsql_proc, mgmt_short_string_array('MGMT_NOTIFY_CORRECTIVE_ACTION'), l_err_msg) THEN -- The procedure is not valid so raise an exception RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_err_msg); END IF; UPDATE MGMT_NOTIFY_DEVICES SET PROGRAM = v_plsql_proc, DEVICE_DESCRIPTION = v_description WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) AND TYPE = PLSQL_DEVICE_TYPE RETURNING DEVICE_NAME INTO dname; IF dname IS NULL THEN ROLLBACK; raise_application_error(-20002, v_profile_name || ' does not have a PL/SQL device named ' || v_device_name || '!'); END IF; END EDIT_PLSQL_DEVICE; -- PURPOSE -- Procedure to add an SNMP notification device to an administrator profile PROCEDURE ADD_SNMP_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_snmp_host IN VARCHAR2, v_snmp_port IN NUMBER DEFAULT 162, v_community IN VARCHAR2 DEFAULT 'public', v_description IN VARCHAR2 DEFAULT '') IS BEGIN INSERT INTO mgmt_notify_devices (DEVICE_NAME, PROFILE_NAME, TYPE, SNMP_HOST, SNMP_PORT, SNMP_COMMUNITY, DEVICE_DESCRIPTION) VALUES (v_device_name, UPPER(v_profile_name), SNMP_DEVICE_TYPE, v_snmp_host, v_snmp_port, v_community, v_description); END ADD_SNMP_DEVICE; -- PURPOSE -- Procedure to change the snmp host of an existing SNMP notification -- device that was added via ADD_SNMP_DEVICE -- PARAMETERS -- V_DEVICE_NAME - the name of the device -- V_PROFILE_NAME - the name of the admin profile -- V_SNMP_HOST - the new name of the snmp host PROCEDURE EDIT_SNMP_DEVICE(v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2, v_snmp_host IN VARCHAR2, v_snmp_port IN NUMBER DEFAULT 162, v_community IN VARCHAR2 DEFAULT 'public', v_description IN VARCHAR2 DEFAULT '') IS dname VARCHAR2(132); BEGIN UPDATE MGMT_NOTIFY_DEVICES SET SNMP_HOST = v_snmp_host, SNMP_PORT = v_snmp_port, SNMP_COMMUNITY = v_community, DEVICE_DESCRIPTION = v_description WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) AND TYPE = SNMP_DEVICE_TYPE RETURNING DEVICE_NAME INTO dname; IF dname IS NULL THEN ROLLBACK; raise_application_error(-20002, v_profile_name || ' does not have an SNMP device named ' || v_device_name || '!'); END IF; END EDIT_SNMP_DEVICE; -- PURPOSE -- Get the device owner for device - v_device_name of type v_type -- PARAMETERS -- V_DEVICE_NAME - the name of the device -- V_TYPE - the type of the device FUNCTION GET_DEVICE_OWNER(v_device_name IN VARCHAR2, v_type IN INTEGER) RETURN VARCHAR2 IS CURSOR dOwnerCursor IS SELECT profile_name FROM mgmt_notify_devices WHERE device_name=v_device_name AND type=v_type; deviceOwner VARCHAR2(256) := ''; BEGIN FOR record IN dOwnerCursor LOOP deviceOwner := record.profile_name; exit; END LOOP; IF( deviceOwner IS NULL ) THEN deviceOwner := ''; END IF; RETURN deviceOwner; END GET_DEVICE_OWNER; -- PURPOSE -- Procedure to set the start date of a user's notification schedule -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule -- V_START_DATE - the date on which the schedule starts. Note that the hour -- and minutes are ignored since the schedule starts at 12:00 -- V_NUM_WEEKS - the number of weeks after which the schedule repeats -- V_TIMEZONE_REGION - the timezone region of the schedule owner -- PROCEDURE SET_SCHEDULE(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ', v_start_date IN DATE, v_num_weeks IN NUMBER DEFAULT 1, v_timezone_region IN VARCHAR2 DEFAULT TO_CHAR(SYSTIMESTAMP,'TZR')) IS sowner VARCHAR2(256); owner VARCHAR2(256); cuser VARCHAR2(256); email VARCHAR2(128); err_msg VARCHAR2(1024); r BOOLEAN; BEGIN sowner := UPPER(v_schedule_owner); cuser := MGMT_USER.GET_CURRENT_EM_USER(); -- In general the schedule will be updated, so try updating first. If the -- schedule does not exist then create it UPDATE MGMT_NOTIFY_SCHEDULES SET start_date = TRUNC(v_start_date), num_weeks = v_num_weeks, updated_by = cuser, updated = SYSDATE, timezone_region = v_timezone_region WHERE schedule_owner = sowner AND schedule_name = v_schedule_name RETURNING schedule_owner INTO owner; -- Check to make sure the update succeeded IF owner IS NULL THEN INSERT INTO MGMT_NOTIFY_SCHEDULES (schedule_owner, schedule_name, start_date, num_weeks, updated_by, updated, timezone_region) VALUES (sowner, v_schedule_name, TRUNC(v_start_date), v_num_weeks, cuser, SYSDATE, v_timezone_region); END IF; END SET_SCHEDULE; -- PURPOSE -- Procedure to get the user who last updated a notification schedule -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule -- V_UPDATED_BY - the user who updated the schedule -- V_UPDATED - when the update took place -- PROCEDURE WHO_UPDATED_SCHEDULE(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ', v_updated_by OUT VARCHAR2, v_updated OUT DATE) IS sowner VARCHAR2(256); BEGIN sowner := UPPER(v_schedule_owner); SELECT updated_by, updated INTO v_updated_by, v_updated FROM MGMT_NOTIFY_SCHEDULES WHERE schedule_owner = sowner AND schedule_name = v_schedule_name; END WHO_UPDATED_SCHEDULE; -- PURPOSE -- Procedure to disable a user's notification schedule for a period of time -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule -- V_DISABLE_START - the date on which the disabling period starts -- V_DISABLE_END - the date on which the disabling period ends -- -- NOTES -- To clear a disabling period pass NULL in for both v_disable_start and -- v_disable_end -- PROCEDURE DISABLE_SCHEDULE(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ', v_disable_start IN DATE, v_disable_end IN DATE) IS sowner VARCHAR2(256); cuser VARCHAR2(256); email VARCHAR2(128); err_msg VARCHAR2(1024); r BOOLEAN; BEGIN sowner := UPPER(v_schedule_owner); cuser := MGMT_USER.GET_CURRENT_EM_USER(); -- Keep track of who did the update UPDATE MGMT_NOTIFY_SCHEDULES SET disable_start = v_disable_start, disable_end = v_disable_end, updated_by = cuser, updated = SYSDATE WHERE schedule_owner = sowner AND schedule_name = v_schedule_name; END DISABLE_SCHEDULE; -- PURPOSE -- Procedure to set the notification schedule of a notification device -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule -- V_DEVICE_NAME - the name of the device -- V_DEVICE_OWNER - the name of the admin profile -- V_SCHEDULE - the hours in this schedule when the device is available. -- This must be an array of 1344 characters PROCEDURE SET_DEVICE_SCHEDULE(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ', v_device_name IN VARCHAR2, v_device_owner IN VARCHAR2, v_schedule IN VARCHAR2) IS sowner VARCHAR2(256); downer VARCHAR2(256); cuser VARCHAR2(256); email VARCHAR2(128); err_msg VARCHAR2(1024); r BOOLEAN; BEGIN sowner := UPPER(v_schedule_owner); downer := UPPER(v_device_owner); cuser := MGMT_USER.GET_CURRENT_EM_USER(); -- Delete the current schedule DELETE FROM MGMT_NOTIFY_DEV_SCHEDULES WHERE schedule_owner = sowner AND schedule_name = v_schedule_name AND device_owner = downer AND device_name = v_device_name; IF v_schedule IS NOT NULL AND (LENGTH(v_schedule) > 0) THEN INSERT INTO mgmt_notify_dev_schedules (SCHEDULE_OWNER, SCHEDULE_NAME, DEVICE_NAME, DEVICE_OWNER, SCHEDULE) VALUES (sowner, v_schedule_name, v_device_name, downer, v_schedule); END IF; -- Keep track of who did the update UPDATE MGMT_NOTIFY_SCHEDULES SET updated_by = cuser, updated = SYSDATE WHERE schedule_owner = sowner AND schedule_name = v_schedule_name; END SET_DEVICE_SCHEDULE; -- PURPOSE -- Procedure to clear a user's schedule -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule PROCEDURE CLEAR_SCHEDULE(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ') IS BEGIN -- Delete the device schedules DELETE FROM MGMT_NOTIFY_DEV_SCHEDULES WHERE schedule_owner = v_schedule_owner AND schedule_name = v_schedule_name; -- Delete the schedule DELETE FROM MGMT_NOTIFY_SCHEDULES WHERE schedule_owner = v_schedule_owner AND schedule_name = v_schedule_name; END CLEAR_SCHEDULE; -- PURPOSE -- Function to check if a user has a schedule set -- PARAMETERS -- V_SCHEDULE_OWNER - the owner of the schedule -- V_SCHEDULE_NAME - the name of the schedule FUNCTION USER_SCHEDULE_SET(v_schedule_owner IN VARCHAR2, v_schedule_name IN VARCHAR2 DEFAULT ' ') RETURN NUMBER IS cnt NUMBER; BEGIN -- Check if a schedule exists SELECT COUNT(schedule_owner) INTO cnt FROM MGMT_NOTIFY_SCHEDULES WHERE schedule_owner = v_schedule_owner AND schedule_name = v_schedule_name AND EXISTS (SELECT schedule_name FROM MGMT_NOTIFY_DEV_SCHEDULES WHERE schedule_owner = v_schedule_owner AND schedule_name = v_schedule_name); IF cnt = 0 THEN RETURN 0; END IF; RETURN 1; END USER_SCHEDULE_SET; -- Procedure to add a notification rule PROCEDURE ADD_NOTIFICATION_RULE(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_description IN VARCHAR2, v_public_rule IN NUMBER DEFAULT 0, v_repeat_enabled IN NUMBER DEFAULT 0) IS BEGIN -- Note that the owner column is not in use yet INSERT INTO mgmt_notify_rules (rule_name, owner, description, public_rule, repeat) VALUES (v_rule_name, v_owner, v_description, v_public_rule, v_repeat_enabled); END ADD_NOTIFICATION_RULE; -- -- PURPOSE -- Procedure to add a configuration to a rule. This allows the specification of -- the severity properties to which the rule applies. -- -- NOTES -- For composite keys v_is_composite_key must be set to 1, the v_key_value -- parameter must contain the composite GUID, the associated key parts -- must be specified in v_key_part1..v_key_part5 and the target type must -- be specified in v_target_guid -- PROCEDURE ADD_CONFIGURATION_TO_RULE (v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_target_type IN VARCHAR2 DEFAULT '%', v_target_name IN VARCHAR2 DEFAULT '%', v_group_guid IN VARCHAR2 DEFAULT NULL, v_metric_name IN VARCHAR2 DEFAULT '%', v_metric_column IN VARCHAR2 DEFAULT '%', v_key_value IN VARCHAR2 DEFAULT '%', v_is_composite_key IN NUMBER DEFAULT 0, v_target_guid IN RAW DEFAULT NULL, v_key_part1 IN VARCHAR2 DEFAULT '', v_key_part2 IN VARCHAR2 DEFAULT '', v_key_part3 IN VARCHAR2 DEFAULT '', v_key_part4 IN VARCHAR2 DEFAULT '', v_key_part5 IN VARCHAR2 DEFAULT '', v_want_clears IN NUMBER DEFAULT 0, v_want_warnings IN NUMBER DEFAULT 0, v_want_critical_alerts IN NUMBER DEFAULT 0, v_want_target_up IN NUMBER DEFAULT 0, v_want_target_down IN NUMBER DEFAULT 0, v_want_target_unreachable_st IN NUMBER DEFAULT 0, v_want_target_unreachable_end IN NUMBER DEFAULT 0, v_want_target_metric_err_start IN NUMBER DEFAULT 0, v_want_target_metric_err_end IN NUMBER DEFAULT 0, v_want_target_blackout_start IN NUMBER DEFAULT 0, v_want_target_blackout_end IN NUMBER DEFAULT 0, v_want_policy_clears IN NUMBER DEFAULT 0, v_want_policy_violations IN NUMBER DEFAULT 0, v_want_warning_job_succeeded IN NUMBER DEFAULT 0, v_want_warning_job_problems IN NUMBER DEFAULT 0, v_want_critical_job_succeeded IN NUMBER DEFAULT 0, v_want_critical_job_problems IN NUMBER DEFAULT 0, v_want_policy_job_succeeded IN NUMBER DEFAULT 0, v_want_policy_job_problems IN NUMBER DEFAULT 0, v_ignore_rca IN NUMBER DEFAULT 0 ) IS BEGIN -- This is a composite key match with wildcards INSERT INTO mgmt_notify_rule_configs (RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID, METRIC_NAME, METRIC_COLUMN, KEY_VALUE, KEY_PART_1, KEY_PART_2, KEY_PART_3, KEY_PART_4, KEY_PART_5, WANT_CLEARS, WANT_WARNINGS, WANT_CRITICAL_ALERTS, WANT_TARGET_UP, WANT_TARGET_DOWN, WANT_TARGET_UNREACHABLE_START, WANT_TARGET_UNREACHABLE_END, WANT_TARGET_METRIC_ERR_START, WANT_TARGET_METRIC_ERR_END, WANT_TARGET_BLACKOUT_START, WANT_TARGET_BLACKOUT_END, WANT_POLICY_CLEARS, WANT_POLICY_VIOLATIONS, WANT_WARNING_JOB_SUCCEEDED, WANT_WARNING_JOB_PROBLEMS, WANT_CRITICAL_JOB_SUCCEEDED, WANT_CRITICAL_JOB_PROBLEMS, WANT_POLICY_JOB_SUCCEEDED, WANT_POLICY_JOB_PROBLEMS, IGNORE_RCA) VALUES (v_rule_name, v_owner, v_target_type, v_target_name, v_group_guid, v_metric_name, v_metric_column, v_key_value, NVL(v_key_part1, ' '), NVL(v_key_part2, ' '),NVL(v_key_part3, ' '), NVL(v_key_part4, ' '), NVL(v_key_part5, ' '), v_want_clears, v_want_warnings, v_want_critical_alerts, v_want_target_up, v_want_target_down, v_want_target_unreachable_st, v_want_target_unreachable_end, v_want_target_metric_err_start, v_want_target_metric_err_end, v_want_target_blackout_start, v_want_target_blackout_end, v_want_policy_clears, v_want_policy_violations, v_want_warning_job_succeeded, v_want_warning_job_problems, v_want_critical_job_succeeded, v_want_critical_job_problems, v_want_policy_job_succeeded, v_want_policy_job_problems, v_ignore_rca); -- Check if this is a composite key IF v_is_composite_key = 1 THEN BEGIN -- Store the key and its associated key parts INSERT INTO MGMT_METRICS_COMPOSITE_KEYS (composite_key, target_guid, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (v_key_value, v_target_guid, v_key_part1, v_key_part2, v_key_part3, v_key_part4, v_key_part5); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- The key already exists to ignore the error NULL; END; END IF; END ADD_CONFIGURATION_TO_RULE; -- PURPOSE -- Procedure to add a configuration to a job rule. This allows the -- specification of the job properties to which the rule applies. This is also -- used for corrective actions in which case the metric details are also -- supplied PROCEDURE ADD_CONFIGURATION_TO_JOB_RULE (v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_target_type IN VARCHAR2 DEFAULT '%', v_target_name IN VARCHAR2 DEFAULT '%', v_group_guid IN VARCHAR2 DEFAULT NULL, v_job_name IN VARCHAR2 DEFAULT '%', v_job_owner IN VARCHAR2 DEFAULT '%', v_job_type IN VARCHAR2 DEFAULT '%', v_want_job_scheduled IN NUMBER DEFAULT 0, v_want_job_running IN NUMBER DEFAULT 0, v_want_job_succeeded IN NUMBER DEFAULT 0, v_want_job_suspended IN NUMBER DEFAULT 0, v_want_job_problems IN NUMBER DEFAULT 0 ) IS BEGIN INSERT INTO mgmt_notify_job_rule_configs (RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID, JOB_NAME, JOB_OWNER, JOB_TYPE, WANT_JOB_SCHEDULED, WANT_JOB_RUNNING, WANT_JOB_SUCCEEDED, WANT_JOB_SUSPENDED, WANT_JOB_PROBLEMS) VALUES (v_rule_name, v_owner, v_target_type, v_target_name, v_group_guid, UPPER(v_job_name), UPPER(v_job_owner), v_job_type, v_want_job_scheduled, v_want_job_running, v_want_job_succeeded, v_want_job_suspended, v_want_job_problems); END ADD_CONFIGURATION_TO_JOB_RULE; -- PURPOSE -- Procedure to set the properties of a rule PROCEDURE SET_RULE_PROPERTIES (v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_description IN VARCHAR2, v_public_rule IN NUMBER DEFAULT 0, v_repeat_enabled IN NUMBER DEFAULT 0) IS rname VARCHAR2(64); BEGIN UPDATE mgmt_notify_rules p SET p.public_rule = v_public_rule, p.description = v_description, p.repeat = v_repeat_enabled WHERE p.rule_name = v_rule_name AND p.owner = v_owner; END SET_RULE_PROPERTIES; -- PURPOSE -- Procedure to make a rule public -- PARAMETERS PROCEDURE MAKE_RULE_PUBLIC(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_make_public IN NUMBER) IS BEGIN UPDATE MGMT_NOTIFY_RULES SET public_rule = v_make_public WHERE rule_name = v_rule_name AND owner = v_owner; END MAKE_RULE_PUBLIC; -- PURPOSE -- Procedure to enable/disable repeat setting for a rule -- PARAMETERS -- P_RULE_NAME - name of the notification rule -- P_OWNER - the owner of the notification rule -- P_ENABLE_REPEAT - whether to enable or disable the repeat. PROCEDURE SET_RULE_REPEAT(p_rule_name IN VARCHAR2, p_owner IN VARCHAR2, p_enable_repeat IN NUMBER) IS l_rule_name mgmt_notify_rules.rule_name%type := NULL; BEGIN UPDATE MGMT_NOTIFY_RULES SET repeat = p_enable_repeat WHERE rule_name = p_rule_name AND owner = p_owner returning rule_name INTO l_rule_name; IF(l_rule_name IS NULL) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, ' No Notification Rule exists for the combination of rule_name = ' || p_rule_name || ' and owner = ' || p_owner) ; END IF; END SET_RULE_REPEAT; -- Procedure to assign notification devices to a notification rule PROCEDURE ADD_DEVICE_TO_RULE(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2) IS BEGIN INSERT INTO mgmt_notify_notifyees (RULE_NAME, OWNER, DEVICE_NAME, PROFILE_NAME) VALUES (v_rule_name, v_owner, v_device_name, UPPER(v_profile_name)); END ADD_DEVICE_TO_RULE; -- PURPOSE -- Function to check if a notification rule exists -- PARAMETERS -- V_RULE_NAME - name of the notification rule -- V_OWNER - the owner of the notification rule FUNCTION RULE_EXISTS(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2) RETURN INTEGER IS rname VARCHAR2(64); BEGIN SELECT rule_name INTO rname FROM MGMT_NOTIFY_RULES WHERE rule_name = v_rule_name AND owner = v_owner; IF rname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END RULE_EXISTS; -- PURPOSE -- Function to find out if the email gateway has been setup -- by the super user -- -- NOTES -- 0 is returned if no gateway has been set up, 1 is returned if a -- gateway has been set up -- FUNCTION IS_MAIL_GATEWAY_SETUP RETURN NUMBER IS cnt NUMBER := 0; BEGIN SELECT COUNT(mail_host) INTO cnt FROM MGMT_NOTIFY_EMAIL_GATEWAY; IF cnt > 0 THEN RETURN 1; ELSE RETURN 0; END IF; END IS_MAIL_GATEWAY_SETUP; -- PURPOSE -- Procedure to delete the configurations of a notification rule. PROCEDURE DELETE_RULE_CONFIGURATIONS(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2) IS BEGIN DELETE FROM MGMT_NOTIFY_RULE_CONFIGS WHERE RULE_NAME = v_rule_name AND OWNER = UPPER(v_owner); DELETE FROM MGMT_NOTIFY_JOB_RULE_CONFIGS WHERE RULE_NAME = v_rule_name AND OWNER = UPPER(v_owner); END DELETE_RULE_CONFIGURATIONS; -- PURPOSE -- Procedure to delete a specific configuration of a notification rule -- PARAMETERS -- V_RULE_NAME - name of the notification rule -- V_OWNER - the owner of the notification rule -- V_TARGET_NAME - the name of the target -- V_TARGET_TYPE - the type of the target -- V_TARGET_NAME - the name of the metric -- V_TARGET_NAME - the name of the metric column -- V_TARGET_NAME - the key value -- -- NOTES -- This is used to delete rule configurations when a UDM or transposed -- metric is deleted. Only configurations that exactly match all input -- parameters will be deleted PROCEDURE DELETE_RULE_CONFIGURATION(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2, v_key_value IN VARCHAR2) IS BEGIN DELETE FROM MGMT_NOTIFY_RULE_CONFIGS WHERE RULE_NAME = v_rule_name AND OWNER = UPPER(v_owner) AND TARGET_NAME = v_target_name AND TARGET_TYPE = v_target_type AND METRIC_NAME = v_metric_name AND METRIC_COLUMN = v_metric_column AND KEY_VALUE = v_key_value; END DELETE_RULE_CONFIGURATION; -- PURPOSE -- Procedure to remove notification devices from a notification rule -- PARAMETERS -- V_RULE_NAME - name of the notification rule -- V_DEVICE_NAME - the name of the device -- V_PROFILE_NAME - the name of the admin profile PROCEDURE DELETE_DEVICE_FROM_RULE(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_device_name IN VARCHAR2, v_profile_name IN VARCHAR2) IS rname VARCHAR2(64); BEGIN DELETE FROM MGMT_NOTIFY_NOTIFYEES WHERE RULE_NAME = v_rule_name AND OWNER = v_owner AND DEVICE_NAME = v_device_name AND PROFILE_NAME = UPPER(v_profile_name) RETURNING RULE_NAME INTO rname; END DELETE_DEVICE_FROM_RULE; -- PURPOSE -- Procedure to remove notification devices from a notification rule -- Note that this procedure does not take in a profile_name. This is needed -- for deleting snmp device or program command device -- PARAMETERS -- V_RULE_NAME - name of the notification rule -- V_OWNER - the owner of the notification rule -- V_DEVICE_NAME - the name of the device PROCEDURE DELETE_DEVICE_FROM_RULE(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_device_name IN VARCHAR2) IS rname VARCHAR2(64); BEGIN DELETE FROM MGMT_NOTIFY_NOTIFYEES WHERE RULE_NAME = v_rule_name AND OWNER = v_owner AND DEVICE_NAME = v_device_name RETURNING RULE_NAME INTO rname; END DELETE_DEVICE_FROM_RULE; -- PURPOSE -- Procedure to delete an administrator profile PROCEDURE DELETE_ADMIN_PROFILE(v_profile_name IN VARCHAR2) IS log_msg VARCHAR2(512); pname VARCHAR2(64); BEGIN DELETE FROM MGMT_NOTIFY_PROFILES WHERE PROFILE_NAME = UPPER(v_profile_name) RETURNING PROFILE_NAME INTO pname; IF pname IS NULL THEN raise_application_error(-20001, 'There is no administrator profile named ' || v_profile_name || '!'); END IF; END DELETE_ADMIN_PROFILE; -- PURPOSE -- Procedure to delete a notification device associated with an administrator -- profile PROCEDURE DELETE_DEVICE(v_profile_name IN VARCHAR2, v_device_name IN VARCHAR2) IS dname VARCHAR2(132); dev_type NUMBER; BEGIN DELETE FROM MGMT_NOTIFY_DEVICES WHERE PROFILE_NAME = UPPER(v_profile_name) AND DEVICE_NAME = v_device_name RETURNING DEVICE_NAME INTO dname; DELETE FROM MGMT_NOTIFY_NOTIFYEES WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = v_profile_name; END DELETE_DEVICE; -- PURPOSE -- Procedure to delete a notification program device associated with an -- administrator profile -- NOTE: This is a UI specific API and could change depending on the -- UI design. -- PARAMETERS -- V_PROFILE_NAME - name of the notification rule -- V_DEVICE_NAME - name of the device PROCEDURE DELETE_PROGRAM_DEVICE(v_profile_name IN VARCHAR2, v_device_name IN VARCHAR2) IS pname VARCHAR2(64); BEGIN DELETE FROM MGMT_NOTIFY_DEVICES WHERE PROFILE_NAME = UPPER(v_profile_name) AND DEVICE_NAME = v_device_name AND (TYPE = PROGRAM_DEVICE_TYPE OR TYPE = PLSQL_DEVICE_TYPE ) RETURNING PROFILE_NAME INTO pname; DELETE FROM MGMT_NOTIFY_NOTIFYEES WHERE DEVICE_NAME = v_device_name AND PROFILE_NAME = v_profile_name; END DELETE_PROGRAM_DEVICE; -- PURPOSE -- Procedure to delete a notification rule PROCEDURE DELETE_NOTIFICATION_RULE(v_rule_name IN VARCHAR2, v_owner IN VARCHAR2, v_super_user IN VARCHAR2) IS rname VARCHAR2(64); dName VARCHAR2(132) := ''; BEGIN DELETE FROM MGMT_NOTIFY_RULES WHERE rule_name = v_rule_name AND owner = UPPER(v_owner) RETURNING RULE_NAME INTO rname; IF rname IS NULL THEN raise_application_error(-20000, v_owner || ' has no notification rule name d ' || v_rule_name || '!'); END IF; END DELETE_NOTIFICATION_RULE; -- PURPOSE -- Get the details of a violation for sending in a notification PROCEDURE GET_VIOLATION_DETAILS(violation_guid_in IN RAW, language_in IN VARCHAR2, country_in IN VARCHAR2, details_out OUT MGMT_MAX_STRING_ARRAY, collection_timestamp_out OUT DATE, logged_timestamp_out OUT DATE, keys_out OUT MGMT_NOTIFY_COLUMNS, ctxt_list_out OUT MGMT_NOTIFY_COLUMNS, is_plsql_notification IN BOOLEAN DEFAULT FALSE) IS hname VARCHAR2(256); kval VARCHAR2(256); coll_time VARCHAR2(64); tz_region VARCHAR2(64); tguid RAW(16); mguid RAW(16); pguid RAW(16); tgt_lt DATE; ctime DATE; ltime DATE; l_violation_type NUMBER; l_value NUMBER; l_metric_label VARCHAR2(64); l_column_label VARCHAR2(64); l_label VARCHAR2(64); l_nlsid VARCHAR2(64); l_policy_nlsid VARCHAR2(64); BEGIN details_out := MGMT_MAX_STRING_ARRAY(); details_out.extend(17); -- note: use target_name as display_name because we don't expose -- target display_name BEGIN SELECT tgt.target_name, tgt.target_type, tgt.target_name, tgt.type_display_name, tgt.timezone_region, tgt.host_name, tgt.target_guid, tgt.last_load_time, s.violation_level, s.message, s.message_nlsid, s.message_params, s.key_value, s.collection_timestamp, s.load_timestamp, s.violation_type INTO details_out(1), details_out(2), details_out(3), details_out(4), details_out(5), hname, tguid, tgt_lt, details_out(12), details_out(13), details_out(14), details_out(15), kval, ctime, ltime, l_violation_type FROM MGMT_TARGETS tgt, MGMT_VIOLATIONS s WHERE s.violation_guid = violation_guid_in AND tgt.target_guid = s.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN details_out := NULL; IF emdw_log.p_is_debug_set THEN emdw_log.debug('GVD: invalid target: violation_guid = ' || violation_guid_in, DELIVER_MODULE_NAME) ; END IF; RETURN; END; -- note: use target_name as display_name because we don't expose -- target display_name details_out(6) := hname; -- BEGIN -- SELECT display_name INTO details_out(6) FROM MGMT_TARGETS -- WHERE target_name = hname -- AND target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE -- AND rownum < 2; -- EXCEPTION -- WHEN NO_DATA_FOUND -- THEN -- details_out(6) := hname; -- END; IF l_violation_type = MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY THEN BEGIN SELECT p.policy_name, '', p.policy_label_nlsid, '', p.description, p.policy_guid, p.metric_guid INTO details_out(7), details_out(8), l_policy_nlsid, details_out(10), details_out(11), pguid, mguid FROM MGMT_POLICIES p, MGMT_VIOLATIONS v WHERE v.violation_guid = violation_guid_in AND p.policy_guid = v.policy_guid; details_out(9) := MGMT_MESSAGE.GET_MESSAGE(l_policy_nlsid, 'POLICY', language_in, country_in, details_out(7)); EXCEPTION WHEN NO_DATA_FOUND THEN details_out := NULL; IF emdw_log.p_is_debug_set THEN emdw_log.debug('GVD: invalid policy: violation_guid = ' || violation_guid_in, DELIVER_MODULE_NAME) ; END IF; RETURN; END; ELSE BEGIN -- Get the metric details_out SELECT m.metric_name, m.metric_column, m.metric_label_nlsid, m.column_label_nlsid, m.description, m.metric_label, m.column_label, v.policy_guid, m.metric_guid INTO details_out(7), details_out(8), details_out(9), details_out(10), details_out(11), l_metric_label, l_column_label, pguid, mguid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_VIOLATIONS v WHERE v.violation_guid = violation_guid_in AND t.target_guid = tguid -- Apparently v.policy_guid should be used to lookup mgmt_policies -- from where the metric_guid can be taken. This means joining on -- mgmt_policies rather than going straight to mgmt_metrics AND m.metric_guid = v.policy_guid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' '); IF is_plsql_notification THEN details_out(9) := l_metric_label; details_out(10) := l_column_label; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN details_out := NULL; IF emdw_log.p_is_debug_set THEN emdw_log.debug('GVD: invalid metric: violation_guid = ' || violation_guid_in, DELIVER_MODULE_NAME) ; END IF; RETURN; END; END IF; -- Get the context ctxt_list_out := MGMT_NOTIFY_COLUMNS(); FOR ctx IN (SELECT column_name, column_type, decode(column_type, 0, to_char(column_value), 1, column_str_value) col_value FROM mgmt_violation_context WHERE target_guid = tguid AND policy_guid = pguid AND key_value = kval AND collection_timestamp = ctime) LOOP SELECT m1.column_label, m1.column_label_nlsid INTO l_label, l_nlsid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1 WHERE t.target_guid = tguid AND m.metric_guid = mguid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND t.target_type = m1.target_type AND m1.type_meta_ver = t.type_meta_ver AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ') AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ') AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ') AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ') AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ') AND m1.metric_name = m.metric_name AND m1.metric_column = ctx.column_name; ctxt_list_out.extend; ctxt_list_out(ctxt_list_out.COUNT) := MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value); END LOOP; -- Get the metric value SELECT value, string_value INTO l_value, details_out(16) FROM MGMT_VIOLATIONS WHERE violation_guid = violation_guid_in; IF details_out(16) IS NULL THEN details_out(16) := l_value; END IF; keys_out := GET_KEYS(mguid, tguid, kval); -- Convert to centiseconds to represent the uptime of the target details_out(17) := TRUNC((SYSDATE - tgt_lt) * (24*60*60*100)); -- pass out the collection timestamp as a date collection_timestamp_out := ctime; logged_timestamp_out := ltime; END GET_VIOLATION_DETAILS; FUNCTION GET_KEY_VALUE_NAME(keys_in IN MGMT_NOTIFY_COLUMNS) RETURN VARCHAR2 IS key_value_name VARCHAR2(512); BEGIN IF keys_in IS NULL OR keys_in.COUNT = 0 THEN RETURN NULL; END IF; FOR i in keys_in.FIRST..keys_in.LAST LOOP IF i > 1 THEN key_value_name := key_value_name || ';'; END IF; key_value_name := key_value_name || keys_in(i).column_label; END LOOP; RETURN key_value_name; END GET_KEY_VALUE_NAME; FUNCTION GET_KEY_VALUE(keys_in IN MGMT_NOTIFY_COLUMNS) RETURN VARCHAR2 IS key_value VARCHAR2(1290); BEGIN IF keys_in IS NULL OR keys_in.COUNT = 0 THEN RETURN NULL; END IF; FOR i in keys_in.FIRST..keys_in.LAST LOOP IF i > 1 THEN key_value := key_value || ';'; END IF; key_value := key_value || keys_in(i).value; END LOOP; RETURN key_value; END GET_KEY_VALUE; -- PURPOSE -- To send a metric severity notification to a PL/SQL procedure PROCEDURE METRIC_SEVERITY_FOR_PLSQL(violation_guid_in IN RAW, language_in IN VARCHAR2, country_in IN VARCHAR2, device_owner_in IN VARCHAR2, device_name_in IN VARCHAR2, rule_owner_in IN VARCHAR2, rule_name_in IN VARCHAR2, procedure_name_out OUT VARCHAR2, error_code_out OUT NUMBER, error_out OUT VARCHAR2) IS pname VARCHAR2(512); sev MGMT_NOTIFY_SEVERITY; details MGMT_MAX_STRING_ARRAY; cs DATE; ls DATE; mguid RAW(16); tguid RAW(16); kguid VARCHAR2(256); keys MGMT_NOTIFY_COLUMNS; ctxt_list MGMT_NOTIFY_COLUMNS; BEGIN BEGIN -- Get the name of the procedure SELECT program INTO pname FROM MGMT_NOTIFY_DEVICES WHERE profile_name = device_owner_in AND device_name = device_name_in; EXCEPTION WHEN NO_DATA_FOUND THEN error_code_out := NO_DEVICE_ERR; procedure_name_out := NULL; RETURN; END; error_code_out := 0; procedure_name_out := pname; -- Get the details of the severity GET_VIOLATION_DETAILS(violation_guid_in, language_in, country_in, details, cs, ls, keys, ctxt_list, TRUE); IF details IS NULL THEN error_code_out := NO_SEVERITY_ERR; RETURN; END IF; SELECT target_guid, policy_guid, key_value INTO tguid, mguid, kguid FROM MGMT_VIOLATIONS WHERE violation_guid = violation_guid_in; sev := MGMT_NOTIFY_SEVERITY(rule_name_in, rule_owner_in, details(3), details(4), details(5), details(6), details(9), details(11), details(10), details(14), GET_KEY_VALUE_NAME(keys), GET_KEY_VALUE(keys), kguid, cs, details(12), details(13), violation_guid_in, tguid, mguid, ctxt_list); -- Set the module and action DBMS_APPLICATION_INFO.SET_MODULE(pname, violation_guid_in); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname) || '(:s)' USING sev; LOG_DELIVERY_TIME(cs, ls, details(5), PLSQL_DEVICE_TYPE); -- Clear the module and action DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL); EXCEPTION WHEN OTHERS THEN -- Clear the module and action DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL); error_code_out := SQLCODE; error_out := SQLERRM; procedure_name_out := pname; END METRIC_SEVERITY_FOR_PLSQL; -- PURPOSE -- Get the details of a job state change for sending in a notification PROCEDURE GET_JOB_STATE_CHANGE_DETAILS(state_change_guid_in IN RAW, language_in IN VARCHAR2, country_in IN VARCHAR2, details_out OUT MGMT_NOTIFY_JOB_SC) IS target_names MGMT_MEDIUM_STRING_ARRAY; target_types MGMT_MEDIUM_STRING_ARRAY; timezones MGMT_SHORT_STRING_ARRAY; stat NUMBER(2); tguid RAW(16); mguid RAW(16); pguid RAW(16); coll_ts DATE; metric_name VARCHAR2(64); metric_column VARCHAR2(64); label VARCHAR2(64); label_nlsid VARCHAR2(64); policy VARCHAR2(64); mtype NUMBER; str_val VARCHAR2(1024); val NUMBER; vl NUMBER; kval VARCHAR2(256); keys MGMT_NOTIFY_COLUMNS; ctxt_list MGMT_NOTIFY_COLUMNS; l_label VARCHAR2(64); l_nlsid VARCHAR2(64); l_policy_name VARCHAR2(128); l_policy_nlsid VARCHAR2(64); l_job_type MGMT_JOB_TYPE_INFO.job_type_default%TYPE; l_job_type_nlsid MGMT_JOB_TYPE_INFO.job_type_nlsid%TYPE; l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE; l_tz_rgn MGMT_TARGETS.timezone_region%TYPE; BEGIN -- There will only be one record FOR sc IN (SELECT sc.*, j.job_name, j.job_owner FROM MGMT_JOB_STATE_CHANGES sc, MGMT_JOB j WHERE sc.state_change_guid = state_change_guid_in AND j.job_id = sc.job_id AND j.job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING) LOOP -- Get the target details SELECT DISTINCT tgt.target_name, tgt.target_type, tgt.timezone_region BULK COLLECT INTO target_names, target_types, timezones FROM MGMT_JOB_EXT_TARGETS jobtgt, MGMT_TARGETS tgt WHERE jobtgt.job_id = sc.job_id AND jobtgt.execution_id = sc.execution_id AND tgt.target_guid = jobtgt.target_guid; IF target_names IS NULL OR target_names.COUNT = 0 THEN details_out := NULL; IF emdw_log.p_is_debug_set THEN emdw_log.debug('GJD: targetless : job_id = ' || sc.job_id, DELIVER_MODULE_NAME) ; END IF; -- Targetless Job, use emdrep timezone BEGIN select TIMEZONE_REGION into l_tz_rgn from MGMT_TARGETS where target_type='oracle_emrep'; EXCEPTION WHEN NO_DATA_FOUND THEN l_tz_rgn := TO_CHAR(SYSTIMESTAMP,'TZR'); END; ELSE -- Job with target, use target's timezone l_tz_rgn := timezones(1); END IF; -- Get the job type BEGIN l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(sc.job_id); SELECT NVL(job_type_default, job_type), job_type_nlsid INTO l_job_type, l_job_type_nlsid FROM MGMT_JOB_TYPE_INFO WHERE job_type_id = l_job_type_id; EXCEPTION WHEN NO_DATA_FOUND THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('GJD: GET_JOB_STATE_CHANGE_DETAILS: Job type not ' || 'found for this job_id = ' || sc.job_id || ', job_type_id = ' || l_job_type_id, DELIVER_MODULE_NAME); END IF; END; -- Check if this is for a job IF sc.type = JOB_STATE_CHANGE THEN details_out := MGMT_NOTIFY_JOB_SC(sc.job_name, l_job_type, l_job_type_nlsid, sc.job_owner, target_names, target_types, l_tz_rgn, sc.logged, sc.occurred, sc.newstate, sc.job_id, sc.execution_id, sc.step_id, NULL, NULL, NULL, NULL, NULL,0, NULL, NULL); -- Check if it is for a CA run as a result of a metric severity ELSIF sc.type = METRIC_CA_STATE_CHANGE THEN -- Get the severity details SELECT target_guid, policy_guid, key_value, collection_timestamp, value, string_value, violation_level INTO tguid, pguid, kval, coll_ts, val, str_val, vl FROM MGMT_VIOLATIONS WHERE violation_guid = sc.violation_guid; mguid := pguid; -- Get the details of the metric SELECT m.metric_name, m.metric_column, m.column_label, m.column_label_nlsid, m.metric_type INTO metric_name, metric_column, label, label_nlsid, mtype FROM MGMT_TARGETS t, MGMT_METRICS m WHERE t.target_guid = tguid AND m.metric_guid = mguid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' '); IF mtype = 0 THEN str_val := val; END IF; -- Get the context ctxt_list := MGMT_NOTIFY_COLUMNS(); FOR ctx IN (SELECT column_name, column_type, decode(column_type, 0, to_char(column_value), 1, column_str_value) col_value FROM mgmt_violation_context WHERE target_guid = tguid AND policy_guid = pguid AND key_value = kval AND collection_timestamp = coll_ts) LOOP SELECT m1.column_label, m1.column_label_nlsid INTO l_label, l_nlsid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1 WHERE t.target_guid = tguid AND m.metric_guid = mguid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND t.target_type = m1.target_type AND m1.type_meta_ver = t.type_meta_ver AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ') AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ') AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ') AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ') AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ') AND m1.metric_name = m.metric_name AND m1.metric_column = ctx.column_name; ctxt_list.extend; ctxt_list(ctxt_list.COUNT) := MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value); END LOOP; details_out := MGMT_NOTIFY_JOB_SC(sc.job_name, l_job_type, l_job_type_nlsid, sc.job_owner, target_names, target_types, timezones(1), sc.logged, sc.occurred, sc.newstate, sc.job_id, sc.execution_id, sc.step_id, label, label_nlsid, metric_name, metric_column, str_val, vl, GET_KEYS(mguid, tguid, kval), ctxt_list); -- Check if it is for a CA run as a result of a policy violation ELSIF sc.type = POLICY_CA_STATE_CHANGE THEN -- Get the violation details SELECT target_guid, policy_guid, key_value, collection_timestamp, value, string_value, violation_level INTO tguid, pguid, kval, coll_ts, val, str_val, vl FROM MGMT_VIOLATIONS WHERE violation_guid = sc.violation_guid; -- Get the details of the policy SELECT policy_name, policy_label_nlsid, metric_guid INTO l_policy_name, l_policy_nlsid, mguid FROM MGMT_POLICIES WHERE policy_guid = pguid; policy := MGMT_MESSAGE.GET_MESSAGE(l_policy_nlsid, 'POLICY', language_in, country_in, l_policy_name); IF str_val IS NULL THEN str_val := val; END IF; -- Get the context ctxt_list := MGMT_NOTIFY_COLUMNS(); FOR ctx IN (SELECT column_name, column_type, decode(column_type, 0, to_char(column_value), 1, column_str_value) col_value FROM mgmt_violation_context WHERE target_guid = tguid AND policy_guid = pguid AND key_value = kval AND collection_timestamp = coll_ts) LOOP SELECT m1.column_label, m1.column_label_nlsid INTO l_label, l_nlsid FROM MGMT_TARGETS t, MGMT_METRICS m, MGMT_METRICS m1 WHERE t.target_guid = tguid AND m.metric_guid = mguid AND t.target_type = m.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 or m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 or m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 or m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 or m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 or m.category_prop_5 = ' ') AND t.target_type = m1.target_type AND m1.type_meta_ver = t.type_meta_ver AND (m1.category_prop_1 = t.category_prop_1 or m1.category_prop_1 = ' ') AND (m1.category_prop_2 = t.category_prop_2 or m1.category_prop_2 = ' ') AND (m1.category_prop_3 = t.category_prop_3 or m1.category_prop_3 = ' ') AND (m1.category_prop_4 = t.category_prop_4 or m1.category_prop_4 = ' ') AND (m1.category_prop_5 = t.category_prop_5 or m1.category_prop_5 = ' ') AND m1.metric_name = m.metric_name AND m1.metric_column = ctx.column_name; ctxt_list.extend; ctxt_list(ctxt_list.COUNT) := MGMT_NOTIFY_COLUMN(l_label, l_nlsid, ctx.col_value); END LOOP; details_out := MGMT_NOTIFY_JOB_SC(sc.job_name, l_job_type, l_job_type_nlsid, sc.job_owner, target_names, target_types, timezones(1), sc.logged, sc.occurred, sc.newstate, sc.job_id, sc.execution_id, sc.step_id, policy, l_policy_nlsid, policy, ' ', str_val, vl, GET_KEYS(mguid, tguid, kval), ctxt_list); END IF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('GJD: unexpected error ' || SQLERRM, DELIVER_MODULE_NAME) ; END IF; details_out := NULL; END GET_JOB_STATE_CHANGE_DETAILS; -- PURPOSE -- To send a job/ca state change notification to a PL/SQL procedure PROCEDURE STATE_CHANGE_FOR_PLSQL(state_change_guid_in IN RAW, language_in IN VARCHAR2, country_in IN VARCHAR2, device_owner_in IN VARCHAR2, device_name_in IN VARCHAR2, rule_owner_in IN VARCHAR2, rule_name_in IN VARCHAR2, procedure_name_out OUT VARCHAR2, error_code_out OUT NUMBER, error_out OUT VARCHAR2) IS pname VARCHAR2(512); change MGMT_NOTIFY_JOB; ca_change MGMT_NOTIFY_CORRECTIVE_ACTION; details MGMT_NOTIFY_JOB_SC; targets SMP_EMD_NVPAIR_ARRAY := SMP_EMD_NVPAIR_ARRAY(); is_ca NUMBER; kguid RAW(16); BEGIN BEGIN -- Get the name of the procedure SELECT program INTO pname FROM MGMT_NOTIFY_DEVICES WHERE profile_name = device_owner_in AND device_name = device_name_in; EXCEPTION WHEN NO_DATA_FOUND THEN error_code_out := NO_DEVICE_ERR; procedure_name_out := NULL; RETURN; END; error_code_out := 0; procedure_name_out := pname; -- Get the details of the state change GET_JOB_STATE_CHANGE_DETAILS(state_change_guid_in, language_in, country_in, details); IF details IS NULL THEN error_code_out := NO_STATE_CHANGE_ERR; RETURN; END IF; IF details.target_names IS NOT NULL AND details.target_names.COUNT > 0 THEN targets.extend(details.target_names.COUNT); FOR i IN details.target_names.FIRST..details.target_names.LAST LOOP targets(i) := SMP_EMD_NVPAIR(details.target_names(i), details.target_types(i)); END LOOP; END IF; SELECT is_corrective_action INTO is_ca FROM MGMT_JOB WHERE job_id = details.job_id AND job_status != MGMT_JOBS.JOB_STATUS_DELETE_PENDING; IF is_ca = 0 THEN change := MGMT_NOTIFY_JOB(rule_name_in, rule_owner_in, details.job_name, details.job_owner, details.job_type, details.status, details.occurred, state_change_guid_in, details.job_id, details.execution_id, targets); -- Set the module and action DBMS_APPLICATION_INFO.SET_MODULE(pname, state_change_guid_in); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname) || '(:s)' USING change; ELSE BEGIN SELECT v.key_value INTO kguid FROM MGMT_JOB_STATE_CHANGES sc, MGMT_VIOLATIONS v WHERE sc.state_change_guid = state_change_guid_in AND v.violation_guid = sc.violation_guid; EXCEPTION WHEN OTHERS THEN NULL; END; ca_change := MGMT_NOTIFY_CORRECTIVE_ACTION (rule_name_in, rule_owner_in, details.job_name, details.job_owner, details.job_type, details.status, details.occurred, state_change_guid_in, details.job_id, details.execution_id, targets, details.metric_name, details.metric_column, details.metric_value, details.violation_level, GET_KEY_VALUE_NAME(details.keys), GET_KEY_VALUE(details.keys), kguid, details.ctxt_list); -- Set the module and action DBMS_APPLICATION_INFO.SET_MODULE(pname, state_change_guid_in); EXECUTE IMMEDIATE 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(pname) || '(:s)' USING ca_change; END IF; -- Clear the module and action DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm); error_code_out := SQLCODE; error_out := SQLERRM; procedure_name_out := pname; END STATE_CHANGE_FOR_PLSQL; -- PURPOSE -- To set a notification up to be retried PROCEDURE SETUP_FOR_RETRY(notification_in IN MGMT_NOTIFY_NOTIFICATION, error_in IN VARCHAR2) IS BEGIN -- If this was already a retry then update the stats IF notification_in.notification_type = RETRY_NOTIFICATION THEN UPDATE MGMT_NOTIFY_REQUEUE SET last_timestamp = SYSDATE, num_requeues = num_requeues + 1 WHERE device_name = notification_in.device_name AND device_owner = notification_in.device_owner AND source_guid = notification_in.source_guid AND rule_name = notification_in.rule_name AND rule_owner = notification_in.rule_owner; ELSE INSERT INTO MGMT_NOTIFY_REQUEUE (source_guid, source_type, notification_type, device_name, device_owner, device_type, rule_name, rule_owner) VALUES (notification_in.source_guid, notification_in.source_type, RETRY_NOTIFICATION, notification_in.device_name, notification_in.device_owner, notification_in.device_type, notification_in.rule_name, notification_in.rule_owner); END IF; -- Set the device to be down UPDATE MGMT_NOTIFY_DEVICES SET STATUS = DEVICE_DOWN, STATUS_MESSAGE = substr(error_in,1,512), CONTACT_TIMESTAMP = SYSDATE WHERE device_name = notification_in.device_name AND profile_name = notification_in.device_owner; END; -- PURPOSE -- Called when a device has been successfully pinged PROCEDURE PING_SUCCEEDED(notification_in IN MGMT_NOTIFY_NOTIFICATION) IS BEGIN -- Delete the requeue record DELETE FROM MGMT_NOTIFY_REQUEUE WHERE device_name = notification_in.device_name AND device_owner = notification_in.device_owner AND source_guid = notification_in.source_guid AND rule_name = notification_in.rule_name AND rule_owner = notification_in.rule_owner; -- Set the device to be up UPDATE MGMT_NOTIFY_DEVICES SET STATUS = DEVICE_UP, STATUS_MESSAGE = '', CONTACT_TIMESTAMP = SYSDATE WHERE device_name = notification_in.device_name AND profile_name = notification_in.device_owner; -- Requeue any pending notifications REQUEUE(notification_in.device_name, notification_in.device_owner); END PING_SUCCEEDED; -- PURPOSE -- Log the delivery time for a severity; PROCEDURE LOG_DELIVERY_TIME(collection_time_in IN DATE, load_time_in IN DATE, tz_region_in IN VARCHAR2, device_type_in IN NUMBER, oms_name_in IN VARCHAR2 DEFAULT NULL) IS now DATE; delivery_time NUMBER := 0; dt VARCHAR2(32); tdt VARCHAR2(32); errmsg VARCHAR2(256); err NUMBER; BEGIN dt := PERF_DELIVERY_TIME; tdt := PERF_TOTAL_DELIVERY_TIME; BEGIN IF device_type_in IN (EMAIL_DEVICE_TYPE, LONG_EMAIL_DEVICE_TYPE, SHORT_EMAIL_DEVICE_TYPE) THEN dt := PERF_EMAIL_DELIVERY_TIME; tdt := PERF_EMAIL_TOTAL_DELIVERY_TIME; ELSIF device_type_in = PROGRAM_DEVICE_TYPE THEN dt := PERF_OSCMD_DELIVERY_TIME; tdt := PERF_OSCMD_TOTAL_DELIVERY_TIME; ELSIF device_type_in = PLSQL_DEVICE_TYPE THEN dt := PERF_PLSQL_DELIVERY_TIME; tdt := PERF_PLSQL_TOTAL_DELIVERY_TIME; ELSIF device_type_in = JAVA_DEVICE_TYPE THEN dt := PERF_JAVA_DELIVERY_TIME; tdt := PERF_JAVA_TOTAL_DELIVERY_TIME; ELSIF device_type_in = SNMP_DEVICE_TYPE THEN dt := PERF_SNMP_DELIVERY_TIME; tdt := PERF_SNMP_TOTAL_DELIVERY_TIME; mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, 0,'device not found '||dt||' '||tdt,'','','','', MGMT_GLOBAL.G_WARN); END IF; delivery_time := (SYSDATE-load_time_in) * (24*60*60*1000); IF delivery_time > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.DELIVER_MODULE_NAME, delivery_time, SYSDATE, 'N', dt, delivery_time, NULL, NULL, device_type_in, oms_name_in); END IF; -- Log the time it took from the severity being loaded into -- the MGMT_VIOLATIONS table until the notification being delivered BEGIN now := mgmt_global.sysdate_tzrgn(tz_region_in); -- Log the time it took from the severity being created until the -- notification was delivered. Note that sysdate is converted to the -- target timezone delivery_time := (now-collection_time_in) * (24*60*60*1000); IF delivery_time > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.DELIVER_MODULE_NAME, delivery_time, SYSDATE, 'N', tdt, delivery_time, NULL, NULL, device_type_in, oms_name_in); END IF; EXCEPTION WHEN OTHERS THEN -- There is no point logging the time since we do not have the -- current time correctly changed into the target timezone NULL; END; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, err,'ERROR '||errmsg|| ' during emd_notification.log_delivery_time','', device_type_in,oms_name_in,'',MGMT_GLOBAL.G_ERROR); err := SQLCODE; errmsg := SUBSTR(SQLERRM,1,256); IF err <> OLDERR OR OLDERRTIME < SYSDATE-(1/24) THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, err,'ERROR '||errmsg|| ' during emd_notification.log_delivery_time','', device_type_in,oms_name_in,'',MGMT_GLOBAL.G_ERROR); OLDERR := err; OLDERRTIME := SYSDATE; END IF; END; END LOG_DELIVERY_TIME; -- PROCEDURE -- To log a record of a delivery -- PROCEDURE LOG_HISTORY(source_obj_type_in IN NUMBER, source_obj_guid_in IN RAW, delivered_in IN VARCHAR2, message_in IN VARCHAR2, tz_region_in IN VARCHAR2 DEFAULT NULL) IS t DATE; tz_region VARCHAR2(64); guid RAW(16) := source_obj_guid_in; source_type NUMBER := source_obj_type_in; BEGIN IF tz_region_in IS NULL THEN SELECT timezone_region INTO tz_region FROM MGMT_TARGETS tgt, MGMT_VIOLATIONS sev WHERE sev.violation_guid = source_obj_guid_in AND tgt.target_guid = sev.target_guid; t := mgmt_global.sysdate_tzrgn(tz_region); ELSE t := mgmt_global.sysdate_tzrgn(tz_region_in); END IF; IF source_obj_type_in IN (METRIC_CA_STATE_CHANGE, POLICY_CA_STATE_CHANGE) THEN source_type := METRIC_SEVERITY; BEGIN SELECT violation_guid INTO guid FROM MGMT_JOB_STATE_CHANGES WHERE state_change_guid = source_obj_guid_in; EXCEPTION WHEN NO_DATA_FOUND THEN guid := source_obj_guid_in; END; END IF; INSERT INTO MGMT_NOTIFICATION_LOG (source_obj_type, source_obj_guid, timestamp, delivered, message) VALUES (source_type, guid, t, delivered_in, message_in); EXCEPTION WHEN OTHERS THEN INSERT INTO MGMT_NOTIFICATION_LOG (source_obj_type, source_obj_guid, timestamp, delivered, message) VALUES (source_type, source_obj_guid_in, SYSDATE, delivered_in, message_in); END LOG_HISTORY; -- -- PURPOSE -- -- To remove deleted targets from notification rules -- -- PARAMETERS -- -- TARGET_NAME_IN - the target name of the deleted target -- TARGET_TYPE_IN - the type of the deleted target -- PROCEDURE HANDLE_TARGET_DELETED(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS tguid RAW(16); BEGIN BEGIN SELECT target_guid INTO tguid FROM MGMT_TARGETS WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; DELETE FROM MGMT_NOTIFY_RULE_CONFIGS WHERE (target_name = target_name_in AND target_type = target_type_in) OR target_guid = tguid; DELETE FROM MGMT_NOTIFY_JOB_RULE_CONFIGS WHERE (target_name = target_name_in AND target_type = target_type_in) OR target_guid = tguid; END HANDLE_TARGET_DELETED; -- PURPOSE -- -- To cleanup when a key value has been deleted -- -- PARAMETERS -- -- P_TARGET_GUID - the target GUID -- P_METRIC_GUID - the metric GUID -- P_KEY_VALUE - the key value -- PROCEDURE HANDLE_METRIC_KEYVAL_DELETION( p_target_guid mgmt_targets.target_guid%TYPE, p_metric_guid mgmt_metrics.metric_guid%TYPE, p_key_value mgmt_metrics_raw.key_value%TYPE) IS l_target_name VARCHAR2(256); l_target_type VARCHAR2(64); l_met_col VARCHAR2(64); BEGIN --trace('called with: ' || p_target_guid || '/' || p_metric_guid || '/' || --p_key_value); SELECT target_name, target_type INTO l_target_name, l_target_type FROM MGMT_TARGETS WHERE target_guid = p_target_guid; -- Using like here to match names in case the Console goes to supporting -- multiple target types in rules. It already supports multiple targets -- of the same type DELETE FROM MGMT_NOTIFY_RULE_CONFIGS WHERE l_target_type LIKE target_type AND l_target_name LIKE target_name AND key_value = p_key_value; COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END HANDLE_METRIC_KEYVAL_DELETION; -- PURPOSE -- -- To queue a severity into MGMT_NOTIFY_INPUT_Q -- -- PARAMETERS -- -- p_msg - violation GUID or state change guid + guid type -- p_guid_type - JOB_STATE_CHANGE or SEVERITY PROCEDURE QUEUE_NOTIF_INPUT(p_msg_in RAW, p_guid_type NUMBER) IS enq_options dbms_aq.enqueue_options_t; qMsg_properties dbms_aq.message_properties_t; qMsg_handle RAW(16); BEGIN IF(length(p_msg_in) != 32 OR p_guid_type NOT IN (GUID_TYPE_SEVERITY, GUID_TYPE_JOB_STATE_CHANGE)) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Invalid severity / job_state_change GUID or invalid guid type : ' || p_msg_in || ' : ' || p_guid_type, 'queue_notif_input'); END IF; END IF; -- Add the message to the queue DBMS_AQ.ENQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME, enqueue_options => enq_options, message_properties => qMsg_properties, payload => utl_raw.cast_to_raw(p_msg_in || '|' || p_guid_type), msgid => qMsg_handle); EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'Error while enquing the severity : ' || p_msg_in || SQLERRM, 'ORA', p_msg_in); END QUEUE_NOTIF_INPUT; -- PURPOSE -- -- To de-queue a severity from MGMT_NOTIFY_INPUT_Q -- -- PARAMETERS -- -- p_notif_src_guid - SEVERITY OR JOB_STATE_CHANGE_GUID -- p_guid_type - JOB_STATE_CHANGE or SEVERITY -- p_rule_guid - RULE GUID in case of repeat signal, null otherwise -- p_repeat_count - Repeat count in case of repeat notification, null otherwise PROCEDURE DEQUEUE_NOTIF_INPUT(p_notif_src_guid OUT RAW, p_guid_type OUT NUMBER, p_rule_guid OUT RAW, p_repeat_count OUT NUMBER) IS dq_options dbms_aq.dequeue_options_t; qMsg_properties dbms_aq.message_properties_t; msg RAW(500); msg_content VARCHAR2(500); qmsg_id_out RAW(32); BEGIN dq_options.navigation := DBMS_AQ.FIRST_MESSAGE; dq_options.wait := DBMS_AQ.NO_WAIT; -- Dequeue a notification DBMS_AQ.DEQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME, dequeue_options => dq_options, message_properties => qMsg_properties, payload => msg, msgid => qmsg_id_out); msg_content := utl_raw.cast_to_varchar2(msg); -- check for lenght -- change the code to extract the data p_notif_src_guid := hextoraw(substr(msg_content,1,32)); p_guid_type := substr(msg_content,34,1); IF(length (msg_content) > 34) THEN p_rule_guid := hextoraw(substr(msg_content,36,32)); p_repeat_count := to_number(substr(msg_content,69)); ELSE p_rule_guid := NULL; p_repeat_count := NULL; END IF; END DEQUEUE_NOTIF_INPUT; -- PURPOSE -- -- To update and en-queue RCA completed severity in MGMT_NOTIFY_INPUT_Q -- -- PARAMETERS -- -- violation_guid_in - violation guid PROCEDURE UPDATE_AND_ENQUEUE_RCA_SEV(violation_guid_in IN RAW, notif_status_in IN NUMBER) IS BEGIN UPDATE mgmt_violations SET load_timestamp = SYSDATE, notification_status = notif_status_in WHERE violation_guid = violation_guid_in; QUEUE_NOTIF_INPUT(violation_guid_in, EMD_NOTIFICATION.GUID_TYPE_SEVERITY); END UPDATE_AND_ENQUEUE_RCA_SEV; -- PURPOSE -- -- To retrieve global repeat settings -- -- PARAMETERS -- p_enabled - 1 if enabled, 0 is disabled -- p_frequency - frequency in minutes, -- p_count -- maximum repeat count -- PROCEDURE GET_GLOBAL_REPEAT_SETTINGS( p_enabled OUT NUMBER, p_frequency OUT NUMBER, p_count OUT NUMBER) IS BEGIN p_enabled := GET_CONFIG_VALUE(REPEAT_ENABLED_PARAM, REPEAT_ENABLED_DEFAULT); p_frequency := GET_CONFIG_VALUE(REPEAT_FREQUENCY_PARAM, REPEAT_FREQUENCY_DEFAULT); p_count := GET_CONFIG_VALUE(REPEAT_COUNT_PARAM, REPEAT_COUNT_DEFAULT); END; -- PURPOSE -- -- To set global repeat settings -- -- PARAMETERS -- p_enabled - 1 if enabled, 0 is disabled -- p_frequency - frequency in minutes, -- p_count -- maximum repeat count -- PROCEDURE SET_GLOBAL_REPEAT_SETTINGS( p_enabled IN NUMBER DEFAULT REPEAT_ENABLED_DEFAULT, p_frequency IN NUMBER DEFAULT REPEAT_FREQUENCY_DEFAULT, p_count IN NUMBER DEFAULT REPEAT_COUNT_DEFAULT) IS l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER; BEGIN -- Ensure that the caller is a super-user. IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = 0 THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only superusers can set global repeat notification settings'); END IF; SET_CONFIG_VALUE(REPEAT_ENABLED_PARAM, p_enabled); SET_CONFIG_VALUE(REPEAT_FREQUENCY_PARAM, p_frequency); SET_CONFIG_VALUE(REPEAT_COUNT_PARAM, p_count); END; -- PURPOSE -- -- To queue a repeat signal into MGMT_NOTIFY_INPUT_Q -- -- PARAMETERS -- -- p_msg - violation GUID or state change guid + guid type -- p_rule_guid - GUID of the rule for which repeat is enabled -- p_repeat_count - Number of times the repeat is sent for this severity -- p_delay - duration in minutes after which repeat notification should be send PROCEDURE QUEUE_REPEAT_NOTIF_INPUT(p_msg_in RAW, p_rule_guid RAW, p_repeat_count NUMBER, p_delay NUMBER) IS enq_options dbms_aq.enqueue_options_t; qMsg_properties dbms_aq.message_properties_t; qMsg_handle RAW(16); l_payload VARCHAR2(256); BEGIN qMsg_properties.delay := p_delay * 60; IF(length(p_msg_in) != 32) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('Invalid severity GUID : ' || p_msg_in , 'queue_notif_input'); END IF; END IF; l_payload := p_msg_in || '|' || GUID_TYPE_REP_SEVERITY || '|' || p_rule_guid || '|' || p_repeat_count; -- Add the message to the queue DBMS_AQ.ENQUEUE(queue_name => G_NOTIFY_INPUT_Q_NAME, enqueue_options => enq_options, message_properties => qMsg_properties, payload => utl_raw.cast_to_raw(l_payload), msgid => qMsg_handle); EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(EMD_NOTIFICATION.CHECK_MODULE_NAME, MGMT_GLOBAL.UNEXPECTED_ERR, MGMT_GLOBAL.UNEXPECTED_ERR_M || 'Error while enquing the severity : ' || p_msg_in || SQLERRM, 'ORA', p_msg_in); END QUEUE_REPEAT_NOTIF_INPUT; -- -- PROCEDURE: ACKNOWLEDGE_ALERT -- -- PURPOSE -- To acknowledge a particular alert record. -- -- PARAMETERS -- p_violation_guid - GUID of the violation record that needs to be acknowledged. -- p_acknowledged_by - User name who aknowledged this alert. -- p_annotation_type - Annotation type for severity acknowledged annotation -- p_message - Annotation message PROCEDURE ACKNOWLEDGE_ALERT(p_violation_guid IN RAW, p_acknowledged_by IN VARCHAR2, p_annotation_type IN VARCHAR2, p_message IN VARCHAR2) IS l_target_guid RAW(16); l_tz_region VARCHAR2(64); BEGIN IF( p_violation_guid IS NULL OR p_acknowledged_by IS NULL OR p_annotation_type IS NULL OR p_message IS NULL ) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'All the parameters are mandatory'); END IF; UPDATE mgmt_violations SET acknowledged = 1, acknowledged_by = p_acknowledged_by WHERE violation_guid = p_violation_guid RETURNING target_guid INTO l_target_guid; IF( l_target_guid IS NULL) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Violation record does not exist'); END IF; SELECT TIMEZONE_REGION INTO l_tz_region FROM mgmt_targets WHERE target_guid = l_target_guid; INSERT INTO MGMT_ANNOTATION (source_obj_type, source_obj_guid, timestamp, annotation_type, user_name, message) VALUES (MGMT_GLOBAL.G_ANNOTATION_SOURCE_SEVERITY, p_violation_guid, mgmt_global.sysdate_tzrgn(l_tz_region), p_annotation_type, p_acknowledged_by, p_message); END ACKNOWLEDGE_ALERT; BEGIN -- Get the repository owner and set up the queue name to avoid string -- concatentation G_REPOSITORY_OWNER := MGMT_USER.GET_REPOSITORY_OWNER; G_QUEUE_NAME := G_REPOSITORY_OWNER || '.MGMT_NOTIFY_Q'; G_NOTIFY_INPUT_Q_NAME := G_REPOSITORY_OWNER || '.MGMT_NOTIFY_INPUT_Q'; G_DEV_PING_STATE_TIMEOUT := GET_CONFIG_VALUE(NOTIFY_DEV_PING_STATE_TIMEOUT, DEF_DEV_PING_STATE_TIMEOUT); END EMD_NOTIFICATION; / show errors