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

Purpose of Query

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

Applicable CAST Version

ReleaseYes/No
8.3.x✅
8.2.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server❓
Microsoft SQL Server❓
CSS2✅

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