Rem Rem $Header: monitoring_outofbox_reports.sql 03-aug-2006.02:50:42 denath Exp $ Rem Rem monitoring_outofbox_reports.sql Rem Rem Copyright (c) 2005, 2006, Oracle. All rights reserved. Rem Rem NAME Rem monitoring_outofbox_reports.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem denath 08/03/06 - Fix Bug 5358834.Changed return type to date for Rem Alert / Violation History charts. Rem denath 07/29/06 - Fix Bug 5358834.Added 2 more parameters to alerts Rem and violations history chart. Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 DECLARE l_target_types MGMT_IP_TARGET_TYPES; l_param_classes MGMT_IP_PARAM_CLASSES; l_param_values MGMT_IP_PARAM_VALUE_LIST; l_targets MGMT_IP_TARGET_LIST; l_report_guid RAW(16); l_element_guid RAW(16); l_order NUMBER; l_elem_order NUMBER; l_curr_row NUMBER; BEGIN ------------------------------------- alert_vio_history.sql --------------------------------------------- BEGIN MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_History_All_Targets', 0); EXCEPTION WHEN OTHERS THEN NULL; END; -- this report has a time period and the user can customize it l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:0'); -------------------- AVAILABILITY TEST REPORT BEGINS -------------------------------------------------- l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'EMR_Alert_Violation_History_All_Targets', p_description_nlsid => 'EMR_Alert_Violation_History_All_Targets_Desc', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_MONITORING_CATEGORY', p_sub_category_nlsid => 'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_CATEGORY', p_late_binding_target_types => null, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_av_hist_report' ); l_elem_order := 1; l_curr_row := 1; -------------------- REPORT DESCRIPTION -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALRT_VIO_HIST_DESC'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => null, p_element_order => l_elem_order, p_element_row => l_curr_row, p_parameters => l_param_values, p_targets => null ); ------------------ ADDING CHART FROM SQL Alert History CHART-------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT to_date(to_char(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,sum(CRITICAL) CRITICAL_ID, sum(WARNING) WARNING_ID FROM ( select MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION ,decode(A.ALERT_STATE,''Critical'',1,0) CRITICAL ,decode(A.ALERT_STATE,''Warning'',1,0) WARNING from MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in (''Critical'',''Warning'') AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) GROUP BY to_char(DURATION,''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alert_History', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING CHART FROM SQL VIOLATION HISTORY CHART-------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT TO_DATE(TO_CHAR(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,SUM(CRITICAL) CRITICAL_ID ,SUM(WARNING) WARNING_ID,SUM(INFORMATIONAL) INFORMATIONAL_ID FROM ( select MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION ,decode(A.violation_level,25,1,0) CRITICAL,decode(A.violation_level,20,1,0) WARNING ,decode(A.violation_level,18,1,0) INFORMATIONAL FROM mgmt$policy_violation_history A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in (20,25,18) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) GROUP BY TO_CHAR(DURATION, ''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Violation_History', l_elem_order, l_curr_row, l_param_values, null); ------------------ ADDING TABLE FROM SQL Alerts -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'select A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID, A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID, ( ltrim(to_char(trunc( NVL(((A.collection_timestamp + (A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp ),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24 ),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24*60 ),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))- A.collection_timestamp)*24*60*60 ),60),''09''))||''s'' ) DURATION_ID,message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B where A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in(''Critical'',''Warning'') AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) order by A.collection_timestamp'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '4'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '3'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alerts', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING TABLE FROM SQL POLICY VIOLATIONS -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'WITH tab1 AS( SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID, DECODE(ltrim(A.KEY_VALUE),NULL||'''',NULL,A.KEY_VALUE) || DECODE(ltrim(A.KEY_VALUE2),NULL||'''',NULL,'' , ''||A.KEY_VALUE2) || DECODE(ltrim(A.KEY_VALUE3),NULL||'''',NULL,'' , ''||A.KEY_VALUE3) || DECODE(ltrim(A.KEY_VALUE4),NULL||'''',NULL,'' , ''||A.KEY_VALUE4) OBJECTS_ID, A.violation_level SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.violation_duration/24) END_TIME_ID, ( ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60),60),''09''))||''s'' ) DURATION_ID, A.POLICY_GUID POLICY_GUID, A.MESSAGE, A.MESSAGE_NLSID, A.collection_timestamp FROM mgmt$policy_violation_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) select TARGET_NAME_ID, TARGET_TYPE_ID, NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID, NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID, OBJECTS_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID, DURATION_ID, NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID from tab1 T1, mgmt$policies R, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM , (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) DM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') DEM where T1.POLICY_GUID=R.POLICY_GUID AND R.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND R.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CEM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+) ORDER BY T1.collection_timestamp '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '6'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '5'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Policy_Violations', l_elem_order, l_curr_row, l_param_values, null); ------------------------------------- alert_vio_history_grp.sql --------------------------------------------- BEGIN MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_History_Group', 0); EXCEPTION WHEN OTHERS THEN NULL; END; l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION ------------------------------------- l_target_types(1) := 'composite'; -- this report has a time period and the user can customize it l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:0'); -------------------- AVAILABILITY TEST REPORT BEGINS -------------------------------------------------- l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid => 'EMR_Alert_Violation_History_Group', p_description_nlsid => 'EMR_Alert_Violation_History_Group_Desc', p_owner => 'SYSMAN', p_category_nlsid => 'IPMSG_MONITORING_CATEGORY', p_sub_category_nlsid => 'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_av_hist_group_report' ); l_elem_order := 1; l_curr_row := 1; -------------------- REPORT DESCRIPTION -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALRT_VIO_HIST_GRP_DESC'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => null, p_element_order => l_elem_order, p_element_row => l_curr_row, p_parameters => l_param_values, p_targets => null ); -------------------- ADDING CHART FROM SQL ALERT HISTORY CHART-------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT to_date(to_char(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,sum(CRITICAL) CRITICAL_ID, sum(WARNING) WARNING_ID FROM(SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION, decode(A.ALERT_STATE,''Critical'',1,0) CRITICAL,decode(A.ALERT_STATE,''Warning'',1,0) WARNING FROM MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in(''Critical'',''Warning'') and A.TARGET_GUID In(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) )GROUP BY to_char(DURATION,''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alert_History', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING CHART FROM SQL VIOLATION HISTORY -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT TO_DATE(TO_CHAR(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,SUM(CRITICAL) CRITICAL_ID, SUM(WARNING) WARNING_ID,SUM(INFORMATIONAL) INFORMATIONAL_ID FROM( SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION, decode(violation_level,25,1,0) CRITICAL,decode(violation_level,20,1,0) WARNING, decode(violation_level,18,1,0) INFORMATIONAL FROM mgmt$policy_violation_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in (20,25,18) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) )GROUP BY TO_CHAR(DURATION,''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Violation_History', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING TABLE FROM SQL ALERTS -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID, A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID, ( ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60 ),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.alert_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60 ),60),''09''))||''s'' ) DURATION_ID,A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.alert_state in (''Critical'',''Warning'') and A.TARGET_GUID In(select MEMBER_TARGET_GUID from MGMT$TARGET_MEMBERS WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) order by A.collection_timestamp'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '4'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '3'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alerts', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING TABLE FROM SQL POLICY VIOLATIONS -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'WITH tab1 AS( SELECT A.target_name TARGET_NAME_ID,A.type_display_name TARGET_TYPE_ID, DECODE(ltrim(A.KEY_VALUE),NULL||'''',NULL,A.KEY_VALUE) ||DECODE(ltrim(A.KEY_VALUE2),NULL||'''',NULL,'' , ''||A.KEY_VALUE2) ||DECODE(ltrim(A.KEY_VALUE3),NULL||'''',NULL,'' , ''||A.KEY_VALUE3) ||DECODE(ltrim(A.KEY_VALUE4),NULL||'''',NULL,'' , ''||A.KEY_VALUE4) OBJECTS_ID, A.violation_level SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.violation_duration/24) END_TIME_ID, (ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60),60),''09''))||''s'' ) DURATION_ID, A.collection_timestamp, A.MESSAGE, A.MESSAGE_NLSID, A.POLICY_GUID FROM mgmt$policy_violation_history A, MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) AND A.target_guid IN(SELECT member_target_guid FROM mgmt$target_members WHERE aggregate_target_guid=??EMIP_BIND_TARGET_GUID??) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) select TARGET_NAME_ID, TARGET_TYPE_ID, NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID, OBJECTS_ID, NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID, DURATION_ID, NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID from tab1 T1,mgmt$policies R, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM , (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) DM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') DEM where T1.POLICY_GUID=R.POLICY_GUID AND R.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND R.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CEM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+) order by T1.collection_timestamp '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '6'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '5'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Policy_Violations', l_elem_order, l_curr_row, l_param_values, null); ------------------------------------- alert_vio_history_tgt.sql --------------------------------------------- BEGIN MGMT_IP.delete_report_by_title('SYSMAN', 'EMR_Alert_Violation_History_Target', 0); EXCEPTION WHEN OTHERS THEN NULL; END; l_target_types := MGMT_IP_TARGET_TYPES(); l_target_types.extend(1); -------- TARGET TYPES TO BE DISPLAYED FOR SELECTION ------------------------------------- l_target_types(1) := 'any_type'; -- this report has a time period and the user can customize it l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodOption', 'true'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodUserCust', 'true'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TimePeriodParam', '0:0'); -------------------- AVAILABILITY TEST REPORT BEGINS -------------------------------------------------- l_report_guid := mgmt_ip.create_report_definition ( p_title_nlsid =>'EMR_Alert_Violation_History_Target', p_description_nlsid =>'EMR_Alert_Violation_History_Target_Desc', p_owner =>'SYSMAN', p_category_nlsid =>'IPMSG_MONITORING_CATEGORY', p_sub_category_nlsid =>'IPMSG_ALERTS_AND_POLICY_VIOLATIONS_CATEGORY', p_late_binding_target_types => l_target_types, p_late_binding_multi_targets => 0, p_show_table_of_contents => 0, p_system_report => 1, p_parameters => l_param_values, p_help_topic_id => 'info_pub_gc_cs_av_hist_target_report' ); l_elem_order := 1; l_curr_row := 1; -------------------- REPORT DESCRIPTION -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(3); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage', 'ALRT_VIO_HIST_TGT_DESC'); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyle', 'OraInstructionText'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_element_guid := mgmt_ip.add_element_to_report_def ( p_report_guid => l_report_guid, p_element_name_nlsid => 'IPMSG_STYLED_TEXT', p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE', p_header_nlsid => null, p_element_order => l_elem_order, p_element_row => l_curr_row, p_parameters => l_param_values, p_targets => null ); -------------------- ADDING CHART FROM SQL ALERT HISTORY CHART-------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT to_date(to_char(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID,sum(CRITICAL) CRITICAL_ID, sum(WARNING) WARNING_ID FROM ( SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION, decode(A.ALERT_STATE,''Critical'',1,0) CRITICAL, decode(A.ALERT_STATE,''Warning'',1,0) WARNING FROM MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid=??EMIP_BIND_TARGET_GUID?? AND A.alert_state in(''Critical'',''Warning'') AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) GROUP BY to_char(DURATION,''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alert_History', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING CHART FROM SQL VIOLATION HISTORY CHART-------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(7); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT TO_DATE(TO_CHAR(DURATION,''Mon-yy''), ''MM-yy'') DURATION_ID, SUM(CRITICAL) CRITICAL_ID, SUM(WARNING) WARNING_ID, SUM(INFORMATIONAL) INFORMATIONAL_ID FROM( SELECT MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) DURATION, decode(A.violation_level,25,1,0) CRITICAL,decode(A.violation_level,20,1,0) WARNING, decode(A.violation_level,18,1,0) INFORMATIONAL FROM mgmt$policy_violation_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) AND A.target_guid=??EMIP_BIND_TARGET_GUID?? AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) )GROUP BY TO_CHAR(DURATION, ''Mon-yy''),to_char(DURATION,''yyyy/MM'') ORDER BY to_char(DURATION,''yyyy/MM'') '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.width', '380'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.height', '200'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.chartType', 'barChart'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.barChartConvertDateStringLocale', 'yyyy-MMM'); l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues', 'true'); l_elem_order := l_elem_order +1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_CHART_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Violation_History', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING TABLE FROM SQL ALERTS -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', 'SELECT A.column_label METRIC_ID,decode(A.alert_state,''Critical'',25,''Warning'',20) SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.alert_duration/24) END_TIME_ID, ( ltrim(to_char(trunc(NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp ),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24 ),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60 ),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc((NVL(((A.collection_timestamp+(A.alert_duration/24))),sysdate)-A.collection_timestamp)*24*60*60 ),60),''09''))||''s'' ) DURATION_ID, A.message MESSAGE_ID FROM MGMT$alert_history A,MGMT$TARGET B WHERE A.TARGET_GUID=B.TARGET_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? AND A.alert_state in (''Critical'',''Warning'') AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.ALERT_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.ALERT_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.ALERT_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ORDER BY A.collection_timestamp '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '2'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '1'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Alerts', l_elem_order, l_curr_row, l_param_values, null); -------------------- ADDING TABLE FROM SQL POLICY VIOLATIONS -------------------------------------------------- l_param_values := MGMT_IP_PARAM_VALUE_LIST(); l_param_values.extend(6); l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.sqlStatement', ' WITH tab1 AS( SELECT DECODE(ltrim(A.KEY_VALUE),NULL||'''',NULL,A.KEY_VALUE) ||DECODE(ltrim(A.KEY_VALUE2),NULL||'''',NULL,'' , ''||A.KEY_VALUE2) ||DECODE(ltrim(A.KEY_VALUE3),NULL||'''',NULL,'' , ''||A.KEY_VALUE3) ||DECODE(ltrim(A.KEY_VALUE4),NULL||'''',NULL,'' , ''||A.KEY_VALUE4) OBJECTS_ID, A.violation_level SEVERITY_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??) START_TIME_ID, MGMT_VIEW_UTIL.ADJUST_TZ(A.collection_timestamp,B.TIMEZONE_REGION,??EMIP_BIND_TIMEZONE_REGION??)+(A.violation_duration/24) END_TIME_ID, ( ltrim(to_char(trunc( NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp ),''009''))||''d'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24 ),24),''09''))||''h'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60 ),60),''09''))||''m'' ||'':''||ltrim(to_char(MOD(trunc( (NVL(((A.collection_timestamp+(A.violation_duration/24))) ,(CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE)))-A.collection_timestamp)*24*60*60 ),60),''09''))||''s'' ) DURATION_ID, A.MESSAGE, A.MESSAGE_NLSID, A.POLICY_GUID, A.collection_timestamp FROM mgmt$policy_violation_history A,mgmt$policies R,MGMT$TARGET B, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM , (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) DM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') DEM WHERE A.POLICY_GUID = R.POLICY_GUID AND A.target_guid = ??EMIP_BIND_TARGET_GUID?? AND A.TARGET_GUID=B.TARGET_GUID AND A.violation_level in(20,25,18) AND R.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND R.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CEM.MESSAGE_ID(+) AND A.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND A.MESSAGE_NLSID=DEM.MESSAGE_ID(+) AND(A.collection_timestamp < MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) and DECODE(A.VIOLATION_DURATION,NULL,CAST(systimestamp at time zone B.TIMEZONE_REGION AS DATE) ,A.collection_timestamp+(A.VIOLATION_DURATION/24))>MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) OR (A.VIOLATION_DURATION=0 and(A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_START_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION) or A.collection_timestamp=MGMT_VIEW_UTIL.ADJUST_TZ(??EMIP_BIND_END_DATE??,??EMIP_BIND_TIMEZONE_REGION??,B.TIMEZONE_REGION)) ) ) ) select NVL(PM.MESSAGE, NVL(PEM.MESSAGE, R.POLICY_NAME)) POLICY_RULE_ID, OBJECTS_ID, NVL(CM.MESSAGE, NVL(CEM.MESSAGE, R.CATEGORY)) CATEGORY_ID, SEVERITY_ID, START_TIME_ID, END_TIME_ID, DURATION_ID, NVL(DM.MESSAGE,NVL(DEM.MESSAGE,T1.MESSAGE)) MESSAGE_ID from tab1 T1, mgmt$policies R, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) PM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en''AND COUNTRY_CODE='' '') PEM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) CM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''CATEGORY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') CEM , (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=??EMIP_BIND_LOCALE_LANGUAGE?? AND COUNTRY_CODE=??EMIP_BIND_LOCALE_COUNTRY??) DM, (SELECT MESSAGE,MESSAGE_ID FROM MGMT$MESSAGES WHERE SUBSYSTEM=''POLICY'' AND LANGUAGE_CODE=''en'' AND COUNTRY_CODE='' '') DEM where T1.POLICY_GUID = R.POLICY_GUID AND R.POLICY_LABEL_NLSID=PM.MESSAGE_ID(+) AND R.POLICY_LABEL_NLSID=PEM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CM.MESSAGE_ID(+) AND R.CATEGORY_NLSID=CEM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DM.MESSAGE_ID(+) AND T1.MESSAGE_NLSID=DEM.MESSAGE_ID(+) ORDER BY T1.collection_timestamp '); l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.resourceBundle', 'oracle.sysman.resources.eml.ip.OOTBResourceBundle'); l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortColumn', '4'); l_param_values(4) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.initialSortOrder', 'ascending'); l_param_values(5) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.numRowsToShow', '10'); l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD( 'oracle.sysman.eml.ip.render.elem.TableRender.severityColumn', '3'); l_elem_order := l_elem_order +1; l_curr_row := l_curr_row+1; l_element_guid := mgmt_ip.add_element_to_report_def ( l_report_guid, 'IPMSG_USER_TABLE_FROM_SQL', 'IPMSG_ANY_TARGET_TYPE', 'EMR_Policy_Violations', l_elem_order, l_curr_row, l_param_values, null); COMMIT; END; /