SQL Queries - CAST Central Base - Queries on Metrics - How to check which rules have changed criticality between snapshots

Created by John McMahon on Feb 11, 2022
Purpose of Query

 This page provides a query to check  the rules which have changed criticality between two snapshots.

Applicable CAST Version


Release
Yes/No
8.3.x (tick)
8.2.x (tick)
Applicable RDBMS


RDBMS
Yes/No
Oracle Server  (question)
Microsoft SQL Server  (question)
CSS2 (tick)


Query for CSS

The query below requires the previous and current snapshot ids which can be obtained by following this page:  SQL Queries - CAST Central Base - Queries on snapshots - How to get the ID and name of a snapshot for a given application

SELECT    A.metric_id         ,
          A.metric_description,
          B.metric_id         ,
          B.metric_description
FROM      ( SELECT r.*,
                  d.metric_description
          FROM    dss_metric_histo_tree r
                  JOIN dss_metric_descriptions d
                  ON      d.metric_id = r.metric_id
          WHERE   metric_critical     = 1
          AND     snapshot_id         = <previous snapshot_id>
          AND     language            = 'ENGLISH'
          AND     description_type_id = 0
          )
          A
          FULL JOIN
                    ( SELECT r.*,
                            d.metric_description
                    FROM    dss_metric_histo_tree r
                            JOIN dss_metric_descriptions d
                            ON      d.metric_id = r.metric_id
                    WHERE   metric_critical     = 1
                    AND     snapshot_id         = <current snapshot_id>
                    AND     language            = 'ENGLISH'
                    AND     description_type_id = 0
                    )
                    B
          ON        A.metric_parent_id = B.metric_parent_id
          AND       A.metric_id        = B.metric_id
          AND       A.metric_index     = B.metric_index
          AND       A.metric_type      = B.metric_type
WHERE     A.metric_id            IS NULL
OR        B.metric_id            IS NULL
Query result example
 
;"";610;"Avoid circular references between header files"
7740;"Avoid HTTP response splitting";;""


Query result interpretation
The query will list the rules which have changed criticality between the snapshots.  If the first set of values is blank and the second is populated, this means that the metric was not critical in the previous snapshot but is critical in the current snapshot.  If the second set is blank and the first is populated, this means that the metric is not critical in the current snapshot but was critical in the previous snapshot.  The query will only show one or the other scenarios.


Query for Oracle
Enter the SQL query
Query result example

Query result interpretation


Query for SQL server
Enter the SQL query
Query result example

Query result interpretation

Notes/comments
 Ticket # 27079



Related Pages