Report Generator - Information - How to identify SQL query related to a specific block in the report generator

Please follow the below steps -

1. Activate SQL Traces on RestAPI war by following the page - Health Dashboard - Information - How to activate SQL traces on AAD and RestAPI logs

2. Modify the file log4net.config in the directory - <ReportGenerator_Installation_folder> as shown below -

   <root>
       <!-- 
         ALL or DEBUG : Display all messages which are typed DEBUG, INFO, WARN, ERROR or FATAL
         INFO         : Display all messages which are typed INFO, WARN, ERROR or FATAL
         WARN         : Display all messages which are typed WARN, ERROR or FATAL
         ERROR        : Display all messages which are typed ERROR or FATAL
         FATAL        : Display all messages which are typed FATAL
         OFF          : Display no messages
       -->
       <level value="DEBUG" />
       <appender-ref ref="A1" />
       <appender-ref ref="RollingFile" />
   </root>

3. Restart Tomcat
4. Relaunch Report Generator

5. Generate Report
   

   Example

   When you generate report using Template "2-Word-Components-Library.docx" then you will find blocks along with names in the generated report -

   

   You can see that the name of the block is - TOP_RISKIEST_COMPONENTS in this example.

6. Open the log file formed. To find this log path you may refer to the page CAST Management Studio - Information - How to find logs and search for the name of the block for which you wish to get the Requested URL.

    

   log example

   2016-05-16 13:34:44,750 - DEBUG - Cast.Util.Log.LogHelper : Start TableBlock generation : Type TOP_RISKIEST_COMPONENTS
   2016-05-16 13:34:44,901 - DEBUG - Cast.Util.Log.LogHelper : Request URL 'http://localhost:8080/CAST-RESTAPI800/rest/AED/applications/3/snapshots/5/components/60014?nbRows=14' - Time elapsed : 00:00:00.1322673 
   2016-05-16 13:34:44,917 - DEBUG - Cast.Util.Log.LogHelper : End TableBlock generation (TOP_RISKIEST_COMPONENTS) in 167 milliseconds

7. Execute the URI on the RestAPI Interface.

   Executing the URI on Rest API Interface

   In the example shown in this page the URI is  http://localhost:8080/CAST-RESTAPI800/rest/AED/applications/3/snapshots/5/components/60014?nbRows=14
   Connect to RestAPI, put the URI in the text box as shown below and click Submit button
   
   

8. Open the Rest API log files (Example - <tomcat_installation_folder>/Webapps/RestAPI/logs/restapi.log) to get the SQL traces for this URI.
    

   2016-05-17 08:33:18,977| DEBUG | http-bio-8080-exec-2 | org.springframework.jdbc.core.JdbcTemplate | Executing prepared SQL query
   2016-05-17 08:33:19,029| DEBUG | http-bio-8080-exec-2 | org.springframework.jdbc.core.JdbcTemplate | Executing prepared SQL statement [select distinct o.OBJECT_ID as OBJECT_ID, o.OBJECT_FULL_NAME as OBJECT_FULL_NAME, o.OBJECT_NAME as OBJECT_NAME, (case ?  when 60011 then cast(r.TRAN_PROPER as bigint) when 60012 then r.CHAN_PROPER * (1 + cast(r.FAN_IN as bigint)) when 60013 then r.ROBU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60014 then r.PERF_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60016 then r.SECU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) else 0 end) as PRI from DSS_MODULE_LINKS ml join DSS_LINK_INFO li on li.LINK_TYPE_ID = 3 and li.PREVIOUS_OBJECT_ID = ml.MODULE_ID  and li.SNAPSHOT_ID = ml.SNAPSHOT_ID join DSS_OBJECTS o on o.OBJECT_ID = li.NEXT_OBJECT_ID join DSS_OBJECT_RANKING r on r.OBJECT_ID = o.OBJECT_ID  and r.SNAPSHOT_ID = ml.SNAPSHOT_ID where ml.OBJECT_ID = ?  and ml.SNAPSHOT_ID = ?  order by PRI desc, OBJECT_FULL_NAME asc, OBJECT_ID asc limit 14 offset 0]

   
   

 Adapt the query to execute on PGAdmin

select distinct o.OBJECT_ID as OBJECT_ID, o.OBJECT_FULL_NAME as OBJECT_FULL_NAME, o.OBJECT_NAME as OBJECT_NAME, (case ?  when 60011 then cast(r.TRAN_PROPER as bigint) when 60012 then r.CHAN_PROPER * (1 + cast(r.FAN_IN as bigint)) when 60013 then r.ROBU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60014 then r.PERF_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60016 then r.SECU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) else 0 end) as PRI from DSS_MODULE_LINKS ml join DSS_LINK_INFO li on li.LINK_TYPE_ID = 3 and li.PREVIOUS_OBJECT_ID = ml.MODULE_ID  and li.SNAPSHOT_ID = ml.SNAPSHOT_ID join DSS_OBJECTS o on o.OBJECT_ID = li.NEXT_OBJECT_ID join DSS_OBJECT_RANKING r on r.OBJECT_ID = o.OBJECT_ID  and r.SNAPSHOT_ID = ml.SNAPSHOT_ID where ml.OBJECT_ID = ?  and ml.SNAPSHOT_ID = ?  order by PRI desc, OBJECT_FULL_NAME asc, OBJECT_ID asc limit 14 offset 0

Set search_path = sample_central;

select distinct o.OBJECT_ID as OBJECT_ID, o.OBJECT_FULL_NAME as OBJECT_FULL_NAME, o.OBJECT_NAME as OBJECT_NAME, (case 60014 when 60011 then cast(r.TRAN_PROPER as bigint) when 60012 then r.CHAN_PROPER * (1 + cast(r.FAN_IN as bigint)) when 60013 then r.ROBU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60014 then r.PERF_PROPER * (1 + cast(r.HIT_COUNT as bigint)) when 60016 then r.SECU_PROPER * (1 + cast(r.HIT_COUNT as bigint)) else 0 end) as PRI from DSS_MODULE_LINKS ml join DSS_LINK_INFO li on li.LINK_TYPE_ID = 3 and li.PREVIOUS_OBJECT_ID = ml.MODULE_ID  and li.SNAPSHOT_ID = ml.SNAPSHOT_ID join DSS_OBJECTS o on o.OBJECT_ID = li.NEXT_OBJECT_ID join DSS_OBJECT_RANKING r on r.OBJECT_ID = o.OBJECT_ID  and r.SNAPSHOT_ID = ml.SNAPSHOT_ID where ml.OBJECT_ID = 3  and ml.SNAPSHOT_ID = 5  order by PRI desc, OBJECT_FULL_NAME asc, OBJECT_ID asc limit 14 offset 0