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

Applicable RDBMS

Details

0 - Specifications related to ADD log with SQL traces activated

1. 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.

   ```sql
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

2. 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

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

2. 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".

1. From measurement database, get metric id of the quality indicator, for this run the following sql query on your measurement database:

   ```sql
SELECT metric_id
       metric_description 
FROM   dss_metric_descriptions 
WHERE  upper (metric_description) like 'Id of quality indicator  of our tile'

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. 2. 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