Rem drv: <create type="pkgbodys" pos="policy/policy_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/compliance/compliance_config_std_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/19 09:29:00 suvmalho Exp $
Rem
Rem compliance_config_std_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem compliance_config_std_pkgbody.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 groyal 11/20/06 - Remove references to 11g specific tables
Rem niramach 10/16/06 - Fix for bug 5557714 (Security fix).
Rem groyal 08/25/06 - Replace total_violations with crit_violations,
Rem warn_violations and info_violations, Rework trend-related procedures
Rem sthiruna 08/10/06 - Fix for Bug 5404180. Modifying the logic for error
Rem status in log_cs_evaluation_results.
Rem sthiruna 08/03/06 - Commenting 11g packages
Rem niramach 07/20/06 - Add update_cs_cqs_valid procedure to em_cs_compile_util pkg.
Rem rreilly 07/17/06 - add ics rqs_guid generation
Rem rreilly 07/10/06 - fix compliance score and do bulk calculation
Rem aragarwa 06/28/06 - Fixing xsd changes : removing context, adding
Rem targettype
Rem rreilly 06/29/06 - adding the cs compile and evaluate packages
Rem sthiruna 06/08/06 - Fix for bug 5263883
Rem aragarwa 06/11/06 - Removing default for few elements
Rem aragarwa 05/17/06 - Removing hierarchy dname columns.
Rem niramach 05/03/06 - Changes related to delete eval results API
Rem - 10.3 and 11g table unification
Rem - target filteration for trend pages
Rem aragarwa 05/01/06 - Adding support for reusable queries and cs
Rem inclusion
Rem niramach 04/11/06 - Add delete cs eval results Api.
Rem aragarwa 03/31/06 - Change MGMT_SHORT_STRING_TABLE to
Rem MGMT_MEDIUM_STRING_TABLE for delete cs policies.
Rem niramach 03/27/06 - Add procedures for trend overview sqls.
Rem aragarwa 02/28/06 - reflecting XSD and table changes.
Rem niramach 03/05/06 - Remove logging compliance score for rulefolder
Rem level.
Rem niramach 02/28/06 - Add performance log for cs evaluation.
Rem kchiasso 02/23/06 - change owner to author
Rem niramach 01/25/06 - Add procedures for cs evaluation.
Rem aragarwa 01/23/06 - Adding procedures and functions for adding rows
Rem in cs tables.
Rem niramach 01/13/06 - Package body for configuration standards.
Rem niramach 01/13/06 - Created
Rem
Rem ****************************************************************************
Rem EM_CONFIG_STD package body
Rem ****************************************************************************
CREATE OR REPLACE PACKAGE BODY em_config_std
AS
PROCEDURE commit_delete_cs_txn(p_execute_stmt VARCHAR2 , p_config_std_guid RAW,
p_is_super_user INTEGER )
IS
l_counter INTEGER :=0;
BEGIN
LOOP
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(' Executing SQL ' || p_execute_stmt, G_MODULE_NAME);
END IF;
EXECUTE IMMEDIATE p_execute_stmt USING p_config_std_guid, p_is_super_user,
MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET,
MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;
l_counter := SQL%ROWCOUNT;
COMMIT;
IF l_counter < mgmt_global.MAX_COMMIT THEN
EXIT;
END IF;
END LOOP;
END commit_delete_cs_txn;
FUNCTION add_rulefolder (
p_cs_guid RAW,
p_rulefolder_iname VARCHAR2,
p_rulefolder_dname VARCHAR2,
p_parent_guid RAW,
p_child_position NUMBER,
p_description VARCHAR2 DEFAULT NULL,
p_reference_url VARCHAR2 DEFAULT NULL
) RETURN RAW
IS
l_rulefolder_guid RAW(16);
BEGIN
INSERT INTO mgmt_cs_rulefolder (cs_guid, rulefolder_iname, rulefolder_dname,
description, reference_url)
VALUES (p_cs_guid, p_rulefolder_iname, p_rulefolder_dname,
p_description, p_reference_url)
RETURNING rulefolder_guid INTO l_rulefolder_guid;
INSERT INTO mgmt_cs_hierarchy (parent_guid,child_guid,
child_type, child_position)
VALUES (p_parent_guid, l_rulefolder_guid,
MGMT_CONFIG_STD.G_ELEMENT_RULEFOLDER, p_child_position);
RETURN l_rulefolder_guid;
END add_rulefolder;
FUNCTION add_rule(
p_cs_guid RAW,
p_rule_iname VARCHAR2,
p_rule_dname VARCHAR2,
p_parent_guid RAW,
p_child_position NUMBER,
p_test VARCHAR2 DEFAULT NULL,
p_importance_level NUMBER DEFAULT MGMT_CONFIG_STD.G_IMPORTANCE_NORMAL,
p_test_type NUMBER DEFAULT NULL,
p_reference_url VARCHAR2 DEFAULT NULL,
p_description VARCHAR2 DEFAULT NULL,
p_rationale VARCHAR2 DEFAULT NULL,
p_fixtext VARCHAR2 DEFAULT NULL,
p_warning VARCHAR2 DEFAULT NULL,
p_message VARCHAR2 DEFAULT NULL,
p_clear_message VARCHAR2 DEFAULT NULL,
p_severity NUMBER DEFAULT MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO,
p_policy_guid RAW DEFAULT NULL,
p_change_advisor_tag MGMT_CS_CHANGE_ADVISOR_DEF DEFAULT NULL,
p_simple_test_condition NUMBER DEFAULT NULL
) RETURN RAW
IS
l_rule_guid RAW(16);
l_change_advisor_type VARCHAR2(256) := NULL;
l_change_advisor_subtype VARCHAR2(256) := NULL;
l_change_advisor_reason VARCHAR2(256) := NULL;
BEGIN
IF ( p_change_advisor_tag IS NOT NULL ) THEN
l_change_advisor_type := p_change_advisor_tag.change_advisor_type;
l_change_advisor_subtype := p_change_advisor_tag.change_advisor_subtype;
l_change_advisor_reason := p_change_advisor_tag.change_advisor_reason;
END IF;
INSERT INTO mgmt_cs_rule (cs_guid, rule_iname, rule_dname,
importance_level, description, reference_url,
rationale, fixtext, warning,
test_type, test, policy_guid,
change_advisor_type, change_advisor_subtype, change_advisor_reason,
message, clear_message, severity, simple_test_condition)
VALUES (p_cs_guid, p_rule_iname, p_rule_dname,
p_importance_level, p_description, p_reference_url,
p_rationale, p_fixtext, p_warning,
p_test_type, p_test, p_policy_guid,
l_change_advisor_type,l_change_advisor_subtype,l_change_advisor_reason,
p_message, p_clear_message, p_severity, p_simple_test_condition)
RETURNING rule_guid INTO l_rule_guid ;
INSERT INTO mgmt_cs_hierarchy (parent_guid, child_guid,
child_type, child_position)
VALUES (p_parent_guid, l_rule_guid,
MGMT_CONFIG_STD.G_ELEMENT_RULE, p_child_position);
RETURN l_rule_guid;
END add_rule;
PROCEDURE add_simple_test(
p_rule_guid RAW,
p_property VARCHAR2,
p_operator NUMBER,
p_value VARCHAR2,
p_position NUMBER
)
IS
BEGIN
INSERT INTO mgmt_cs_rule_simple_test(
rule_guid, property, operator, value, position)
VALUES (
p_rule_guid, p_property, p_operator, p_value, p_position);
END add_simple_test ;
PROCEDURE add_fixlink(
p_rule_guid RAW,
p_display_label VARCHAR2,
p_link_template VARCHAR2,
p_link_encode NUMBER,
p_is_link_empage NUMBER
)
IS
BEGIN
INSERT INTO mgmt_cs_rule_fix_link(
rule_guid, display_label, link_template,
link_encode,is_link_em_page )
VALUES (
p_rule_guid,p_display_label,p_link_template,
p_link_encode, p_is_link_empage);
END add_fixlink ;
-- Add keyword to configuration standard.
PROCEDURE add_keyword(
p_cs_guid RAW,
p_keyword VARCHAR2
)
IS
BEGIN
INSERT INTO mgmt_cs_keyword (keyword, cs_guid)
VALUES (p_keyword, p_cs_guid);
END add_keyword;
PROCEDURE add_reusable_query(
p_cs_guid RAW,
p_query_iname VARCHAR2,
p_description VARCHAR2 DEFAULT NULL,
p_query VARCHAR2
)
IS
BEGIN
INSERT INTO mgmt_cs_reusable_query (cs_guid,
reusable_query_iname, description, reusable_query)
VALUES(p_cs_guid,
p_query_iname, p_description, p_query);
END add_reusable_query;
PROCEDURE add_overridden_parameter(
p_cs_inclusion_guid RAW,
p_name VARCHAR2,
p_value VARCHAR2,
p_reference VARCHAR2 DEFAULT NULL
)
IS
BEGIN
INSERT INTO mgmt_cs_inclusion_parameter (cs_inclusion_guid,
param_iname, param_override_value, ref_param_iname)
VALUES( p_cs_inclusion_guid,
p_name, p_value, p_reference);
END add_overridden_parameter;
PROCEDURE add_violation_context (
p_rule_guid RAW,
p_column_iname VARCHAR2,
p_column_dname VARCHAR2,
p_column_type NUMBER,
p_column_position NUMBER,
p_is_key NUMBER,
p_is_hidden NUMBER,
p_link_template VARCHAR2,
p_link_encode NUMBER,
p_is_empage NUMBER
)
IS
BEGIN
INSERT INTO mgmt_cs_rule_viol_ctx (rule_guid,
column_iname, column_dname, column_type, column_position, is_key,
is_hidden, link_template,link_encode, is_link_em_page)
VALUES( p_rule_guid,
p_column_iname, p_column_dname, p_column_type, p_column_position, p_is_key,
p_is_hidden, p_link_template, p_link_encode, p_is_empage);
END add_violation_context;
PROCEDURE add_parameter(
p_cs_guid RAW,
p_param_iname VARCHAR2,
p_description VARCHAR2 DEFAULT NULL,
p_param_type NUMBER DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_NUMTYPE,
p_param_value VARCHAR2,
p_prohibit_changes NUMBER DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_PROCHANGE_FALSE,
p_lower_bound NUMBER DEFAULT 0,
p_upper_bound NUMBER DEFAULT 0,
p_choice_list MGMT_CS_CHOICE_LIST_ARRAY DEFAULT NULL,
p_must_match NUMBER DEFAULT 0
)
IS
l_choice VARCHAR2(1024);
l_param_guid RAW(16);
choice_ctr NUMBER;
BEGIN
INSERT INTO mgmt_cs_parameter (cs_guid, param_iname,
param_type,param_value,
description,prohibit_changes, must_match,
lower_bound, upper_bound)
VALUES(p_cs_guid, p_param_iname,
p_param_type, p_param_value,
p_description, p_prohibit_changes,p_must_match,
p_lower_bound, p_upper_bound)
RETURNING param_guid INTO l_param_guid ;
-- Insert into parameters
IF ((p_choice_list IS NOT NULL) AND (p_choice_list.COUNT > 0))
THEN
FOR choice_ctr IN p_choice_list.FIRST..p_choice_list.LAST
LOOP
l_choice := p_choice_list(choice_ctr);
INSERT INTO mgmt_cs_parameter_choices( param_guid, choice_value )
VALUES ( l_param_guid , l_choice);
END LOOP;
END IF;
END add_parameter;
PROCEDURE delete_cs_data(
p_cs_guid RAW)
IS
l_delete_stmt VARCHAR2(4000);
l_counter INTEGER :=0;
BEGIN
-- Remove from all tables that have cs guid
FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name,
mgc.name cs_column_name
FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc
WHERE o.owner# = userenv('SCHEMAID')
AND tgc.name like ('%TARGET_GUID%')
AND tgc.obj# = o.obj#
AND mgc.name like ('%CS_GUID%')
AND mgc.obj# = o.obj#
AND o.name like ('MGMT_%')
AND o.obj# = t.obj#
AND bitand(t.property, 1) = 0
AND bitand(tgc.property,32) = 0
AND bitand(tgc.property,512) = 0
AND bitand(mgc.property,32) = 0
AND bitand(mgc.property,512) = 0
ORDER BY o.name )
LOOP
l_delete_stmt := 'DELETE FROM ' || EM_CHECK.simple_sql_name(crec.table_name) ||
' WHERE ' || EM_CHECK.simple_sql_name(crec.cs_column_name) || ' = HEXTORAW(:cs_guid)' ||
' AND ROWNUM <= :MAX_COMMIT_SIZE' ;
LOOP
EXECUTE IMMEDIATE l_delete_stmt USING p_cs_guid, mgmt_global.MAX_COMMIT;
l_counter := SQL%ROWCOUNT;
COMMIT;
IF l_counter < mgmt_global.MAX_COMMIT THEN
EXIT;
END IF;
END LOOP;
END LOOP;
END delete_cs_data;
PROCEDURE delete_cs_metadata(
p_cs_guid RAW)
IS
BEGIN
-- Remove all copied policies
remove_copied_policies(p_cs_guid);
DELETE FROM mgmt_cs_keyword
WHERE cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_hierarchy
WHERE child_guid in
(select rule_guid from mgmt_cs_rule
where cs_guid = p_cs_guid
union all
select rulefolder_guid from mgmt_cs_rulefolder
where cs_guid = p_cs_guid
union all
select cs_inclusion_guid from mgmt_cs_inclusion
where parent_cs_guid = p_cs_guid );
-- first clear the choices table and then clean up parameters.
DELETE FROM mgmt_cs_parameter_choices
WHERE param_guid IN
( SELECT param_guid FROM mgmt_cs_parameter
WHERE cs_guid = p_cs_guid );
DELETE FROM mgmt_cs_parameter
WHERE cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_reusable_query
WHERE cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_inclusion_parameter
WHERE cs_inclusion_guid IN
( SELECT cs_inclusion_guid FROM mgmt_cs_inclusion
WHERE parent_cs_guid = p_cs_guid );
DELETE FROM mgmt_cs_rule_fix_link
WHERE rule_guid IN
( SELECT rule_guid FROM mgmt_cs_rule
WHERE cs_guid = p_cs_guid AND TEST_TYPE = MGMT_CONFIG_STD.G_TEST_TYPE_DMQL);
DELETE FROM mgmt_cs_rule_viol_ctx
WHERE rule_guid IN
( SELECT rule_guid FROM mgmt_cs_rule
WHERE cs_guid = p_cs_guid AND TEST_TYPE = MGMT_CONFIG_STD.G_TEST_TYPE_DMQL);
DELETE FROM mgmt_cs_rule_simple_test
WHERE rule_guid IN
( SELECT rule_guid FROM mgmt_cs_rule
WHERE cs_guid = p_cs_guid);
DELETE FROM mgmt_cs_inclusion
WHERE parent_cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_rule
WHERE cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_rulefolder
WHERE cs_guid = p_cs_guid;
DELETE FROM mgmt_cs_config_standard
WHERE cs_guid = p_cs_guid;
END delete_cs_metadata;
PROCEDURE remove_copied_policies(
p_cs_guid RAW)
IS
l_policy_names MGMT_MEDIUM_STRING_TABLE := MGMT_MEDIUM_STRING_TABLE();
l_target_types MGMT_MEDIUM_STRING_TABLE := MGMT_MEDIUM_STRING_TABLE();
l_policy_name mgmt_policies.policy_name%TYPE;
l_target_type mgmt_policies.target_type%TYPE;
BEGIN
SELECT p.policy_name, p.target_type
BULK COLLECT INTO l_policy_names, l_target_types
FROM mgmt_cs_rule r, mgmt_policies p
WHERE r.cs_guid = p_cs_guid
AND p.policy_guid = r.policy_guid;
IF ((l_policy_names IS NOT NULL) AND (l_policy_names.COUNT > 0))
THEN
FOR ctr IN l_policy_names.FIRST..l_policy_names.LAST
LOOP
l_policy_name := l_policy_names(ctr);
l_target_type := l_target_types(ctr);
MGMT_POLICY.delete_policy(p_target_type => l_target_type,
p_policy_name => l_policy_name);
END LOOP;
END IF;
END remove_copied_policies;
--
-- PURPOSE
--
-- To set the repository timing flag of the given configuration standard.This flag
-- is to enable/disable logging the performance details of the cs evaluation in the
-- the system performance logs.
--
-- Parameters
-- p_config_std_name : Configuration Standard internal name
-- p_config_std_author: Author of the configuration standard
-- p_version : Version of the configuration standard.
-- p_enabled: Enabled/disabled flag . (0/1).
PROCEDURE set_repo_timing(
p_config_std_name IN VARCHAR2,
p_config_std_author IN VARCHAR2,
p_version IN NUMBER DEFAULT 1,
p_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE)
IS
BEGIN
em_check.check_range(p_value=>p_enabled,
p_min_value=>0,
p_max_value=>1,
p_param_name=>' p_enabled ') ;
UPDATE mgmt_cs_config_standard
SET repo_timing_enabled = p_enabled
WHERE cs_iname = p_config_std_name AND
author = p_config_std_author AND
version = p_version ;
IF SQL%NOTFOUND
THEN
raise_application_error(MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR,
MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR_M||'Config Std Name='|| p_config_std_name || ' author='
|| p_config_std_author || ' version='|| p_version);
END IF ;
END set_repo_timing;
-- ---------------------------------------------------
-- Procedure: LOG_CS_EVALUATION_RESULTS
--
-- Description:
--
-- Called by MGMT_CONFIG_STD.EVALUATE_CONFIG_STD
-- and from Java Classes.
--
-- After all CS rules have been evaluated, summary
-- counts and the compliance score are calculated
-- for all targets. The results of these
-- calculations are stored in MGMT_CS_EVAL_SUMM_* tables.
--
-- Parameters:
--
-- p_config_std_name - Configuration Standard Name
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Configuration Standard Guid
-- p_target_type - target type
-- p_targets_list - MGMT_TARGET_ARRAY having the list of MGMT_TARGET_OBJ
-- p_repo_timing_enabled - Flag to turn on/off performance data logging.
-- p_start_time - time the evaluation started
--
-- NOTE:
-- p_targets_list and p_start_time are NULL for DMQL CS.
-- These values are retrieved from repository tables
-- for this case.
-- ---------------------------------------------------
PROCEDURE log_cs_evaluation_results(
p_config_std_name IN VARCHAR2,
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_target_type IN VARCHAR2,
p_targets_list IN MGMT_TARGET_ARRAY DEFAULT NULL,
p_repo_timing_enabled IN NUMBER,
p_start_time IN DATE DEFAULT NULL)
IS
TYPE l_targets_cur IS REF CURSOR;
l_targets GUID_ARRAY;
l_targets_sql l_targets_cur;
l_start_time DATE := p_start_time;
l_proceed BOOLEAN := TRUE;
l_cs_type NUMBER(1);
l_target_guid RAW(16);
l_total_rules_evaluated NUMBER := 0;
l_rules_evaluated NUMBER := 0;
BEGIN
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG('log_cs_evaluation_results:Enter',G_MODULE_NAME) ;
END IF ;
-- Get the Type of the CS rules: Policy or DMQL
-- Currrently all CS rules must be of the same type
BEGIN
SELECT max(test_type), count(rule_iname)
INTO l_cs_type, l_total_rules_evaluated
FROM mgmt_cs_rule
WHERE cs_guid=p_cs_guid
GROUP BY cs_guid;
EXCEPTION WHEN NO_DATA_FOUND THEN
EMDW_LOG.ERROR('log_cs_evaluation_results: Error: ' ||
'The CS ' || p_cs_guid || ' has no associated rules.',
G_MODULE_NAME);
l_proceed := FALSE;
END;
-- Get the List of Targets
IF l_cs_type = mgmt_config_std.G_TEST_TYPE_DMQL THEN
EMDW_LOG.ERROR('log_cs_evaluation_results: Error: The type was DMQL.',
G_MODULE_NAME);
l_proceed := FALSE;
ELSE
OPEN l_targets_sql FOR
SELECT t.target_guid
FROM MGMT_TARGETS t, TABLE(CAST(p_targets_list as MGMT_TARGET_ARRAY)) p
WHERE t.target_name = p.target_name
AND t.target_type = p.target_type;
END IF;
FETCH l_targets_sql BULK COLLECT INTO l_targets;
CLOSE l_targets_sql;
-- Check for No Targets
IF (l_targets IS NULL OR l_targets.COUNT <= 0) THEN
EMDW_LOG.ERROR('log_cs_evaluation_results: Error: ' ||
'No Targets were evaluated for CS ' || p_cs_guid,
G_MODULE_NAME);
l_proceed := FALSE;
END IF;
IF (l_proceed = TRUE) THEN
-- POLICY CS ONLY: Save rule summary info for all rules and targets
IF (l_cs_type = mgmt_config_std.G_TEST_TYPE_POLICY) THEN
MERGE INTO
mgmt_cs_eval_summ_rule e
USING
(SELECT
rt.target_guid AS target_guid,
rt.rule_guid AS rule_guid,
nvl(p.total_violations_logged, 0) AS total_violations,
nvl(p.last_evaluation_date, l_start_time) AS evaluation_date,
nvl(err.status,
case nvl(p.total_violations_logged, -1)
when -1 then mgmt_config_std.G_UNKNOWN_RULES
when 0 then mgmt_config_std.G_COMPLIANT_RULES
else mgmt_config_std.G_NONCOMPLIANT_RULES end) AS status
FROM
mgmt_policy_assoc_eval_summ p,
(SELECT
ar.rule_guid AS rule_guid,
ar.policy_guid AS policy_guid,
at.column_value AS target_guid
FROM mgmt_cs_rule ar, TABLE(cast(l_targets AS GUID_ARRAY)) at
WHERE ar.cs_guid = p_cs_guid) rt,
(SELECT
distinct policy_guid, target_guid,
mgmt_config_std.G_ERROR_RULES AS status
FROM mgmt_policy_assoc_eval_details
WHERE last_error_message IS NOT NULL) err
WHERE rt.policy_guid = p.policy_guid(+)
AND rt.target_guid = p.target_guid(+)
AND p.policy_guid = err.policy_guid(+)
AND p.target_guid = err.target_guid(+)) t
ON (e.rqs_guid = p_rqs_guid
AND t.target_guid = e.target_guid
AND t.rule_guid = e.rule_guid)
WHEN MATCHED THEN
UPDATE SET total_violations = t.total_violations,
evaluation_date=t.evaluation_date, status = t.status
WHEN NOT MATCHED THEN
INSERT
(rqs_guid,target_guid,rule_guid,root_cs_guid,
root_target_guid,total_violations,status,evaluation_date)
VALUES
(p_rqs_guid, t.target_guid,t.rule_guid,p_cs_guid,
t.target_guid, t.total_violations,t.status,t.evaluation_date);
END IF;
-- Calculate Rule Folder Counts
process_rule_folders(
p_rqs_guid => p_rqs_guid,
p_cs_guid => p_cs_guid,
p_targets => l_targets);
-- Calculate CS Counts and Compliance Score
process_cs(
p_rqs_guid => p_rqs_guid,
p_cs_guid => p_cs_guid,
p_targets => l_targets,
p_eval_date => l_start_time);
-- Performance Numbers
IF p_repo_timing_enabled = MGMT_GLOBAL.G_TRUE THEN
MGMT_LOG.log_performance
(v_job_name_in => G_MODULE_NAME,
v_duration_in => (SYSDATE - l_start_time)*86400000,
v_time_in => SYSDATE,
v_is_total_in => 'Y',
v_name_in => 'Total rules evaluated for all selected targets' ,
v_value_in => (l_total_rules_evaluated * l_targets.COUNT),
v_client_data_in => p_config_std_name) ;
END IF ;
END IF;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG('log_cs_evaluation_results:Exit Normal',G_MODULE_NAME) ;
END IF ;
END log_cs_evaluation_results;
-- ---------------------------------------------------
-- Procedure: PROCESS_RULE_FOLDERS
--
-- Description:
--
-- Calculates CS evaluation results for all rule
-- folders. Rule Folders deepest in the hierarchy
-- are evaluated evaluated first. This is because
-- all the child rule folder counts are needed to get
-- the values for a parent rule folder.
--
-- Parameters:
--
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Unique ID for the CS
-- p_targets - table of evaluated targets
--
-- ---------------------------------------------------
PROCEDURE process_rule_folders(
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_targets IN GUID_ARRAY)
IS
CURSOR l_getFoldersCursor IS
SELECT rf.child_guid
FROM (SELECT child_guid, child_type, LEVEL as child_level
FROM mgmt_cs_hierarchy
START WITH parent_guid=p_cs_guid
CONNECT BY PRIOR child_guid = parent_guid) rf
WHERE rf.child_type = mgmt_config_std.G_ELEMENT_RULEFOLDER
ORDER BY rf.child_level DESC;
l_folders GUID_ARRAY;
l_folder_guid RAW(16);
BEGIN
-- Get all rule folders, lowest folders first..
OPEN l_getFoldersCursor;
FETCH l_getFoldersCursor BULK COLLECT INTO l_folders;
CLOSE l_getFoldersCursor;
-- NOTE: Rule Folders are processessed one at a time, rule
-- folders with the greatest LEVEL values must be
-- processed first.
IF ((l_folders IS NOT NULL) AND (l_folders.COUNT > 0)) THEN
FOR i IN l_folders.FIRST..l_folders.LAST LOOP
l_folder_guid := l_folders(i);
-- MERGE all data for this rule folder into
-- MGMT_CS_EVAL_SUMM_RULEFOLDER for all targets
MERGE INTO
mgmt_cs_eval_summ_rulefolder e
USING
(SELECT
c.target_guid, c.root_cs_guid, c.root_target_guid,
sum(c.compliant_rules) as compliant_rules,
sum(c.non_compliant_rules) as non_compliant_rules,
sum(c.error_rules) as error_rules,
sum(c.unknown_rules) as unknown_rules,
sum(c.crit_violations) as crit_violations,
sum(c.warn_violations) as warn_violations,
sum(c.info_violations) as info_violations
FROM
(SELECT
s.target_guid, s.root_cs_guid, s.root_target_guid,
decode(s.status, mgmt_config_std.G_COMPLIANT_RULES, 1, 0)
as compliant_rules,
decode(s.status, mgmt_config_std.G_NONCOMPLIANT_RULES, 1, 0)
as non_compliant_rules,
decode(s.status, mgmt_config_std.G_ERROR_RULES, 1, 0)
as error_rules,
decode(s.status, mgmt_config_std.G_UNKNOWN_RULES, 1, 0)
as unknown_rules,
decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_CRITICAL, s.total_violations, 0)
as crit_violations,
decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_WARNING, s.total_violations, 0)
as warn_violations,
decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_INFO, s.total_violations, 0)
as info_violations
FROM
mgmt_cs_eval_summ_rule s,
mgmt_cs_rule r,
TABLE(CAST(p_targets as GUID_ARRAY)) trg,
(SELECT child_guid as rule_guid
FROM mgmt_cs_hierarchy
WHERE parent_guid = l_folder_guid
AND child_type = mgmt_config_std.G_ELEMENT_RULE) chld
WHERE s.rqs_guid = p_rqs_guid
AND s.target_guid = trg.column_value
AND s.rule_guid = chld.rule_guid
AND r.rule_guid = s.rule_guid
UNION ALL
SELECT
sf.target_guid, sf.root_cs_guid, sf.root_target_guid,
sf.compliant_rules,
sf.non_compliant_rules,
sf.error_rules,
sf.unknown_rules,
sf.crit_violations,
sf.warn_violations,
sf.info_violations
FROM
mgmt_cs_eval_summ_rulefolder sf,
TABLE(CAST(p_targets as GUID_ARRAY)) trg,
(SELECT child_guid as rulefolder_guid
FROM mgmt_cs_hierarchy
WHERE parent_guid = l_folder_guid
AND child_type = mgmt_config_std.G_ELEMENT_RULEFOLDER) chld
WHERE sf.rqs_guid = p_rqs_guid
AND sf.target_guid = trg.column_value
AND sf.rulefolder_guid = chld.rulefolder_guid) c
GROUP BY c.target_guid, c.root_cs_guid, c.root_target_guid) t
ON (e.rulefolder_guid = l_folder_guid
AND e.rqs_guid = p_rqs_guid
AND e.target_guid = t.target_guid)
WHEN MATCHED THEN
UPDATE SET compliant_rules = t.compliant_rules,
non_compliant_rules = t.non_compliant_rules,
error_rules = t.error_rules,
unknown_rules = t.unknown_rules,
crit_violations = t.crit_violations,
warn_violations = t.warn_violations,
info_violations = t.info_violations
WHEN NOT MATCHED THEN
INSERT
(rqs_guid,target_guid,rulefolder_guid,root_cs_guid,
root_target_guid,compliant_rules, non_compliant_rules,
error_rules, unknown_rules,
crit_violations, warn_violations, info_violations)
VALUES
(p_rqs_guid, t.target_guid,l_folder_guid,t.root_cs_guid,
t.root_target_guid, t.compliant_rules, t.non_compliant_rules,
t.error_rules, t.unknown_rules,
t.crit_violations, t.warn_violations, t.info_violations);
END LOOP;
END IF;
END process_rule_folders;
-- ---------------------------------------------------
-- Procedure: PROCESS_CS
--
-- Description:
--
-- Calculates CS counts and compliance score for
-- all targets.
--
-- Parameters:
--
-- p_rqs_guid - CS Runtime Query Set Guid
-- p_cs_guid - Unique ID for the CS
-- p_targets - table of evaluated targets
-- p_eval_date - cs evaluation date
--
-- ---------------------------------------------------
PROCEDURE process_cs(
p_rqs_guid IN RAW,
p_cs_guid IN RAW,
p_targets IN GUID_ARRAY,
p_eval_date IN DATE)
IS
BEGIN
-- MERGE CS data for all targets into the MGMT_CS_EVAL_SUMM_RQS table
-- NOTE: the USING query ignores all rules that have an importance_level
-- of not scored. It does this, because importance_level of
-- not scored is 0 and summing 0 or multiplying by 0 = 0, and 0
-- means ignored. So, if G_IMPORTANCE_NOT_SCORED changes from 0
-- to another value, this query will break and the refrences to
-- r.importance_level will need to be wrapped in a CASE statement
-- to force not scored rules to be ignored.
MERGE INTO
mgmt_cs_eval_summ_rqs e
USING
(SELECT
c.target_guid, c.root_cs_guid, c.root_target_guid,
c.compliant_rules, c.non_compliant_rules,
c.error_rules, c.unknown_rules,
c.crit_violations, c.warn_violations, c.info_violations,
(CASE WHEN c.importance_total = 0 THEN 0
ELSE (c.weighted_total/c.importance_total) END)
as compliance_score
FROM
(SELECT
s.target_guid, s.root_cs_guid, s.root_target_guid,
sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN 1
ELSE 0 END) as compliant_rules,
sum(CASE WHEN s.status = mgmt_config_std.G_NONCOMPLIANT_RULES THEN 1
ELSE 0 END) as non_compliant_rules,
sum(CASE WHEN s.status = mgmt_config_std.G_ERROR_RULES THEN 1
ELSE 0 END) as error_rules,
sum(CASE WHEN s.status = mgmt_config_std.G_UNKNOWN_RULES THEN 1
ELSE 0 END) as unknown_rules,
sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_CRITICAL, s.total_violations, 0))
as crit_violations,
sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_WARNING, s.total_violations, 0))
as warn_violations,
sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_INFO, s.total_violations, 0))
as info_violations,
sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN
r.importance_level
WHEN s.status = mgmt_config_std.G_NONCOMPLIANT_RULES THEN
r.importance_level
ELSE 0 END) as importance_total,
sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN
(r.importance_level * 100)
ELSE 0 END) as weighted_total
FROM mgmt_cs_rule r, mgmt_cs_eval_summ_rule s,
TABLE(CAST(p_targets as GUID_ARRAY)) trg
WHERE r.cs_guid = p_cs_guid
AND s.rqs_guid = p_rqs_guid
AND s.target_guid = trg.column_value
AND r.rule_guid = s.rule_guid
GROUP BY s.rqs_guid, s.target_guid,
s.root_cs_guid, s.root_target_guid) c
) t
ON (e.rqs_guid = p_rqs_guid
AND e.target_guid = t.target_guid)
WHEN MATCHED THEN
UPDATE SET compliant_rules = t.compliant_rules,
non_compliant_rules = t.non_compliant_rules,
error_rules = t.error_rules,
unknown_rules = t.unknown_rules,
crit_violations = t.crit_violations,
warn_violations = t.warn_violations,
info_violations = t.info_violations,
last_evaluation_date = p_eval_date,
compliance_score = t.compliance_score
WHEN NOT MATCHED THEN
INSERT
(rqs_guid, target_guid,root_cs_guid, root_target_guid,
compliant_rules, non_compliant_rules, error_rules,
unknown_rules, crit_violations, warn_violations, info_violations, last_evaluation_date,
compliance_score)
VALUES
(p_rqs_guid, t.target_guid,t.root_cs_guid, t.root_target_guid,
t.compliant_rules, t.non_compliant_rules, t.error_rules,
t.unknown_rules, t.crit_violations, t.warn_violations, t.info_violations, p_eval_date,
t.compliance_score);
END process_cs;
-- ----------------------------------------------------
-- Compliance trend overview charts related procedures.
-- ----------------------------------------------------
--
-- PURPOSE
-- Returns the min and max timestamps for data availability for the specified
-- metric.
--
-- Parameters
-- p_rqs_guid : Guid of config std.
-- p_object_guid : Guid of config std/rule folder/rule for which trend data is required.
-- p_metric_name : Name of metric for which trend data to be shown
-- p_metric_column : Name of metric column for which trend data to be shown
-- p_target_name : Target for which trend data to be shown
-- p_min_collection_time: Min data availablity
-- p_max_collection_time: Max data availablity
--
PROCEDURE get_min_max_collection_time(
p_rqs_guid IN RAW,
p_object_guid IN RAW,
p_metric_name IN VARCHAR2,
p_metric_column IN VARCHAR2,
p_target_name IN VARCHAR2 DEFAULT '%',
p_min_collection_time OUT DATE,
p_max_collection_time OUT DATE
)
IS
l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
BEGIN
l_repos_tgt_guid := get_repos_target_guid();
SELECT MIN_TIME, MAX_TIME INTO p_min_collection_time, p_max_collection_time
FROM
(with metric_info as
(select l_repos_tgt_guid as target_guid, m.metric_guid, k.composite_key
from
mgmt_targets tgt,
mgmt_metrics m,
mgmt_metrics_composite_keys k
where
m.target_type = 'oracle_emrep' and
m.metric_name = p_metric_name and
m.metric_column = p_metric_column and
k.target_guid = l_repos_tgt_guid and
k.key_part1_value = p_rqs_guid and
k.key_part2_value = p_object_guid and
k.key_part3_value = tgt.target_guid and
tgt.target_name like p_target_name
)
select min(t) as MIN_TIME, max(t) as MAX_TIME from
(
select rm.collection_timestamp as t
from metric_info, mgmt_metrics_raw rm
where
rm.target_guid = metric_info.target_guid and
rm.metric_guid = metric_info.metric_guid and
rm.key_value = metric_info.composite_key
union all
select hm.rollup_timestamp as t
from metric_info, mgmt_metrics_1hour hm
where
hm.target_guid = metric_info.target_guid and
hm.metric_guid = metric_info.metric_guid and
hm.key_value = metric_info.composite_key
union all
select dm.rollup_timestamp as t
from metric_info, mgmt_metrics_1day dm
where
dm.target_guid = metric_info.target_guid and
dm.metric_guid = metric_info.metric_guid and
dm.key_value = metric_info.composite_key
));
END get_min_max_collection_time;
--
-- Returns the trend(historic) data for a given configuration standard and
-- time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
-- p_comp_score_buckets_cursor: Cursor for compliance score buckets chart
--
PROCEDURE get_cs_trend_data(
p_rqs_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor,
p_comp_score_buckets_cursor OUT sys_refcursor
)
IS
l_table_type NUMBER := G_RAW_TABLE_TYPE;
l_table_name VARCHAR2(30);
l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
l_time_range VARCHAR2(2000);
l_summ_sql VARCHAR2(32767);
l_bucket_sql VARCHAR2(32767);
l_inDays BOOLEAN;
BEGIN
l_repos_tgt_guid := get_repos_target_guid();
l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
IF (p_historyForNDays IS NOT NULL)
THEN
l_inDays := TRUE;
l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
ELSE
l_inDays := FALSE;
l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
END IF;
IF (l_table_type = G_RAW_TABLE_TYPE)
THEN
IF (l_inDays)
THEN
l_time_range := 'v.collection_timestamp >= sysdate - :4';
ELSE
l_time_range := 'v.collection_timestamp >= :4 and v.collection_timestamp <= :5';
END IF;
l_summ_sql :=
'SELECT
key.key_part1_value as rqs_guid,
round(avg(to_number(v.value))) as total_violations,
round(avg(to_number(vs.value))) as compliance_score,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.collection_timestamp as time
FROM
mgmt_metrics m,
mgmt_metrics ms,
mgmt_metrics_raw v,
mgmt_metrics_raw vs,
mgmt_metrics_composite_keys key,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and
ms.target_type = m.target_type and
ms.metric_name = m.metric_name and
m.metric_column = ''VIOLATION_COUNT'' and
ms.metric_column = ''COMPLIANCE_SCORE'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
vs.target_guid = v.target_guid and
vs.metric_guid = ms.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.composite_key = vs.key_value and
key.key_part1_value = :2 and
t.target_name like :3 and
key.key_part3_value = t.target_guid and '
|| l_time_range || ' and
vs.collection_timestamp = v.collection_timestamp
GROUP BY key.key_part1_value, v.collection_timestamp
ORDER BY v.collection_timestamp';
l_bucket_sql :=
'WITH buckets as
(
SELECT
avgs.target_guid,
avgs.rqs_guid,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=1) then 1 else 0 end as bucket1,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=2) then 1 else 0 end as bucket2,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=3) then 1 else 0 end as bucket3,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=4) then 1 else 0 end as bucket4,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=5) then 1 else 0 end as bucket5,
avgs.time
FROM
(SELECT
key.key_part1_value as rqs_guid,
key.key_part3_value as target_guid,
round(avg(to_number(v.value))) as compliance_score,
v.collection_timestamp as time
FROM
mgmt_metrics m,
mgmt_metrics_raw v,
mgmt_metrics_composite_keys key,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and
m.metric_column = ''COMPLIANCE_SCORE'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part3_value = t.target_guid and
key.key_part1_value = :2 and
t.target_name like :3 and '
|| l_time_range || '
GROUP BY key.key_part1_value, key.key_part3_value, v.collection_timestamp) avgs
)
SELECT
x.rqs_guid as rqs_guid,
SUM(x.bucket1) as bucket1,
SUM(x.bucket2) as bucket2,
SUM(x.bucket3) as bucket3,
SUM(x.bucket4) as bucket4,
SUM(x.bucket5) as bucket5,
x.time
FROM buckets x
GROUP BY x.rqs_guid,time
ORDER BY time';
ELSE
IF (l_inDays)
THEN
l_time_range := 'v.rollup_timestamp >= sysdate - :4';
ELSE
l_time_range := 'v.rollup_timestamp >= :4 and v.rollup_timestamp <= :5';
END IF;
l_table_name :=
CASE l_table_type
WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
WHEN G_1DAY_TABLE_TYPE THEN 'mgmt_metrics_1day'
ELSE 'mgmt_metrics_1hour'
END;
l_summ_sql :=
'SELECT
key.key_part1_value as rqs_guid,
round(avg(to_number(v.value_average))) as total_violations,
round(avg(to_number(vs.value_average))) as compliance_score,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.rollup_timestamp as time
FROM
mgmt_metrics m,
mgmt_metrics ms, '
|| l_table_name || ' v, '
|| l_table_name || ' vs,
mgmt_metrics_composite_keys key ,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and
ms.target_type = m.target_type and
ms.metric_name = m.metric_name and
m.metric_column = ''VIOLATION_COUNT'' and
ms.metric_column = ''COMPLIANCE_SCORE'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
vs.target_guid = v.target_guid and
vs.metric_guid = ms.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.composite_key = vs.key_value and
key.key_part1_value = :2 and
t.target_name like :3 and
key.key_part3_value = t.target_guid and '
|| l_time_range || ' and
vs.rollup_timestamp = v.rollup_timestamp
GROUP BY key.key_part1_value, v.rollup_timestamp
ORDER BY v.rollup_timestamp';
l_bucket_sql :=
'WITH buckets as
(
SELECT
avgs.target_guid,
avgs.rqs_guid,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=1) then 1 else 0 end as bucket1,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=2) then 1 else 0 end as bucket2,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=3) then 1 else 0 end as bucket3,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=4) then 1 else 0 end as bucket4,
case when(width_bucket(avgs.compliance_score, 0, 101, 5)=5) then 1 else 0 end as bucket5,
avgs.time
FROM
(SELECT
key.key_part1_value as rqs_guid,
key.key_part3_value as target_guid,
round(avg(to_number(v.value_average))) as compliance_score,
v.rollup_timestamp as time
FROM
mgmt_metrics m, '
|| l_table_name || ' v,
mgmt_metrics_composite_keys key,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and
m.metric_column = ''COMPLIANCE_SCORE'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part3_value = t.target_guid and
key.key_part1_value = :2 and
t.target_name like :3 and '
|| l_time_range || '
GROUP BY key.key_part1_value, key.key_part3_value, v.rollup_timestamp) avgs
)
SELECT
x.rqs_guid as rqs_guid,
SUM(x.bucket1) as bucket1,
SUM(x.bucket2) as bucket2,
SUM(x.bucket3) as bucket3,
SUM(x.bucket4) as bucket4,
SUM(x.bucket5) as bucket5,
x.time
FROM buckets x
GROUP BY x.rqs_guid,time
ORDER BY time';
END IF;
IF (l_inDays)
THEN
OPEN p_eval_summ_cursor FOR
l_summ_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyForNDays;
OPEN p_comp_score_buckets_cursor FOR
l_bucket_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyForNDays;
ELSE
OPEN p_eval_summ_cursor FOR
l_summ_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyStartDate, p_historyEndDate;
OPEN p_comp_score_buckets_cursor FOR
l_bucket_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyStartDate, p_historyEndDate;
END IF;
END get_cs_trend_data;
--
-- PURPOSE
-- Returns the trend(historic) data for a given rule folder and time period.
--
-- Parameters
-- p_rqs_guid: Guid of the config standard for which trend data is required.
-- p_folder_guid : Guid of folder for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_folder_trend_data(
p_rqs_guid IN RAW,
p_folder_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor
)
IS
l_table_type NUMBER := G_RAW_TABLE_TYPE;
l_table_name VARCHAR2(30);
l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
l_time_range VARCHAR2(2000);
l_summ_sql VARCHAR2(32767);
l_inDays BOOLEAN;
BEGIN
l_repos_tgt_guid := get_repos_target_guid();
l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
IF (p_historyForNDays IS NOT NULL)
THEN
l_inDays := TRUE;
l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
ELSE
l_inDays := FALSE;
l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
END IF;
IF (l_table_type = G_RAW_TABLE_TYPE)
THEN
IF (l_inDays)
THEN
l_time_range := 'v.collection_timestamp >= sysdate - :5';
ELSE
l_time_range := 'v.collection_timestamp >= :5 and v.collection_timestamp <= :6';
END IF;
l_summ_sql :=
'SELECT
key.key_part2_value as folder_guid,
round(avg(to_number(v.value))) as total_violations,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.collection_timestamp as time
FROM
mgmt_metrics m,
mgmt_metrics_raw v,
mgmt_metrics_composite_keys key ,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_FOLDER'' and
m.metric_column = ''VIOLATION_COUNT'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part1_value = :2 and
key.key_part2_value = :3 and
t.target_name like :4 and
key.key_part3_value = t.target_guid and '
|| l_time_range || ' and
v.collection_timestamp = v.collection_timestamp and
v.collection_timestamp = v.collection_timestamp
GROUP BY key.key_part2_value, v.collection_timestamp
ORDER BY v.collection_timestamp';
ELSE
IF (l_inDays)
THEN
l_time_range := 'v.rollup_timestamp >= sysdate - :5';
ELSE
l_time_range := 'v.rollup_timestamp >= :5 and v.rollup_timestamp <= :6';
END IF;
l_table_name :=
CASE l_table_type
WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
WHEN G_1DAY_TABLE_TYPE THEN 'mgmt_metrics_1day'
ELSE 'mgmt_metrics_1hour'
END;
l_summ_sql :=
'SELECT
key.key_part2_value as folder_guid,
round(avg(to_number(v.value_average))) as total_violations,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.rollup_timestamp as time
FROM
mgmt_metrics m, '
|| l_table_name || ' v,
mgmt_metrics_composite_keys key ,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_FOLDER'' and
m.metric_column = ''VIOLATION_COUNT'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part1_value = :2 and
key.key_part2_value = :3 and
t.target_name like :4 and
key.key_part3_value = t.target_guid and '
|| l_time_range || ' and
v.rollup_timestamp = v.rollup_timestamp and
v.rollup_timestamp = v.rollup_timestamp
GROUP BY key.key_part2_value, v.rollup_timestamp
ORDER BY v.rollup_timestamp';
END IF;
IF (l_inDays)
THEN
OPEN p_eval_summ_cursor FOR l_summ_sql
USING l_repos_tgt_guid, p_rqs_guid, p_folder_guid, p_target_name, p_historyForNDays;
ELSE
OPEN p_eval_summ_cursor FOR l_summ_sql
USING l_repos_tgt_guid, p_rqs_guid, p_folder_guid, p_target_name, p_historyStartDate, p_historyEndDate;
END IF;
END get_folder_trend_data;
--
-- PURPOSE
-- Returns the trend(historic) data for a given rule and time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_rule_guid : Guid of rule for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_rule_trend_data(
p_rqs_guid IN RAW,
p_rule_guid IN RAW,
p_target_name IN VARCHAR2 DEFAULT '%',
p_historyForNDays IN NUMBER DEFAULT NULL,
p_historyStartDate IN DATE DEFAULT NULL,
p_historyEndDate IN DATE DEFAULT NULL,
p_eval_summ_cursor OUT sys_refcursor
)
IS
l_table_type NUMBER := G_RAW_TABLE_TYPE;
l_table_name VARCHAR2(30);
l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
l_time_range VARCHAR2(2000);
l_summ_sql VARCHAR2(32767);
l_inDays BOOLEAN;
BEGIN
l_repos_tgt_guid := get_repos_target_guid();
IF (p_historyForNDays IS NOT NULL)
THEN
l_inDays := TRUE;
l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
ELSE
l_inDays := FALSE;
l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
END IF;
IF (l_table_type = G_RAW_TABLE_TYPE)
THEN
IF (l_inDays)
THEN
l_time_range := 'v.collection_timestamp >= sysdate - :5';
ELSE
l_time_range := 'v.collection_timestamp >= :5 and v.collection_timestamp <= :6';
END IF;
l_summ_sql :=
'SELECT
key.key_part2_value as rule_guid,
round(avg(to_number(v.value))) as total_violations,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.collection_timestamp as time
FROM
mgmt_metrics m,
mgmt_metrics_raw v,
mgmt_metrics_composite_keys key ,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_RULE'' and
m.metric_column = ''VIOLATION_COUNT'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part1_value = :2 and
key.key_part2_value = :3 and
t.target_name like :4 and
key.key_part3_value = t.target_guid and '
|| l_time_range || '
GROUP BY key.key_part2_value, v.collection_timestamp
ORDER BY v.collection_timestamp';
ELSE
IF (l_inDays)
THEN
l_time_range := 'v.rollup_timestamp >= sysdate - :5';
ELSE
l_time_range := 'v.rollup_timestamp >= :5 and v.rollup_timestamp <= :6';
END IF;
l_table_name :=
CASE l_table_type
WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
WHEN G_1DAY_TABLE_TYPE THEN 'mgmt_metrics_1day'
ELSE 'mgmt_metrics_1hour'
END;
l_summ_sql :=
'SELECT
key.key_part2_value as rule_guid,
round(avg(to_number(v.value_average))) as total_violations,
count(distinct(key.key_part3_value)) as targets_evaluated,
v.rollup_timestamp as time
FROM
mgmt_metrics m, '
|| l_table_name || ' v,
mgmt_metrics_composite_keys key ,
mgmt_targets t
WHERE
m.target_type = ''oracle_emrep'' and
m.metric_name = ''CS_EVAL_SUMM_RULE'' and
m.metric_column = ''VIOLATION_COUNT'' and
v.target_guid = :1 and
v.metric_guid = m.metric_guid and
key.target_guid = v.target_guid and
key.composite_key = v.key_value and
key.key_part1_value = :2 and
key.key_part2_value = :3 and
t.target_name like :4 and
key.key_part3_value = t.target_guid and '
|| l_time_range || '
GROUP BY key.key_part2_value, v.rollup_timestamp
ORDER BY v.rollup_timestamp';
END IF;
IF (l_inDays)
THEN
OPEN p_eval_summ_cursor FOR l_summ_sql
USING l_repos_tgt_guid, p_rqs_guid, p_rule_guid, p_target_name, p_historyForNDays;
ELSE
OPEN p_eval_summ_cursor FOR l_summ_sql
USING l_repos_tgt_guid, p_rqs_guid, p_rule_guid, p_target_name, p_historyStartDate, p_historyEndDate;
END IF;
END get_rule_trend_data;
--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyForNDays : Number of days for which historic data is required
--
FUNCTION get_trend_table_type(
p_historyForNDays IN NUMBER DEFAULT 7
) RETURN NUMBER
IS
l_table_type NUMBER := G_1HOUR_TABLE_TYPE;
BEGIN
--More than 7 days history
IF (p_historyForNDays > 7)
THEN
l_table_type := G_1DAY_TABLE_TYPE;
--More than 1 day history
ELSIF (p_historyForNDays > 1)
THEN
l_table_type := G_1HOUR_TABLE_TYPE;
ELSE
l_table_type := G_RAW_TABLE_TYPE;
END IF;
RETURN l_table_type;
END get_trend_table_type;
--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyStartDate : Start date from when onwards the historic data is required.
--
FUNCTION get_trend_table_type(
p_historyStartDate IN DATE
) RETURN NUMBER
IS
l_raw_retention_window NUMBER := 0;
l_1hour_retention_window NUMBER := 0;
l_table_type NUMBER := G_RAW_TABLE_TYPE;
l_period NUMBER := 0;
BEGIN
--Get the retention windows
l_raw_retention_window := emd_loader.get_raw_retention_window;
l_1hour_retention_window := emd_loader.get_1hour_retention_window;
--If start date is given
IF (l_period <= (l_raw_retention_window*86400000))
THEN
l_table_type := G_RAW_TABLE_TYPE;
ELSIF (l_period <= (l_1hour_retention_window*86400000))
THEN
l_table_type := G_1HOUR_TABLE_TYPE;
ELSE
l_table_type := G_1DAY_TABLE_TYPE;
END IF;
RETURN l_table_type;
END get_trend_table_type;
--
-- PURPOSE
--
-- To delete the evaluation results of the given configuration standard for all targets
-- for which the user has operator target privileges.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_cs_eval_results ( p_config_std_guid RAW )
IS
l_delete_stmt VARCHAR2(4000);
is_super_user INTEGER := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
BEGIN
is_super_user := MGMT_USER.has_priv(MGMT_USER.GET_CURRENT_EM_USER,MGMT_USER.SUPER_USER);
--There could be more number of records,so handle commit along the way.
l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rqs ' ||
'WHERE ' ||
'rqs_guid = HEXTORAW(:config_std_guid ) AND '||
'((:is_super_user = :USER_HAS_PRIV) OR '||
'(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
'target_guid) = :USER_HAS_PRIV)) AND '||
' ROWNUM <= :MAX_COMMIT_SIZE' ;
--Executes the delete statement and commits the txn appropriately
em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt,
p_config_std_guid => p_config_std_guid,
p_is_super_user => is_super_user);
--submit job which deletes the evaluation results from rule folder and policy
-- tables.
submit_delete_eval_results_job(p_config_std_guid => p_config_std_guid);
END delete_cs_eval_results;
--
-- PURPOSE
--
-- To delete the evaluation results of the rule folder of the given config std for
-- all targets evaluated for which the user has the target operator privileges.
-- This api will be called from delete eval results job.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_eval_results_data (
p_config_std_guid RAW)
IS
l_policy_guids MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
l_policy_guid mgmt_policies.policy_guid%TYPE;
l_delete_stmt VARCHAR2(4000);
l_counter INTEGER :=0;
l_annot_purged NUMBER := 0;
l_notify_log_purged NUMBER := 0;
l_notify_rq NUMBER := 0;
is_super_user INTEGER := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
BEGIN
is_super_user := MGMT_USER.has_priv(MGMT_USER.GET_CURRENT_EM_USER,MGMT_USER.SUPER_USER);
--delete for all targets evaluated
l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rulefolder '||
'WHERE '||
'rqs_guid = HEXTORAW(:config_std_guid) AND '||
'((:is_super_user = :USER_HAS_PRIV) OR '||
'(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
'target_guid) = :USER_HAS_PRIV)) AND '||
'ROWNUM <= :MAX_COMMIT_SIZE';
--Executes the delete statement and commits the txn appropriately
em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt,
p_config_std_guid => p_config_std_guid,
p_is_super_user => is_super_user);
--Get the policy guids of the rules under the rule folders
SELECT policy_guid BULK COLLECT INTO l_policy_guids
FROM mgmt_cs_rule
WHERE cs_guid = p_config_std_guid;
IF ((l_policy_guids IS NOT NULL) AND (l_policy_guids.COUNT > 0))
THEN
FOR ctr IN l_policy_guids.FIRST..l_policy_guids.LAST
LOOP
l_policy_guid := l_policy_guids(ctr);
--disable the trigger on MGMT_VIOLATION for delete
EM_SEVERITY.g_from_purge_api := TRUE ;
-- Remove from all tables that have policy guid
-- MGMT_VIOLATION_*, MGMT_POLICY_EVAL_* tables are included here
FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name,
mgc.name policy_column_name
FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc
WHERE o.owner# = userenv('SCHEMAID')
AND tgc.name like ('%TARGET_GUID%')
AND tgc.obj# = o.obj#
AND mgc.name like ('%POLICY_GUID%')
AND mgc.obj# = o.obj#
AND o.name like ('MGMT_%')
AND o.obj# = t.obj#
AND bitand(t.property, 1) = 0
AND bitand(tgc.property,32) = 0
AND bitand(tgc.property,512) = 0
AND bitand(mgc.property,32) = 0
AND bitand(mgc.property,512) = 0
ORDER BY o.name )
LOOP
--delete for targets evaluated
l_delete_stmt := 'DELETE FROM ' || EM_CHECK.simple_sql_name(crec.table_name) ||
' WHERE ' || EM_CHECK.simple_sql_name(crec.policy_column_name) || ' = HEXTORAW(:policy_guid) '||
' AND ((:is_super_user = :USER_HAS_PRIV) OR '||
' (MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
EM_CHECK.simple_sql_name(crec.target_column_name) ||') = :USER_HAS_PRIV))'||
' AND ROWNUM <= :MAX_COMMIT_SIZE' ;
LOOP
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(' Executing SQL ' || l_delete_stmt, G_MODULE_NAME);
END IF;
EXECUTE IMMEDIATE l_delete_stmt USING l_policy_guid , is_super_user,
MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET,
MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;
l_counter := SQL%ROWCOUNT;
-- Delete from other table through trigger has been disabled
-- for MGMT_VIOLATIONS using EM_SEVERITY.G_FROM_PURGE_API flag
-- hence deleting it using procedure.
IF crec.table_name = 'MGMT_VIOLATIONS' AND
EM_SEVERITY.p_violation_guids IS NOT NULL AND
EM_SEVERITY.p_violation_guids.count > 0 THEN
--CHECK: Is this call required?
EM_SEVERITY.purge_for_violation_guids
(
p_violation_guids => EM_SEVERITY.p_violation_guids,
p_annot_purged => l_annot_purged,
p_notify_log_purged => l_notify_log_purged,
p_notify_rq => l_notify_rq
);
EM_SEVERITY.p_violation_guids.delete;
EM_SEVERITY.p_violation_guids := MGMT_TARGET_GUID_ARRAY();
END IF;
COMMIT;
IF l_counter < mgmt_global.MAX_COMMIT THEN
EXIT;
END IF;
END LOOP; --END OF COMMIT CHECK LOOP
END LOOP; -- END OF FOR crec IN LOOP
--enable the trigger on MGMT_VIOLATION for delete
EM_SEVERITY.g_from_purge_api := FALSE ;
END LOOP; -- END OF policy guids LOOP
--Delete corresponding rule eval results records from mgmt_cs_eval_summ_rule table
l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rule '||
'WHERE '||
'rqs_guid = HEXTORAW(:config_std_guid) AND '||
'((:is_super_user = :USER_HAS_PRIV) OR '||
'(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
'target_guid) = :USER_HAS_PRIV)) '||
' AND ROWNUM <= :MAX_COMMIT_SIZE' ;
--Executes the delete statement and commits the txn appropriately
em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt,
p_config_std_guid => p_config_std_guid,
p_is_super_user => is_super_user);
END IF;
END delete_eval_results_data;
--
-- PURPOSE
--
-- Executes the given delete cs statement and commits along the way based on the MAX_COMMIT value.
--
--
-- Parameters
-- p_execute_stmt : Statement to be executed
-- p_config_std_guid : Configuration Standard Guid
-- p_is_super_user : Whether the current em user is super user or not.
--
/*
PROCEDURE commit_delete_cs_txn(p_execute_stmt VARCHAR2 , p_config_std_guid RAW,
p_is_super_user INTEGER )
IS
l_counter INTEGER :=0;
BEGIN
LOOP
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG(' Executing SQL ' || p_execute_stmt, G_MODULE_NAME);
END IF;
EXECUTE IMMEDIATE EM_CHECK.NOOP(p_execute_stmt) USING p_config_std_guid, p_is_super_user,
MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET,
MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;
l_counter := SQL%ROWCOUNT;
COMMIT;
IF l_counter < mgmt_global.MAX_COMMIT THEN
EXIT;
END IF;
END LOOP;
END commit_delete_cs_txn;
*/
--
-- PURPOSE
--
-- Submits the delete eval results job which takes care of deleting the evaluation
-- results from rule folder summary and policy evaluation results tables.
--
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE submit_delete_eval_results_job(
p_config_std_guid RAW)
IS
l_job_params MGMT_JOB_PARAM_LIST;
l_job_targets MGMT_JOB_TARGET_LIST;
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_job_name VARCHAR2(64);
l_job_id RAW(16);
l_execution_id RAW(16);
BEGIN
-- This job has no targets.
l_job_targets := MGMT_JOB_TARGET_LIST();
l_job_params := MGMT_JOB_PARAM_LIST();
l_job_params.extend(1);
l_job_params(1) := MGMT_JOB_PARAM_RECORD('configStdGuid', 1,
p_config_std_guid, null);
-- Its schedule is immediate.
l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
SYSDATE, null, 0, 0, 0, null, null,
MGMT_JOBS.TIMEZONE_REPOSITORY,
0, 0, null);
l_job_name := 'DEL_EVALRES_JOB_' || SYS_GUID();
BEGIN
MGMT_JOBS.submit_job(p_job_name => l_job_name,
p_description => 'This is a delete compliance evaluation results job',
p_job_type => MGMT_CONFIG_STD.G_DELETE_EVAL_RESULTS,
p_job_targets => l_job_targets,
p_job_params => l_job_params,
p_schedule => l_schedule,
p_job_id_out => l_job_id,
p_execution_id_out => l_execution_id,
p_owner => MGMT_USER.get_current_em_user(),
p_system_job => MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY,
p_job_creds => null,
p_job_target_type => null,
p_job_notify_states => null);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END submit_delete_eval_results_job;
--Returns the repository target guid.
FUNCTION get_repos_target_guid RETURN RAW
IS
l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();
BEGIN
BEGIN
-- Enter super-user mode to get the repository guid since only users having
-- access to repository can get the repository guid
SETEMUSERCONTEXT(MGMT_USER.get_repository_owner,
MGMT_USER.OP_SET_IDENTIFIER);
--Assumption: Only one target for repository target type.
--Get the repository target guid from mgmt_targets in super user mode.
SELECT target_guid INTO l_repos_tgt_guid
FROM mgmt_targets
WHERE target_type = 'oracle_emrep';
-- Revert back to being the same user as we entered
SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
EXCEPTION
WHEN OTHERS THEN
IF l_current_user IS NOT NULL THEN
SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
END IF;
RAISE;
END;
RETURN l_repos_tgt_guid;
END get_repos_target_guid;
END em_config_std;
/
show errors;