Report on OEM Alert metrics

My director asked for metrics on alerts that our team gets from OEMGC. Oracle definitely has an interesting, if not obfuscated, schema design in the Enterprise Manager repository. I was able to find enough information to gather a fair bit of useful information and what I needed for the request. The report can show details down to the actual target object and message as well as rolling up to just alerts by criticality and/or object (or more if you want).

I found Karl’s site here a useful start:

http://karlarao.wordpress.com/2011/12/06/mining-emgc-notification-alerts/

Another reference I found, but not sure if I actually got use out of was

MOS: EMDIAG REPVFY Kit for Grid Control 10g and 11g and DB Control 10g and 11g – Download, Install/De-Install and Upgrade (Doc ID 421499.1).

These are the the final queries I settled on. They do not use data from all of the tables, but I left some things as as reference to data that could be useful later or to you.

SELECT to_char(s.load_timestamp,'yyyy/mm/dd hh24:mi:ss') ts, 
 decode(v.violation_level,18,'Insecure/Invalid state',20,'Warning',25,'Critical',125,'Agent Unreachable',325,'Metric Error','Unknown') alert_level, 
 t.target_type, t.target_name, v.message
FROM sysman.mgmt_severity s, sysman.mgmt_targets t, sysman.mgmt_annotation a, sysman.mgmt_notification_log l, sysman.mgmt_violations v
WHERE s.target_guid = t.target_guid
and s.target_guid = v.target_guid and s.load_timestamp = v.load_timestamp
AND s.severity_guid = a.source_obj_guid (+)
AND s.severity_guid = l.source_obj_guid (+)
AND substr(l.message,1,11) = 'E-mail sent'
and v.violation_level in (18,20,25,125,325)
and s.load_timestamp > sysdate-4
ORDER BY 1
/


SELECT 'OEM' source, to_char(s.load_timestamp,'yyyy/mm/dd') alert_date, t.target_type, t.target_name, 
 decode(v.violation_level,18,'Insecure/Invalid state',20,'Warning',25,'Critical',125,'Agent Unreachable',325,'Metric Error','Unknown') alert_level, 
 count(1) total
FROM sysman.mgmt_severity s, sysman.mgmt_targets t, sysman.mgmt_annotation a, sysman.mgmt_notification_log l, sysman.mgmt_violations v
WHERE s.target_guid = t.target_guid
and s.target_guid = v.target_guid and s.load_timestamp = v.load_timestamp
AND s.severity_guid = a.source_obj_guid (+)
AND s.severity_guid = l.source_obj_guid (+)
AND substr(l.message,1,11) = 'E-mail sent'
and s.load_timestamp > sysdate-4
and v.violation_level in (18,20,25,125,325)
group by to_char(s.load_timestamp,'yyyy/mm/dd'), t.target_type, t.target_name, 
 decode(v.violation_level,18,'Insecure/Invalid state',20,'Warning',25,'Critical',125,'Agent Unreachable',325,'Metric Error','Unknown')
ORDER BY 2,3
/

Here are some examples of the output:

TS ALERT_LEVEL TARGET_TYPE TARGET_NAME
------------------- -------------------- ------------------------------ ------------------------------
MESSAGE
----------------------------------------------------------------------------------------------------

2014/10/07 23:41:33 Warning host thisserver01.sys.company.net
Filesystem /db/redo2 has 12.04% available space, fallen below warning (20) or critical (5) threshold.

2014/10/08 19:49:01 Critical oracle_listener LISTENER_thatdb.sys.company.net
The listener is down: TNS-12541: TNS:no listener.

2014/10/08 19:49:05 Critical osm_instance +ASM1_thatdb.sys.company.net
Failed to connect to ASM instance. ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach)

SOU ALERT_DATE TARGET_TYPE TARGET_NAME ALERT_LEVEL TOTAL
--- ---------- ------------------------------ ------------------------------ -------------------- ----------
OEM 2014/10/05 oracle_listener svr1.sys.company.net_LISTENER Critical 1
OEM 2014/10/06 host svr3.sys.company.net Warning 1
OEM 2014/10/06 oracle_database svr4.sys.company.net Metric Error 1

Enjoy.

 

One response to “Report on OEM Alert metrics

  1. Pingback: Report on OEM Alert metrics | Jed's

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s