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