Page tree
Skip to end of metadata
Go to start of metadata
Purpose
This page describes how to identify SQL query related to a specific block in the report generator after activating SQL traces on  RestAPI logs and how to adapt it to execute it on pgadmin.
Applicable in CAST Version


Release
Yes/No
8.3.x(tick)
Applicable RDBMS

 

RDBMS
Yes/No
CSS 2(tick)
CSS 3(tick)
Details

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

Example

Here in the below obtained query we can see that there are three question marks ?

  1. case ?
  2. ml.OBJECT_ID = ?
  3. ml.SNAPSHOT_ID = ?  
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

Substitute the three question marks with the values from the RestAPI URI. Here in this example the URI is - AED/applications/3/snapshots/5/components/60014?nbRows=14, hence the values to substitute will be -

  1. case 60014
  2. ml.OBJECT_ID = 3

  3. ml.SNAPSHOT_ID = 5

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

Result -


Notes/comments


Related Pages




  • No labels