Health Dashboard - Information - How to identify SQL query of a specific tile using logs with SQL activated

Purpose

This page describes the methodology to identify the SQL query of a specific tile on CAST Application Analytics Dashboard (CAST HD) using logs with SQL activated.

Applicable in CAST Version

Release	Yes/No
8.3.x

Applicable RDBMS

RDBM	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Details

0 - Specifications related to ADD log with SQL traces activated

In ADD log, some of SQL queries are incomplete. For example in the following query we don't have the value of "tc.metric_parent_id" and "tc.snapshot_id"

SELECT qi.metric_id        AS QIID, 
       qi.aggregate_weight AS QIWEIGHT, 
       qi.metric_critical  AS QICRITICAL, 
       tc.metric_id        AS TCID, 
       tc.aggregate_weight AS TCWEIGHT 
FROM   dss_metric_histo_tree tc 
       JOIN dss_metric_histo_tree qi 
         ON qi.metric_parent_id = tc.metric_id 
            AND qi.snapshot_id = tc.snapshot_id 
WHERE  tc.metric_parent_id = ?
       AND tc.snapshot_id = ?

In "HD.log", and just bellow the query you will find the values of "tc.metric_parent_id" and "tc.snapshot_id" in the following format.

Setting SQL statement parameter value: column index 1, parameter value [60017], value class [java.lang.Integer], SQL type unknown
Setting SQL statement parameter value: column index 2, parameter value [1], value class [java.lang.Integer], SQL type unknown

In this example, the value of "tc.metric_parent_id" is 60017, and the value of "tc.snapshot_id " is 1.

The complete SQL query will be as follow:

SELECT qi.metric_id        AS QIID, 
       qi.aggregate_weight AS QIWEIGHT, 
       qi.metric_critical  AS QICRITICAL, 
       tc.metric_id        AS TCID, 
       tc.aggregate_weight AS TCWEIGHT 
FROM   dss_metric_histo_tree tc 
       JOIN dss_metric_histo_tree qi 
         ON qi.metric_parent_id = tc.metric_id 
            AND qi.snapshot_id = tc.snapshot_id 
WHERE  tc.metric_parent_id = 60017
       AND tc.snapshot_id = 1

Note that "HD.log" is an interactive log file. The log file is reloaded when navigating on the HD webpage, that's why you may find some duplicated SQL queries.

1 - Action plan to identify a specific tile in AAD using logs with SQL activated

Activate SQL traces: For activate SQL traces, please visit the page Health Dashboard - Information - How to activate SQL traces on HD and RestAPI logs

Get quality indicator id of the quality indicator appearing in the tile of CAST Application Analytics Dashboard (CAST HD): For getting quality indicator, edit the file %CATALINA_HOME%\webapps\CAST-HD\portal\resources\cmp.json, and identify "area" of your tile.

Assuming that we investigate about the tile shown in the screenshot below:

If, we edit the cmp.json file (%CATALINA_HOME%\webapps\CAST-HD\portal\resources\cmp.json), the definition of our tile is as follow:

	{
                    "id": 5,
                    "plugin": "QualityIndicatorResults",
                    "color": "yellow",
                    "parameters": {
                        "title": "Robustness",
                        "qualityIndicator": {
                            "id": "ROBUSTNESS",
                            "format": "0.00",
                            "description": ""
                        },
                        "widget": "gauge"
                    }
          }

Id of quality indicator of our tile is "Robustness".

From measurement database, get metric id of the quality indicator, for this run the following sql query on your measurement database:
```
SELECT metric_id
       metric_description 
FROM   dss_metric_descriptions 
WHERE  upper (metric_description) like 'Id of quality indicator  of our tile'
```
special cases
In measurement database, the following quality indicators of the tile of CAST Application Analytics Dashboard (CAST HD) have not the same id in cmp.json:
Tilte in cmp.json metric_description in measurement data base
Technical Size LOCS
Functional Size OMG-Compliant Automated Function Points
Number of violations to critical quality rules CRITICAL VIOLATIONS

For our example, on measurement database, we execute the following query:
```
SELECT metric_id, 
       metric_description 
FROM   dss_metric_descriptions 
WHERE  upper (metric_description) like 'ROBUSTNESS' 
```
As result, we get "metric_id" = 60013.

In "HD.log", search for the value of "metric_id". In our example, we found that 2 SQL queries in "HD.log", tries to have results for "metric id" = 60013 :

SELECT r.object_id, 
       r.snapshot_id, 
       r.metric_id, 
       r.metric_num_value, 
       r.metric_value_index 
FROM   dss_metric_results r 
WHERE  r.metric_value_index IN ( 0, 1, 2 ) 
       AND r.object_id IN ( 3 ) 
       AND r.metric_id IN ( 60013 ) 
       AND r.snapshot_id IN ( 1 )

SELECT r.object_id, 
       r.snapshot_id, 
       r.metric_id, 
       r.metric_num_value, 
       r.metric_value_index 
FROM   dss_metric_results r 
WHERE  r.metric_value_index IN ( 0, 1, 2 ) 
       AND r.object_id IN ( 3 ) 
       AND r.metric_id IN ( 60013 )

In fact, there are 2 tiles showing Robustness in CAST Application Analytics Dashboard (CAST HD), the second tile represent Evolution of robustness like you can see in screenshot bellow:

The first query correspond to the first tile and second query correspond to the second tile.

Notes/comments

Related Pages

Tilte in cmp.json	metric_description in measurement data base
Technical Size	LOCS
Functional Size	OMG-Compliant Automated Function Points
Number of violations to critical quality rules	CRITICAL VIOLATIONS