Purpose of Query

The purpose of this query is to provide grades for all Health Factors (Security, Transferability, etc) for all versions of all applications in the central database.

Applicable CAST Version
Release
Yes/No
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(question)
Microsoft SQL Server(question)
CSS2(tick)
Query for CSS
SELECT   dos.OBJECT_NAME               AS "Name"                 ,
         dos.OBJECT_FULL_NAME          AS "Description"          ,
         ds.SNAPSHOT_ID                AS "Snapshot"             ,
         ds.SNAPSHOT_NAME              AS "Version"              ,
         SUM(dmr.TQI)                  AS "TQI"                  ,
         SUM(dmr.Architecture)         AS "Architecture"         ,
         SUM(dmr.Changeability)        AS "Changeability"        ,
         SUM(dmr.Documentation)        AS "Documentation"        ,
         SUM(dmr.Efficiency)           AS "Efficiency"           ,
         SUM(dmr.ProgrammingPractices) AS "Programming Practices",
         SUM(dmr.Robustness)           AS "Robustness"           ,
         SUM(dmr.Security)             AS "Security"             ,
         SUM(dmr.Maintainability)      AS "SEI Maintainability"  ,
         SUM(dmr.Transferability)      AS "Transferability"
FROM     DSS_SNAPSHOTS ds
         JOIN DSS_OBJECTS dos
         ON       dos.OBJECT_ID = ds.APPLICATION_ID
         JOIN
                  (SELECT dmr.SNAPSHOT_ID,
                          dmr.OBJECT_ID  ,
                          CASE
                                  WHEN dmr.METRIC_ID = 60011
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Transferability,
                          CASE
                                  WHEN dmr.METRIC_ID = 60012
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Changeability,
                          CASE
                                  WHEN dmr.METRIC_ID = 60013
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Robustness,
                          CASE
                                  WHEN dmr.METRIC_ID = 60014
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Efficiency,
                          CASE
                                  WHEN dmr.METRIC_ID = 60015
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Maintainability,
                          CASE
                                  WHEN dmr.METRIC_ID = 60016
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Security,
                          CASE
                                  WHEN dmr.METRIC_ID = 60017
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS TQI,
                          CASE
                                  WHEN dmr.METRIC_ID = 66031
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS ProgrammingPractices,
                          CASE
                                  WHEN dmr.METRIC_ID = 66032
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Architecture,
                          CASE
                                  WHEN dmr.METRIC_ID = 66033
                                  THEN dmr.METRIC_NUM_VALUE
                                  ELSE 0
                          END AS Documentation
                  FROM    DSS_METRIC_RESULTS dmr
                  WHERE   dmr.METRIC_VALUE_INDEX = 0
                  AND     dmr.METRIC_ID IN (60011,
                                            60012,
                                            60013,
                                            60014,
                                            60015,
                                            60016,
                                            60017,
                                            66031,
                                            66032,
                                            66033)
                  )
                  dmr
         ON       dmr.SNAPSHOT_ID = ds.SNAPSHOT_ID
         AND      dmr.OBJECT_ID   = ds.APPLICATION_ID
GROUP BY dos.OBJECT_NAME     ,
         dos.OBJECT_FULL_NAME,
         ds.SNAPSHOT_ID      ,
         ds.SNAPSHOT_NAME    ,
         ds.FUNCTIONAL_DATE
ORDER BY dos.OBJECT_NAME,
         ds.FUNCTIONAL_DATE
Query result example
 "ASP_tests";"ASP_tests";4;"V1";3.56581312206312;3.87819992507493;3.66486782893033;3.10802469135802;4;3.54319132834758;3.51456311776079;3.58333333333333;3.4220743203573;3.3752986018611
Query result interpretation
 Lists the application name, description, snapshot id, version, and all grades for TQI and other health factors.
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
 

 

 

Related Pages