Rem drv: <create type="post_creation" pos="esm/esm_post_creation.sql-"/>
Rem
Rem $Header: esm_core_reports.sql 27-jul-2006.04:32:32 niramach Exp $
Rem
Rem esm_core_reports.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem esm_core_reports.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem niramach 07/27/06 - Backport niramach_bug-5331253 from main
Rem dsukhwal 12/09/05 - Backport dsukhwal_bug-4750935 from main
Rem niramach 06/20/06 - Fix for bug 5331253
Rem dsukhwal 11/18/05 - handle timezones
Rem dsukhwal 09/02/05 - fix timeSeriesChart
Rem gsbhatia 07/23/05 - Fix repmgr header
Rem gsbhatia 07/03/05 - New repmgr header impl
Rem lgloyd 06/24/05 - normalize category strings
Rem dsukhwal 06/22/05 - add instruction text
Rem dsukhwal 05/27/05 - using builtin elements, bug 4359156
Rem dkjain 03/31/05 - Fix for elements appeared on the ADD page
Rem dsukhwal 02/01/05 - dsukhwal_reports2
Rem dsukhwal 01/24/05 - Created
Rem
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_curr_order NUMBER;
l_curr_row NUMBER;
BEGIN
l_curr_order := 1;
l_curr_row := 1;
----------------Violation Flux Start----------------------------------------
-- late binding for any target type
l_target_types := MGMT_IP_TARGET_TYPES();
l_target_types.extend(1);
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');
--Delete earlier version of OOB system report
BEGIN
mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,
'TIMED_VIOLATION_FLUX_NLSID',
1,
0);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'TIMED_VIOLATION_FLUX_NLSID',
p_description_nlsid => 'TIMED_VIOLATION_FLUX_DESC_NLSID',
p_owner => mgmt_user.get_repository_owner,
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'Security_Policy_NLSID',
p_late_binding_target_types => null,--l_target_types,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1,
p_parameters => l_param_values
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'VIOL_FLUX_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
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_element_order => l_curr_order,
p_element_row => l_curr_row,
p_parameters => l_param_values,
p_targets => null);
l_curr_order := l_curr_order + 1;
l_curr_row := l_curr_row + 1;
--instruction text end--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(8);
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatementIsPlSql',
'true');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'BEGIN
DECLARE
rep_tz VARCHAR2(200);
user_tz_in VARCHAR2(200);
TYPE CURSOR_TYPE IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
BEGIN
result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
start_date_in := ??EMIP_BIND_START_DATE??;
end_date_in := ??EMIP_BIND_END_DATE??;
user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??;
select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep'';
query_string := ''WITH dates AS (SELECT
mgmt_view_util.adjust_tz(:1,:2,:3) as start_date,
mgmt_view_util.adjust_tz(:4,:5,:6) as end_date
from dual)
SELECT
label,
time,
sum(violations) --) as violations
FROM(
(
SELECT
''''''||''NEW_LBL''||'''''' as label,
mgmt_view_util.adjust_tz(rollup_timestamp, :7, :8) as time,
average as violations --new violations
FROM
MGMT$METRIC_HOURLY
WHERE
key_value in (''''18'''',''''20'''',''''25'''') AND
ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
target_type = ''''oracle_emrep'''' AND
metric_name = ''''TARGET_SECURITY_NEW_VIOLATIONS'''' AND
metric_column = ''''NEW_VIOLATIONS'''' )
UNION
(
SELECT
''''''||''FIXED_LBL''||'''''' as label,
mgmt_view_util.adjust_tz(rollup_timestamp,:9,:10) as time,
average as violations --cleared violations
FROM
MGMT$METRIC_HOURLY
WHERE
key_value in (''''18'''',''''20'''',''''25'''') AND
ROLLUP_TIMESTAMP > (SELECT start_date FROM dates) AND
ROLLUP_TIMESTAMP < (SELECT end_date FROM dates) AND
target_type = ''''oracle_emrep'''' AND
metric_name = ''''TARGET_SECURITY_CLEARED_VIOLATIONS'''' AND
metric_column = ''''CLEARED_VIOLATIONS''''
)
)
GROUP BY time, label
ORDER BY time ASC'';
OPEN result_cursor_out for query_string using start_date_in, user_tz_in, rep_tz, end_date_in, user_tz_in, rep_tz, user_tz_in, rep_tz, user_tz_in, rep_tz;
END;
END;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
'400');
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',
'timeSeriesChart');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.fill',
'cumulative');
l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
'true');
l_param_values(8) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
'oracle.sysman.resources.eml.esa.EsaMsg');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_header_nlsid => null,
p_element_order => l_curr_order,
p_element_row => l_curr_row,
p_parameters => l_param_values,
p_targets => null
);
----------------Violation Flux End----------------------------------------
----------------Security Compliance Start----------------------------------------
l_curr_order := 1;
l_curr_row := 1;
-- late binding for any target type
l_target_types := MGMT_IP_TARGET_TYPES();
l_target_types.extend(1);
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');
--Delete earlier version of OOB system report
BEGIN
mgmt_ip.delete_report_by_title(mgmt_user.get_repository_owner,
'TIMED_SECURITY_COMPLIANCE_NLSID',
1,
0);
EXCEPTION
WHEN OTHERS THEN NULL;
END;
l_report_guid := mgmt_ip.create_report_definition (
p_title_nlsid => 'TIMED_SECURITY_COMPLIANCE_NLSID',
p_description_nlsid => 'TIMED_SECURITY_COMPLIANCE_DESC_NLSID',
p_owner => mgmt_user.get_repository_owner,
p_category_nlsid => 'IPMSG_SECURITY_CATEGORY',
p_sub_category_nlsid => 'Security_Policy_NLSID',
p_late_binding_target_types => null,--l_target_types,
p_late_binding_multi_targets => 0,
p_show_table_of_contents => 0,
p_system_report => 1,
p_parameters => l_param_values
);
--instruction text begin--------------------------------
l_param_values := MGMT_IP_PARAM_VALUE_LIST();
l_param_values.extend(3);
--The text to be displayed
l_param_values(1) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textMessage',
'SEC_COMPL_INSTR_TEXT');
--The resource bundle containing the text
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textResourceBundleName',
'oracle.sysman.resources.eml.esa.EsaMsg');
--Specifies the style class
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.TextParamBean.textStyleClass',
'OraInstructionText');
--Adding instruction text to the created report definition and passing the above parameters
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_element_order => l_curr_order,
p_element_row => l_curr_row,
p_parameters => l_param_values,
p_targets => null);
l_curr_order := l_curr_order + 1;
l_curr_row := l_curr_row + 1;
--instruction text end--------------------------------
l_curr_order := l_curr_order + 1;
l_curr_row := l_curr_row + 1;
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.sqlStatementIsPlSql',
'true');
l_param_values(2) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.sqlStatement',
'BEGIN
DECLARE
rep_tz VARCHAR2(200);
user_tz_in VARCHAR2(200);
TYPE CURSOR_TYPE IS REF CURSOR;
result_cursor_out CURSOR_TYPE;
start_date_in DATE DEFAULT NULL;
end_date_in DATE DEFAULT NULL;
query_string VARCHAR(6000);
BEGIN
result_cursor_out := ??EMIP_BIND_RESULTS_CURSOR??;
start_date_in := ??EMIP_BIND_START_DATE??;
end_date_in := ??EMIP_BIND_END_DATE??;
user_tz_in := ??EMIP_BIND_TIMEZONE_REGION??;
select TIMEZONE_REGION into rep_tz from mgmt$target where TARGET_TYPE = ''oracle_emrep'';
query_string := ''SELECT ''''COMPL_SCORE_GRAPH_LABEL'''' as COLUMN_LABEL,
mgmt_view_util.adjust_tz(rollup_timestamp, :1, :2) as ROLLUP_TIMESTAMP,
round(avg(average)) as AVERAGE
FROM
MGMT$METRIC_HOURLY
WHERE
key_value in (''''18'''',''''20'''',''''25'''') AND
ROLLUP_TIMESTAMP > mgmt_view_util.adjust_tz(:3,:4,:5) AND
ROLLUP_TIMESTAMP < mgmt_view_util.adjust_tz(:6,:7,:8) AND
target_type = ''''oracle_emrep'''' AND
metric_name = ''''TARGET_SECURITY_COMPLIANCE'''' AND
metric_column = ''''COMPLIANCE_SCORE''''
GROUP BY rollup_timestamp
ORDER BY rollup_timestamp asc'';
OPEN result_cursor_out for query_string using user_tz_in, rep_tz, start_date_in, user_tz_in, rep_tz, end_date_in, user_tz_in, rep_tz;
END;
END;');
l_param_values(3) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.width',
'400');
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',
'timeSeriesChart');
l_param_values(6) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.resourceBundle',
'oracle.sysman.resources.eml.esa.EsaMsg');
l_param_values(7) := MGMT_IP_PARAM_VALUE_RECORD(
'oracle.sysman.eml.ip.render.elem.ChartParamController.translateKeyValues',
'true');
l_element_guid := mgmt_ip.add_element_to_report_def (
p_report_guid => l_report_guid,
p_element_name_nlsid => 'IPMSG_USER_CHART_FROM_SQL',
p_element_type_nlsid => 'IPMSG_ANY_TARGET_TYPE',
p_header_nlsid => null,
p_element_order => l_curr_order,
p_element_row => l_curr_row,
p_parameters => l_param_values,
p_targets => null
);
----------------Security Compliance End----------------------------------------
END;
/
COMMIT;