Purpose of Query
This query is to be run on CB. It gives for a specific snapshot, the list of critical rules and for every critical rule the list of objects violating the rule
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x | |
8.2.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS |
Query for CSS
Please run this query on central Database. Replace the snapshot_id and the application_id with the right values
--list of objects with critical violations SELECT DISTINCT zz.f_snapshot_id, zz.f_metric_id, zz.f_metric_name, zz.f_object_id, zz.f_object_full_name FROM (SELECT cd1.snapshot_id AS F_SNAPSHOT_ID, cpt.syst_id AS F_SYST_ID, cpt.app_id AS F_APP_ID, cqt.metric_critical AS F_M_CRIT, cqt.metric_id AS F_METRIC_ID, dmd.metric_description AS F_METRIC_NAME, cd1.object_id AS F_OBJECT_ID, (SELECT dso.object_full_name FROM dss_objects dso WHERE dso.object_id = cd1.object_id) AS F_OBJECT_FULL_NAME, Coalesce(cpxl.metric_num_value, 0) AS F_METRIC_NUM_VALUE, ( CASE WHEN Ocur.object_checksum IS NULL THEN 2 WHEN Oprev.object_checksum IS NULL THEN 1 WHEN Ocur.object_checksum = Oprev.object_checksum THEN 0 ELSE 3 END ) AS F_STATUS, Coalesce(OE.justify, 'NA') AS F_JUSTIFY, doe.access_action AS F_ACCESS_ACTION, doe.access_except AS F_ACCESS_EXCEPT FROM (SELECT object_id, snapshot_id, diag_id, context_id FROM dss_diagdetails) cd1 join dss_metric_histo_tree cqt ON ( cqt.metric_id = cd1.diag_id AND cqt.snapshot_id = cd1.snapshot_id ) join dss_metric_descriptions dmd ON ( dmd.metric_id = cqt.metric_id AND dmd.LANGUAGE = 'ENGLISH' AND dmd.description_type_id = 0 ) join dss_portf_tree cpt ON ( cd1.context_id = cpt.module_id AND cpt.snapshot_id = cd1.snapshot_id AND cpt.app_id = 3 ) join dss_objects_ex doe ON ( doe.object_id = cpt.app_id AND doe.access_list = 1 ) left join (SELECT dmr.metric_num_value, object_id, snapshot_id FROM dss_metric_results dmr WHERE dmr.metric_id = 65005) cpxl ON cpxl.object_id = cd1.object_id AND cpxl.snapshot_id = cd1.snapshot_id left join dss_object_exceptions OE ON ( OE.object_id = cd1.object_id AND OE.metric_id = cqt.metric_id AND cd1.snapshot_id < OE.last_snapshot_id ) join adg_delta_snapshots d ON ( d.application_id = cpt.app_id AND d.snapshot_id = cd1.snapshot_id ) left outer join dss_object_info Ocur ON ( Ocur.snapshot_id = d.snapshot_id AND Ocur.object_id = cd1.object_id ) left outer join dss_object_info Oprev ON ( Oprev.snapshot_id = d.prev_snapshot_id AND Oprev.object_id = cd1.object_id )) zz WHERE zz.f_m_crit = 1 AND zz.f_snapshot_id = <snapshot_id> AND zz.f_syst_id = 2 AND zz.f_app_id = 3 -- ORDER BY zz.f_snapshot_id, zz.f_metric_id, zz.f_object_id
Query result example
Here is an example of query results
3;1580;"Avoid using execute immediate";36804;"ORACLE.PROC1"
How to interpret results?
Above returned row means that object ORACLE.PROC1 (object_id 36804) is violating the critical rule "Avoid using execute immediate" (metric_id 1580) in snapshot 3
Query for Oracle
Not available
Query result example
Query for SQL server
Not available
Query result example
Notes/comments
Ticket # 4446
Related Pages