Applicable in CAST Version

Release	Yes/No
8.2.x
8.1.x
8.0.x
7.3.x
7.2.x
7.0.x

Introduction

This page allows to identify the query providing data in the AD Dashboard page. The aim of identifying this query is to qualify issue of missing or wrong data displayed in the dashboard page.

The missing or wrong data is used in different sections/graphs/tables showned in the dashboard page.

Action Plan

Update parameters in the web.xml file
Restart the application in Tomcat
Connect to the dashboard
Display the page having issue
Investigate the SQL traces

Update parameters in the web.xml file

Edit the web.xml file located under WAR deployment folder as %Apache Software Foundation\Tomcat 5.0\webapps\CASTAD702\WEB-INF

set following parameters to 0

<context-param>
		<param-name>logger.level.SQL</param-name>
		<param-value>0</param-value>
		<description>SQL log level</description>
	</context-param>
	<context-param>
		<param-name>logger.level.ROWREADER</param-name>
		<param-value>0</param-value>
		<description>ROWREADER log level</description>
	</context-param>

set following parameters to true

<context-param>
		<param-name>logger.output.file</param-name>
		<param-value>true</param-value>
		<description>Outputs logs to file</description>
	</context-param>
	<context-param>
		<param-name>logger.output.buffer</param-name>
		<param-value>true</param-value>
		<description>Outputs logs to internal buffer</description>
	</context-param>
	<context-param>
		<param-name>logger.output.console</param-name>
		<param-value>true</param-value>
		<description>Outputs logs to JVM console</description>
	</context-param>

check the location where the CAST_<date>.log files are generated; by default, they are located in the 'castweb' sub folder of the user's CAST directory, otherwise, the location is specified by the following parameter:
```
<context-param>
	<param-name>cast.web.home</param-name>
	<param-value>E:/MyADGLogDir</param-value>
	<description>the path to home repository of cast web</description>
</context-param> 
```

Investigate the SQL traces

In the bottom of the Dashboard page, you will see the used SQL queries to dispaly data in the dahbaord page.
In the dahabord page, some section are not expanded. When expanding these sections to see their content, the used queries are not displayed in the same dahbaord page. The queries are displayed in the tomcat log file C:\Program Files\Apache Software Foundation\Tomcat 6.0\logs\stdout_<date>.log as :
- Top 10 Violations
- Violated Rules
- List of Very High Risk Objects
- ....

The SQL trace in the dahbaord page or in the tocamt log file will be like followig ones

DETAIL]: [SQL] select UPDATE_DATE, CACHE_NAME from fan_out_env_central.VIEWER_CACHE_UPDATE where CACHE_NAME = 'all'
[DETAIL]: [SQL] select UPDATE_DATE, CACHE_NAME from fan_out_env_central.VIEWER_CACHE_UPDATE where CACHE_NAME = 'all'
[INFO]: [ROWREADER] Read: RETRIEVER_IS_MOD_OR_APP
[DETAIL]: [SQL] PREPARE select 0, 'ERROR_CORPO_OR_SYS' from fan_out_env_central.VIEWER_DUAL where ltrim(to_char('51')) not like ('%'||'object'|| '%') and not exists ( select 1 from fan_out_env_central.DSS_PORTF_TREE portf where ltrim(to_char(portf.MODULE_ID)) = ltrim(to_char('51')) or ltrim(to_char(portf.APP_ID)) = ltrim(to_char('51')) ) union all select 1, 'ERROR_CORPO_OR_SYS' from fan_out_env_central.VIEWER_DUAL where ltrim(to_char('51')) like ('%'||'object'|| '%')
[DETAIL]: [SQL] EXECUTE
[DETAIL]: [SQL] Query Time < 0 ms
[INFO]: [ROWREADER] Read: RETRIEVER_RULES_TOP_LEVEL_OBJECT
[DETAIL]: [SQL] PREPARE select count(*), 'ERROR_TOP_LEVEL_OBJECT' from fan_out_env_central.DSS_OBJECTS o, fan_out_env_central.DSS_OBJECT_TYPES t where o.OBJECT_ID=51 and o.OBJECT_TYPE_ID=t.OBJECT_TYPE_ID and t.OBJECT_GROUP in (3,4)
[DETAIL]: [SQL] EXECUTE
[DETAIL]: [SQL] Query Time < 1 ms
[INFO]: [ROWREADER] Read: RETRIEVER_RULES_TOP_LEVEL_VALUE
[DETAIL]: [SQL] PREPARE select count(*), 'ERROR_TOP_LEVEL_VALUE' from fan_out_env_central.DSS_OBJECTS o, fan_out_env_central.DSS_METRIC_RESULTS r, fan_out_env_central.DSS_METRIC_TYPES m where o.OBJECT_ID=51 and o.OBJECT_ID=r.OBJECT_ID and r.METRIC_ID=m.METRIC_ID and r.METRIC_VALUE_INDEX=0 and m.METRIC_GROUP=10 and r.METRIC_NUM_VALUE >=1 and r.METRIC_NUM_VALUE <=4
[DETAIL]: [SQL] EXECUTE
[DETAIL]: [SQL] Query Time < 0 ms
[INFO]: [ROWREADER] Read: RETRIEVER_FRAME_INIT_ALLSNAPSHOT
[DETAIL]: [SQL] PREPARE select SNAPSHOT_ID from fan_out_env_central.DSS_SNAPSHOTS order by 1
[DETAIL]: [SQL] EXECUTE
[DETAIL]: [SQL] Query Time < 0 ms
[INFO]: [ROWREADER] Read: RETRIEVER_FRAME_PORTAL_TOP_LEVEL_DETAILS_1
[DETAIL]: [SQL] PREPARE select distinct 'F_OBJECTYPE', OBJECT_TYPE_NAME from fan_out_env_central.DSS_OBJECTS O, fan_out_env_central.DSS_OBJECT_TYPES T where T.OBJECT_TYPE_ID = O.OBJECT_TYPE_ID and O.OBJECT_ID = 51
[DETAIL]: [SQL] EXECUTE
[DETAIL]: [SQL] Query Time < 12 ms
....

The trace contains inforamtion about

ROWREADER name as '[INFO]: [ROWREADER] Read: RETRIEVER_RULES_TOP_LEVEL_OBJECT'
The queries
Query Time= Query execution Time

The ROWREADER name can help to identify queries involved in a given section of the dashboard page.

For the List of Very High Risk Objects, the used queries are given in the [ROWREADER] Read: RETRIEVER_METRIC_VALUE_BODY_GRADE_DATA_LIST_NOCOMPARE

[INFO]:    [ROWREADER] Read: RETRIEVER_METRIC_VALUE_BODY_GRADE_DATA_LIST_NOCOMPARE
[DETAIL]:  [SQL] drop table if exists temp_object_access
[DETAIL]:  [SQL] Query Time < 62 ms
[DETAIL]:  [SQL] create local temporary table temp_object_access(OBJECT_ID int,ACCESS_EXCEPT int,ACCESS_ACTION int)
[DETAIL]:  [SQL] Query Time < 329 ms
[DETAIL]:  [SQL] insert into temp_object_access  select a.OBJECT_ID, a.ACCESS_EXCEPT, a.ACCESS_ACTION from fan_out_env_central.VIEWER_USERS u, fan_out_env_central.VIEWER_USER_ACCESS a where u.ID = '1' and u.ADMINISTRATOR=0 and a.USER_ID = u.ID and a.OBJECT_CATEGORY = 1 and a.ACCESS_LIST = 1 union all select -1,1,1 from fan_out_env_central.VIEWER_USERS u where u.ID = '1' and u.ADMINISTRATOR=1
[DETAIL]:  [SQL] Query Time < 15 ms
[DETAIL]:  [SQL] PREPARE select distinct DSO.OBJECT_FULL_NAME as F_OBJECT_NAME, DMR.OBJECT_ID as F_OBJECT_ID, DMR.METRIC_NUM_VALUE as F_VALUE_NUM, DMR.METRIC_CHAR_VALUE as F_VALUE_CHAR, DMR.METRIC_OBJECT_ID as F_VALUE_OBJECT,  coalesce(  (select 1  from fan_out_env_central.VIEWER_DUAL where exists (select 1 from fan_out_env_central.DSS_METRIC_RESULTS DMR4 where DMR4.METRIC_ID = 2777581 and DMR4.OBJECT_ID  = DMR.OBJECT_ID and DMR4.METRIC_OBJECT_ID = DMR.METRIC_OBJECT_ID and DMR4.SNAPSHOT_ID  = 1)), -1)  as F_NEW, 0 as F_UPDATED, 2777581  as F_CUR_METRIC_ID, coalesce( ( select  OE.JUSTIFY from  fan_out_env_central.DSS_OBJECT_EXCEPTIONS OE where  OE.OBJECT_ID  = DMR.OBJECT_ID  and OE.METRIC_ID = 2777580  and 2 < OE.LAST_SNAPSHOT_ID ) ,'NA') as F_IGNORED, coalesce( (  select ap.ACTION_DEF  from fan_out_env_central.VIEWER_ACTION_PLANS ap  where ap.OBJECT_ID = DMR.OBJECT_ID and ap.METRIC_ID = 2777580 and '201102100000' >= (select dds.TEXT_DATE from fan_out_env_central.DSS_DATE_SNAPSHOT dds where dds.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID and dds.IS_COMPUTED = 1) and ap.LAST_SNAPSHOT_ID = 1000000000 ) ,'NA') as F_ACTION, coalesce(  ( select  ap.PRIORITY from  fan_out_env_central.VIEWER_ACTION_PLANS ap where  ap.OBJECT_ID = DMR.OBJECT_ID  and ap.METRIC_ID = 2777580  and '201102100000' >= (select dds.TEXT_DATE from fan_out_env_central.DSS_DATE_SNAPSHOT dds where dds.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID and dds.IS_COMPUTED = 1)  and ap.LAST_SNAPSHOT_ID = 1000000000 ) ,2) as F_PRIORITY, (select DMV.METRIC_VALUE_TYPE from fan_out_env_central.DSS_METRIC_VALUE_TYPES DMV where DMV.METRIC_ID= 2777581) as F_METRIC_VALUE_TYPE, (select DSO2.OBJECT_NAME from fan_out_env_central.DSS_OBJECTS DSO2 where DSO2.OBJECT_ID = dmlnk.OBJECT_ID) as F_MODULE_NAME, objacces.ACCESS_ACTION as F_ALLOW_ACTION, objacces.ACCESS_EXCEPT as F_ALLOW_EXCEPT, coalesce((select 1 from fan_out_env_central.TypCat where IdCatParent = 138084 and IdTyp = DSO.OBJECT_TYPE_ID),0) as F_CODEVIEWABLE, DMR.POSITION_ID as F_POSITION_ID  from  fan_out_env_central.DSS_OBJECTS DSO  join fan_out_env_central.DSS_METRIC_RESULTS DMR  on  ( DSO.OBJECT_ID = DMR.OBJECT_ID and DSO.OBJECT_TYPE_ID in (select DOT.OBJECT_TYPE_ID from fan_out_env_central.DSS_OBJECT_TYPES DOT where DOT.OBJECT_GROUP != 1) and DMR.METRIC_ID = 2777581 and DMR.SNAPSHOT_ID = 2  )  join fan_out_env_central.DSS_LINK_INFO DL  on  ( DL.LINK_TYPE_ID = 3 and DL.NEXT_OBJECT_ID = DMR.OBJECT_ID and DL.SNAPSHOT_ID = 2  )  join fan_out_env_central.DSS_MODULE_LINKS ML on  ( ML.MODULE_ID  = DL.PREVIOUS_OBJECT_ID and ML.OBJECT_ID  = 51 and ML.SNAPSHOT_ID  = 2  )  join fan_out_env_central.DSS_LINK_INFO lnkinfo  on  ( lnkinfo.NEXT_OBJECT_ID = DSO.OBJECT_ID and lnkinfo.LINK_TYPE_ID  = 3 and lnkinfo.SNAPSHOT_ID  = 2  )  join fan_out_env_central.DSS_MODULE_LINKS dmlnk  on  ( /* authorization to access to detailed list of objects ? */ lnkinfo.PREVIOUS_OBJECT_ID = dmlnk.MODULE_ID and dmlnk.OBJECT_TYPE_ID  = 20000 and dmlnk.SNAPSHOT_ID = 2  )  join TEMP_OBJECT_ACCESS objacces  on  ( objacces.OBJECT_ID in (dmlnk.OBJECT_ID,-1)  ) /* do not display module names that is not the selected module nor is part of the selected application. */ join fan_out_env_central.DSS_FUNC_MODULE_LINKS ML2 on (dmlnk.OBJECT_ID in (ML2.MODULE_ID) and ML2.OBJECT_ID = 51 and ML2.SNAPSHOT_ID = 2) where exists (select 1 from fan_out_env_central.DSS_LINK_INFO li where li.PREVIOUS_OBJECT_ID = 51 and li.LINK_TYPE_ID = 1 and li.SNAPSHOT_ID = 2 and li.NEXT_OBJECT_ID = dmlnk.OBJECT_ID) order by 3 DESC LIMIT 10000
[DETAIL]:  [SQL] EXECUTE
[DETAIL]:  [SQL] Query Time < 532 ms

After identifying the query, you should investigate its different statements to determine the reason of missing or wrong data displayed in the dashbaord page

Page tree

CAST Engineering Dashboard - Information - How to Identify query leading to missing or wrong data and graph in the AD Dashboard page

Introduction

Action Plan

Update parameters in the web.xml file

Investigate the SQL traces