Target Availability Check Scripts - Explored!
SET ECHO OFF
SET VERIFY OFF
SET LINESIZE 200
SET TRIMSPOOL ON
SET PAGESIZE 100
column target_name format a50
column target_type format a15
column timezone_region format a15
column BROKEN_STR format a20 WRAP
column METRIC_ERROR_MESSAGE format a30 WRAP
column message format a25 WRAP
spool availrpt.log
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
PROMPT #### TARGET INFO ####
select t.target_name, t.target_type, t.target_guid, t.timezone_region, lt.last_load_time, t.load_timestamp,
t.BROKEN_REASON, t.BROKEN_STR, t.LAST_UPDATED_TIME
from mgmt_targets t, mgmt_targets_load_times lt
where t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and t.target_guid = lt.target_guid;
PROMPT #### TARGET AGENT INFO ####
select t.target_name, t.target_type, t.target_guid, t.timezone_region, t.last_load_time, t.load_timestamp,
t.BROKEN_REASON, t.BROKEN_STR, t.LAST_UPDATED_TIME
from mgmt_targets t
where t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT ##### Target and Metric Cat prop ######
select 'TARGET - '||target_guid||'-'||target_type||' - '||type_meta_ver||' - '||category_prop_1||' - '||category_prop_2||' - '||
category_prop_3||' - '||category_prop_4||' - '||category_prop_5||' - '||load_timestamp cat_props
from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
UNION
select 'METRIC - '||m.metric_guid||'-'||m.target_type||' - '||m.type_meta_ver||' - '||m.category_prop_1||' - '||m.category_prop_2||' - '||
m.category_prop_3||' - '||m.category_prop_4||' - '||m.category_prop_5||' - '||MGMT_GLOBAL.FROM_UTC(m.load_timestamp, EM_TARGET.get_repository_timezone) cat_props
from mgmt_targets t, mgmt_metrics m
where t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and m.metric_name = 'Response'
and m.metric_column = 'Status'
and target_name like ('&&em_target_name')
and t. target_type like ('&&em_target_type')
/
PROMPT #### TARGET AVAILABILITY HISTORY ####
select t.target_name, t.target_type,
decode(a.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
a.start_collection_timestamp, a.end_collection_timestamp
from mgmt_targets t, mgmt_availability a
where t.target_guid = a.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
-- and a.start_collection_timestamp > (SYSDATE - 2)
order by a.start_collection_timestamp;
PROMPT #### AGENT AVAILABILITY HISTORY ####
select t.target_name, t.target_type,
decode(a.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
a.start_collection_timestamp, a.end_collection_timestamp
from mgmt_targets t, mgmt_availability a
where t.target_guid = a.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
--and a.start_collection_timestamp > (SYSDATE - 2)
order by a.start_collection_timestamp;
PROMPT #### TARGET CURRENT AVAILABILITY ####
select t.target_name, t.target_type,
decode(ca.current_status,0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
ca.start_collection_timestamp
from mgmt_targets t, mgmt_current_availability ca
where t.target_guid = ca.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
--and ca.start_collection_timestamp > (SYSDATE - 2)
order by ca.start_collection_timestamp;
PROMPT #### AGENT CURRENT AVAILABILITY ####
select t.target_name, t.target_type,
decode(ca.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
ca.start_collection_timestamp
from mgmt_targets t, mgmt_current_availability ca
where t.target_guid = ca.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
--and ca.start_collection_timestamp > (SYSDATE - 2)
;
PROMPT #### TARGET AVAILABILITY MARKER ####
select t.target_name, t.target_type,
decode (am.marker_avail_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
am.marker_timestamp
from mgmt_targets t, mgmt_availability_marker am
where t.target_guid = am.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
order by am.marker_timestamp;
PROMPT #### AGENT AVAILABILITY MARKER ####
select t.target_name, t.target_type,
decode(am.marker_avail_status,0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
am.marker_timestamp
from mgmt_targets t, mgmt_availability_marker am
where t.target_guid = am.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### TARGET RESPONSE/STATUS SEVERITY HISTORY ####
select t.target_name, t.target_type, s.collection_timestamp,
decode(s.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
615, 'hostunreachclear',
625, 'hostunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown', s.severity_code) "Severity",
s.load_timestamp,
s.notification_status notif,
s.MESSAGE_NLSID||' - '||s.message, s.change_seq
from mgmt_severity s, mgmt_targets t, mgmt_metrics m
where s.target_guid = t.target_guid
and s.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and s.collection_timestamp > (SYSDATE - 2)
order by s.change_seq, s.collection_timestamp;
PROMPT #### AGENT RESPONSE/STATUS SEVERITY HISTORY ####
select t.target_name, t.target_type, s.collection_timestamp,
decode(s.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown', s.severity_code) "Severity",
s.load_timestamp,
s.notification_status notif,
s.MESSAGE_NLSID||' - '||s.message, s.change_seq
from mgmt_severity s, mgmt_targets t, mgmt_metrics m
where s.target_guid = t.target_guid
and s.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and s.collection_timestamp > (SYSDATE - 2)
order by s.change_seq, s.collection_timestamp;
PROMPT #### TARGET RESPONSE/STATUS CURRENT SEVERITY ####
select t.target_name, t.target_type, cs.collection_timestamp,
decode(cs.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown') "Severity",
cs.load_timestamp,
cs.message
from mgmt_current_severity cs, mgmt_targets t, mgmt_metrics m
where cs.target_guid = t.target_guid
and cs.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and cs.collection_timestamp > (SYSDATE - 2)
order by cs.collection_timestamp;
PROMPT #### AGENT RESPONSE/STATUS CURRENT SEVERITY ####
select t.target_name, t.target_type, cs.collection_timestamp,
decode(cs.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown') "Severity",
cs.load_timestamp,
cs.message
from mgmt_current_severity cs, mgmt_targets t, mgmt_metrics m
where cs.target_guid = t.target_guid
and cs.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
and m.metric_name = 'Response'
and m.metric_column = 'Status'
-- and cs.collection_timestamp > (SYSDATE - 2)
order by cs.collection_timestamp;
PROMPT #### TARGET LAST VIOLATION ####
select t.target_name, t.target_type, lv.collection_timestamp
from mgmt_last_violation lv, mgmt_targets t, mgmt_metrics m
where lv.target_guid = t.target_guid
and lv.policy_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status';
PROMPT #### AGENT LAST VIOLATION ####
select t.target_name, t.target_type, lv.collection_timestamp
from mgmt_last_violation lv, mgmt_targets t, mgmt_metrics m
where lv.target_guid = t.target_guid
and lv.policy_guid = m.metric_guid
and t.type_meta_ver = m.type_meta_ver
and m.metric_name = 'Response'
and m.metric_column = 'Status'
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### AGENT PING STATUS ####
select t.target_name, t.target_type,
decode(p.status,
1, 'UP',
2, 'DOWN',
3, 'POTENTIALLY DOWN',
'UKNOWN VALUE') "Agent Status",
p.last_heartbeat_ts,
P.unrch_start_ts,
p.clean_heartbeat_utc,
p.hb_received_utc
from mgmt_targets t, mgmt_emd_ping p
where t.target_guid = p.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### Metric coll error for Response ####
select * from
mgmt_metric_errors
where upper(coll_name) like '%RESPONSE%' and target_guid in (select target_guid from mgmt_targets where target_name like ('&&em_target_name') and target_type like ('&&em_target_type'))
order by collection_timestamp
/
undefine em_target_name
undefine em_target_type
spool off
SET VERIFY OFF
SET LINESIZE 200
SET TRIMSPOOL ON
SET PAGESIZE 100
column target_name format a50
column target_type format a15
column timezone_region format a15
column BROKEN_STR format a20 WRAP
column METRIC_ERROR_MESSAGE format a30 WRAP
column message format a25 WRAP
spool availrpt.log
alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
PROMPT #### TARGET INFO ####
select t.target_name, t.target_type, t.target_guid, t.timezone_region, lt.last_load_time, t.load_timestamp,
t.BROKEN_REASON, t.BROKEN_STR, t.LAST_UPDATED_TIME
from mgmt_targets t, mgmt_targets_load_times lt
where t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and t.target_guid = lt.target_guid;
PROMPT #### TARGET AGENT INFO ####
select t.target_name, t.target_type, t.target_guid, t.timezone_region, t.last_load_time, t.load_timestamp,
t.BROKEN_REASON, t.BROKEN_STR, t.LAST_UPDATED_TIME
from mgmt_targets t
where t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT ##### Target and Metric Cat prop ######
select 'TARGET - '||target_guid||'-'||target_type||' - '||type_meta_ver||' - '||category_prop_1||' - '||category_prop_2||' - '||
category_prop_3||' - '||category_prop_4||' - '||category_prop_5||' - '||load_timestamp cat_props
from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
UNION
select 'METRIC - '||m.metric_guid||'-'||m.target_type||' - '||m.type_meta_ver||' - '||m.category_prop_1||' - '||m.category_prop_2||' - '||
m.category_prop_3||' - '||m.category_prop_4||' - '||m.category_prop_5||' - '||MGMT_GLOBAL.FROM_UTC(m.load_timestamp, EM_TARGET.get_repository_timezone) cat_props
from mgmt_targets t, mgmt_metrics m
where t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and m.metric_name = 'Response'
and m.metric_column = 'Status'
and target_name like ('&&em_target_name')
and t. target_type like ('&&em_target_type')
/
PROMPT #### TARGET AVAILABILITY HISTORY ####
select t.target_name, t.target_type,
decode(a.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
a.start_collection_timestamp, a.end_collection_timestamp
from mgmt_targets t, mgmt_availability a
where t.target_guid = a.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
-- and a.start_collection_timestamp > (SYSDATE - 2)
order by a.start_collection_timestamp;
PROMPT #### AGENT AVAILABILITY HISTORY ####
select t.target_name, t.target_type,
decode(a.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
a.start_collection_timestamp, a.end_collection_timestamp
from mgmt_targets t, mgmt_availability a
where t.target_guid = a.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
--and a.start_collection_timestamp > (SYSDATE - 2)
order by a.start_collection_timestamp;
PROMPT #### TARGET CURRENT AVAILABILITY ####
select t.target_name, t.target_type,
decode(ca.current_status,0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
ca.start_collection_timestamp
from mgmt_targets t, mgmt_current_availability ca
where t.target_guid = ca.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
--and ca.start_collection_timestamp > (SYSDATE - 2)
order by ca.start_collection_timestamp;
PROMPT #### AGENT CURRENT AVAILABILITY ####
select t.target_name, t.target_type,
decode(ca.current_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
ca.start_collection_timestamp
from mgmt_targets t, mgmt_current_availability ca
where t.target_guid = ca.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
--and ca.start_collection_timestamp > (SYSDATE - 2)
;
PROMPT #### TARGET AVAILABILITY MARKER ####
select t.target_name, t.target_type,
decode (am.marker_avail_status, 0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
am.marker_timestamp
from mgmt_targets t, mgmt_availability_marker am
where t.target_guid = am.target_guid
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
order by am.marker_timestamp;
PROMPT #### AGENT AVAILABILITY MARKER ####
select t.target_name, t.target_type,
decode(am.marker_avail_status,0, 'down',
1, 'up',
2, 'metricerror',
3, 'agentdown',
4, 'unreachable',
5, 'blackout',
6, 'unknown') "CurrentStatus",
am.marker_timestamp
from mgmt_targets t, mgmt_availability_marker am
where t.target_guid = am.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### TARGET RESPONSE/STATUS SEVERITY HISTORY ####
select t.target_name, t.target_type, s.collection_timestamp,
decode(s.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
615, 'hostunreachclear',
625, 'hostunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown', s.severity_code) "Severity",
s.load_timestamp,
s.notification_status notif,
s.MESSAGE_NLSID||' - '||s.message, s.change_seq
from mgmt_severity s, mgmt_targets t, mgmt_metrics m
where s.target_guid = t.target_guid
and s.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and s.collection_timestamp > (SYSDATE - 2)
order by s.change_seq, s.collection_timestamp;
PROMPT #### AGENT RESPONSE/STATUS SEVERITY HISTORY ####
select t.target_name, t.target_type, s.collection_timestamp,
decode(s.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown', s.severity_code) "Severity",
s.load_timestamp,
s.notification_status notif,
s.MESSAGE_NLSID||' - '||s.message, s.change_seq
from mgmt_severity s, mgmt_targets t, mgmt_metrics m
where s.target_guid = t.target_guid
and s.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and s.collection_timestamp > (SYSDATE - 2)
order by s.change_seq, s.collection_timestamp;
PROMPT #### TARGET RESPONSE/STATUS CURRENT SEVERITY ####
select t.target_name, t.target_type, cs.collection_timestamp,
decode(cs.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown') "Severity",
cs.load_timestamp,
cs.message
from mgmt_current_severity cs, mgmt_targets t, mgmt_metrics m
where cs.target_guid = t.target_guid
and cs.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status'
--and cs.collection_timestamp > (SYSDATE - 2)
order by cs.collection_timestamp;
PROMPT #### AGENT RESPONSE/STATUS CURRENT SEVERITY ####
select t.target_name, t.target_type, cs.collection_timestamp,
decode(cs.severity_code, 0, 'nonup_down',
1, 'up_down',
3, 'register_deregister',
10, 'annotationrecord',
15, 'clear',
20, 'warning',
25, 'critical',
115, 'agentunreachclear',
125, 'agentunreach',
215, 'endblackout',
225, 'startblackout',
315, 'endmetricerror',
325, 'startmetricerror',
425, 'websitedown') "Severity",
cs.load_timestamp,
cs.message
from mgmt_current_severity cs, mgmt_targets t, mgmt_metrics m
where cs.target_guid = t.target_guid
and cs.metric_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and ( (t.category_prop_1 = m.category_prop_1) or (m.category_prop_1 = ' ') )
and ( (t.category_prop_2 = m.category_prop_2) or (m.category_prop_2 = ' ') )
and ( (t.category_prop_3 = m.category_prop_3) or (m.category_prop_3 = ' ') )
and ( (t.category_prop_4 = m.category_prop_4) or (m.category_prop_4 = ' ') )
and ( (t.category_prop_5 = m.category_prop_5) or (m.category_prop_5 = ' ') )
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
)
and m.metric_name = 'Response'
and m.metric_column = 'Status'
-- and cs.collection_timestamp > (SYSDATE - 2)
order by cs.collection_timestamp;
PROMPT #### TARGET LAST VIOLATION ####
select t.target_name, t.target_type, lv.collection_timestamp
from mgmt_last_violation lv, mgmt_targets t, mgmt_metrics m
where lv.target_guid = t.target_guid
and lv.policy_guid = m.metric_guid
and t.target_type = m.target_type
and t.type_meta_ver = m.type_meta_ver
and t.target_name like ('&&em_target_name')
and t.target_type like ('&&em_target_type')
and m.metric_name = 'Response'
and m.metric_column = 'Status';
PROMPT #### AGENT LAST VIOLATION ####
select t.target_name, t.target_type, lv.collection_timestamp
from mgmt_last_violation lv, mgmt_targets t, mgmt_metrics m
where lv.target_guid = t.target_guid
and lv.policy_guid = m.metric_guid
and t.type_meta_ver = m.type_meta_ver
and m.metric_name = 'Response'
and m.metric_column = 'Status'
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### AGENT PING STATUS ####
select t.target_name, t.target_type,
decode(p.status,
1, 'UP',
2, 'DOWN',
3, 'POTENTIALLY DOWN',
'UKNOWN VALUE') "Agent Status",
p.last_heartbeat_ts,
P.unrch_start_ts,
p.clean_heartbeat_utc,
p.hb_received_utc
from mgmt_targets t, mgmt_emd_ping p
where t.target_guid = p.target_guid
and t.target_guid in (select target_guid from mgmt_targets
where target_type = 'oracle_emd'
and emd_url in (select emd_url from mgmt_targets
where target_name like ('&&em_target_name')
and target_type like ('&&em_target_type')
)
);
PROMPT #### Metric coll error for Response ####
select * from
mgmt_metric_errors
where upper(coll_name) like '%RESPONSE%' and target_guid in (select target_guid from mgmt_targets where target_name like ('&&em_target_name') and target_type like ('&&em_target_type'))
order by collection_timestamp
/
undefine em_target_name
undefine em_target_type
spool off
Post a Comment: