CAST Engineering Dashboard - Export All - Missing data in generated CSV - Incomplete export all from the Action Plan

Purpose (problem description)

This page troubleshoot the problem of missing rows from Action Plan view. For example consider the below case where there are a total of 5492 items with  still violation- 2025 &  corrected - 2394 . When you click on export all you can find only 1980 items under still a violation and 2329 under corrected.

You may also obseve variable number of rows exported  each time you click on “export all” with the last row is incomplete.

Observed in CAST AIP

ReleaseYes/No
8.3.x✅

Observed on RDBMS

RDBMSYes/No
CSS✅

Step by Step Scenario

  1. Launch Dashboard
  2. Goto Action Plan View
  3. Click on Export All button

Action plan

  1. If you are in 7.0.7, Check for errors in the tomcat log

    1. Activate SQL traces and check tomcat log, check if you have the following error message :

      [ERROR]:   stream error : null

      This issue happens once and we were not able to reproduce this issue at our side  with the Central base and the war

      As a workaround, you can use the below query to get the list of objects from the action plan list instead of using the “Export all” button :

select distinct (select dso.OBJECT_FULL_NAME from <CENTRAL_BASE>.DSS_OBJECTS dso where dso.OBJECT_ID = action.F_OBJECT_ID),
action.F_OBJECT_ID,
action.F_METRIC_ID,
/=> Technical Criterion ID action.F_TC_METRIC_ID,
<=/ case coalesce( (select distinct 1 from <CENTRAL_BASE>.DSS_METRIC_RESULTS where METRIC_ID = action.F_DETAIL_METRIC_ID and OBJECT_ID = action.F_OBJECT_ID and SNAPSHOT_ID = <SNAPSHOT_ID>) , 0) when 1 then coalesce( (select distinct 2 from <CENTRAL_BASE>.VIEWER_ACTION_PLANS /* N/A (same snapshot) /
where FIRST_SNAPSHOT_ID=<SNAPSHOT_ID> and METRIC_ID=action.F_METRIC_ID and OBJECT_ID=action.F_OBJECT_ID ) , 1) / Still a violation /
when 0 then 3 / Corrected violation / end as F_ACTION_STATUS,
‘550e8400e29b41d’ as F_IGNORED,
action.F_ACTION,
action.F_DATE,
action.F_PRIORITY,
action.F_USER,
(select d.METRIC_DESCRIPTION from <CENTRAL_BASE>.DSS_METRIC_DESCRIPTIONS d where d.METRIC_ID = action.F_METRIC_ID and d.DESCRIPTION_TYPE_ID = 0 and d.LANGUAGE = ‘ENGLISH’), / nom de la QR /
/=> Technical Criterion Name (select d.METRIC_DESCRIPTION from <CENTRAL_BASE>.DSS_METRIC_DESCRIPTIONS d where d.METRIC_ID = action.F_TC_METRIC_ID and d.DESCRIPTION_TYPE_ID = 0 and d.LANGUAGE = ‘ENGLISH’), <=/ / Technical criterion name /
action.F_METRIC_GROUP / metric group of violation /
from ( / Part 1 is for Distributions / select ap.OBJECT_ID as F_OBJECT_ID, / object selected for action / ap.METRIC_ID as F_METRIC_ID, / distribution id / mtt.METRIC_PARENT_ID as F_TC_METRIC_ID, / parent Technical Criterion id of distribution (may be more than one) /
ap.METRIC_ID as F_DETAIL_METRIC_ID, / distribution id /
ap.ACTION_DEF as F_ACTION, / action comment /
ap.SEL_DATE as F_DATE, / date of selection /
ap.PRIORITY as F_PRIORITY, / action priority /
ap.USER_NAME as F_USER, / user having selected object /
5 as F_METRIC_GROUP / metric group for Distributions / from <CENTRAL_BASE>.VIEWER_ACTION_PLANS ap, <CENTRAL_BASE>.DSS_METRIC_TYPE_TREES mtt, (select distinct l.NEXT_OBJECT_ID,
ml.OBJECT_ID / useful ? /
from <CENTRAL_BASE>.DSS_LINK_INFO l,
<CENTRAL_BASE>.DSS_LINK_INFO l2,
<CENTRAL_BASE>.DSS_MODULE_LINKS ml
where l.PREVIOUS_OBJECT_ID = ml.MODULE_ID
and l.LINK_TYPE_ID = 3
and l.SNAPSHOT_ID = <SNAPSHOT_ID>
and ml.OBJECT_TYPE_ID = 20000
and ml.SNAPSHOT_ID = <SNAPSHOT_ID>
and ml.OBJECT_ID in (select OBJECT_ID from <CENTRAL_BASE>.DSS_OBJECTS) / user must see the module /
and l2.NEXT_OBJECT_ID = ml.OBJECT_ID
and l2.PREVIOUS_OBJECT_ID = <OBJECT_ID>
and l2.LINK_TYPE_ID = 1
and l2.SNAPSHOT_ID = <SNAPSHOT_ID> ) theModule,
<CENTRAL_BASE>.DSS_SNAPSHOTS firstsnap,
<CENTRAL_BASE>.DSS_SNAPSHOTS cursnap where ap.METRIC_ID = mtt.METRIC_ID
and firstsnap.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID
and cursnap.SNAPSHOT_ID = <SNAPSHOT_ID>
and cursnap.FUNCTIONAL_DATE >= firstsnap.FUNCTIONAL_DATE
and ap.LAST_SNAPSHOT_ID = 1000000000
and ap.METRIC_ID in (select METRIC_ID from <CENTRAL_BASE>.DSS_METRIC_TYPES where METRIC_GROUP = 5)
and ap.OBJECT_ID + 0 = theModule.NEXT_OBJECT_ID
union all
/ Part 2 is for Quality Rules / select ap.OBJECT_ID as F_OBJECT_ID,
ap.METRIC_ID as F_METRIC_ID,
mtt.METRIC_PARENT_ID as F_TC_METRIC_ID,
(select DMTT.METRIC_ID from <CENTRAL_BASE>.DSS_METRIC_TYPE_TREES DMTT where DMTT.METRIC_PARENT_ID = ap.METRIC_ID) as F_DETAIL_METRIC_ID,
ap.ACTION_DEF as F_ACTION,
ap.SEL_DATE as F_DATE,
ap.PRIORITY as F_PRIORITY,
ap.USER_NAME as F_USER,
1 as F_METRIC_GROUP / metric group for Quality Rules / from <CENTRAL_BASE>.VIEWER_ACTION_PLANS ap,
<CENTRAL_BASE>.DSS_METRIC_TYPE_TREES mtt,
(select distinct l.NEXT_OBJECT_ID,
ml.OBJECT_ID
from <CENTRAL_BASE>.DSS_LINK_INFO l,
<CENTRAL_BASE>.DSS_LINK_INFO l2,
<CENTRAL_BASE>.DSS_MODULE_LINKS ml
where l.PREVIOUS_OBJECT_ID = ml.MODULE_ID
and l.LINK_TYPE_ID = 3
and l.SNAPSHOT_ID = <SNAPSHOT_ID>
and ml.OBJECT_TYPE_ID = 20000
and ml.SNAPSHOT_ID = <SNAPSHOT_ID>
and ml.OBJECT_ID in (select OBJECT_ID from <CENTRAL_BASE>.DSS_OBJECTS) / user must see the module */
and l2.NEXT_OBJECT_ID = ml.OBJECT_ID
and l2.PREVIOUS_OBJECT_ID = <OBJECT_ID>
and l2.LINK_TYPE_ID = 1
and l2.SNAPSHOT_ID = <SNAPSHOT_ID> ) theModule,
<CENTRAL_BASE>.DSS_SNAPSHOTS firstsnap,
<CENTRAL_BASE>.DSS_SNAPSHOTS cursnap where ap.METRIC_ID = mtt.METRIC_ID
and firstsnap.SNAPSHOT_ID = ap.FIRST_SNAPSHOT_ID
and cursnap.SNAPSHOT_ID = <SNAPSHOT_ID>
and cursnap.FUNCTIONAL_DATE >= firstsnap.FUNCTIONAL_DATE
and ap.LAST_SNAPSHOT_ID = 1000000000
and ap.METRIC_ID in (select METRIC_ID from <CENTRAL_BASE>.DSS_METRIC_TYPES where METRIC_GROUP = 1)
and ap.OBJECT_ID + 0 = theModule.NEXT_OBJECT_ID ) action order by 9 asc

   2. In the above query replace the <CENTRAL\_BASE>,<SNAPSHOT\_ID> with the name of central repository and snpshotID respectively & replace the <OBJECT\_ID> with the value retrieved using the below query :

      ```java
Select OBJECT_ID from <CENTRAL_BASE>.dss_objects where object_type_id = -102 and object_name = '<MODULE_NAME>'
  1. If you are not having any error or if you are in different version, check if it is a corruption of VIEWER_ACTION_PLANS table

    1. The reason for this is could be that the VIEWER_ACTION_PLANS table is be corrupt. You can find duplicated rows in this table, hence in the dashboard the count shows a greater value. But the exported data is correct, as the duplicates will not be exported from dashboard.

      To confirm the existence of duplicates, run below query

select ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF, count(1) from VIEWER_ACTION_PLANS ap group by ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF having count(1) > 1 order by ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF

   2. Run the below script on the Central Base to remove the duplicates

      ```sql
update VIEWER_ACTION_PLANS  
set METRIC_ID = -METRIC_ID;

insert into VIEWER_ACTION_PLANS  (METRIC_ID, OBJECT_ID, FIRST_SNAPSHOT_DATE, LAST_SNAPSHOT_DATE, SEL_DATE, PRIORITY, ACTION_DEF)
select distinct -ap.METRIC_ID, ap.OBJECT_ID, ap.FIRST_SNAPSHOT_DATE, ap.LAST_SNAPSHOT_DATE, ap.SEL_DATE, ap.PRIORITY, ap.ACTION_DEF
from VIEWER_ACTION_PLANS  ap;

delete from VIEWER_ACTION_PLANS 
where METRIC_ID < 0;

   After running the script when you reload the page you can see the correct numbers for total,still violation & corrected items on the Action Plan Views
  1. If it is not a corruption of VIEWER_ACTION_PLANS table, contact CAST support with Relevant input

Relevant  input

  1. Sherlock output
  2. Tomcat logs with SQL traces
  3. Screenshot showing the number of rows in the dashboard
  4. Export file with less rows than expected

Notes/comments

Related Pages