This discussion is archived
3 Replies Latest reply: Jul 11, 2013 5:21 AM by Andrey Goryunov RSS

How to setup escalation notification for OEM Grid alerts?

Sivaprasad S Newbie
Currently Being Moderated

Version: OEM Grid 11g

 

Please let me know the pointers, details of how to set up escalation notification if OEM Alert notification are not acknowledged in a day.

  • 1. Re: How to setup escalation notification for OEM Grid alerts?
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    You can check this Setting Up Repeat Notifications RefDoc: Notifications

     

    HTH

  • 2. Re: How to setup escalation notification for OEM Grid alerts?
    Sivaprasad S Newbie
    Currently Being Moderated

    DK2010,

     

    Thanks for the above link, however I look for escalation notification not repeat notification.

  • 3. Re: How to setup escalation notification for OEM Grid alerts?
    Andrey Goryunov Explorer
    Currently Being Moderated

    Hi,

     

    possibly one of approaches would be to create user-defined metric that checks alerts and their acknowledgement

    periodically (let's say every 2,4 hours). You might use the following SQL:

     

    with sql_targets as (

      select * from (

        select target_type, target_name, metric_name, column_label, key_value, current_value, collection_timestamp,

        row_number() over(partition by target_name, metric_name, column_label order by collection_timestamp) rn,

        count(1) over(partition by target_name, metric_name, column_label) cnt,

        round((sysdate - collection_timestamp), 0) days

        from mgmt$alert_current c

        where 1=1

        and alert_state in('Critical')

      ) where rn = cnt

    ),

    sql_notes as (

      select n.target_type, n.target_name, n.metric_name, n.column_label, n.key_value, annotation_message msg, annotation_type typ,

      decode(annotation_type, 'ACKNOWLEDGED', 1, 0) acked,

      row_number() over(partition by n.target_type, n.target_name, n.metric_name, n.column_label order by annotation_timestamp) rn,

      count(1) over(partition by n.target_type, n.target_name, n.metric_name, n.column_label) cnt, t.collection_timestamp, annotation_timestamp

      from mgmt$alert_annotations n, sql_targets t

      where 1=1

      and t.target_type = n.target_type

      and t.target_name = n.target_name

      and t.metric_name = n.metric_name

      and t.column_label = n.column_label

      and t.key_value = n.key_value

      and n.annotation_timestamp >= t.collection_timestamp

    )

    select target_type, target_name, metric_name, column_label, key_value, count(*), max(acked), min(acked), min(annotation_timestamp),

    max(annotation_timestamp) from sql_notes

    group by target_type, target_name, metric_name, column_label, key_value

    having max(acked) = 0;

     

    HTH,

    Andrey

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points