Purpose of Query
The purpose of this query is to retrieve the transactions shown in the dashboard TWRI view (FRAME_PORTAL_TRANSACTION_VIEW - Risk Indicators - Transaction Level).
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS2 |
Query for CSS
For CAST version 8.3.16 or higher use this query (in 8.3.16 the link information was moved from the DSS_LINK_INFO table to the FP_LINK_INFO table):
SELECT o.OBJECT_FULL_NAME , o.OBJECT_ID AS TRANSACTION_ID, t.RANKING FROM (SELECT o.OBJECT_ID, o.OBJECT_FULL_NAME FROM DSS_OBJECTS o WHERE o.OBJECT_TYPE_ID = 30002 ) AS o JOIN (SELECT DISTINCT l.PREVIOUS_OBJECT_ID FROM FP_LINK_INFO l WHERE l.LINK_TYPE_ID = 11004 /* Link on table must exists */ AND l.SNAPSHOT_ID = <snapshot_id> ) AS l ON l.PREVIOUS_OBJECT_ID = o.OBJECT_ID JOIN ( SELECT transaction_detail.PREVIOUS_OBJECT_ID, SUM(r.VIOLATION_INDEX) AS RANKING FROM (SELECT DISTINCT r.OBJECT_ID, r.VIOLATION_INDEX FROM DSS_SNAPSHOT_RANKING r WHERE r.SNAPSHOT_ID = <snapshot_id> AND r.BUSINESS_CRITERION_ID = 60013 ) AS r JOIN (SELECT DISTINCT transaction_detail.NEXT_OBJECT_ID, transaction_detail.PREVIOUS_OBJECT_ID FROM FP_LINK_INFO AS transaction_detail WHERE transaction_detail.SNAPSHOT_ID = <snapshot_id> AND transaction_detail.LINK_TYPE_ID BETWEEN 11002 AND 11005 ) AS transaction_detail ON r.OBJECT_ID = transaction_detail.NEXT_OBJECT_ID GROUP BY transaction_detail.PREVIOUS_OBJECT_ID ) t ON t.PREVIOUS_OBJECT_ID = o.OBJECT_ID ORDER BY 3 DESC
For CAST version 8.3.15 or lower use this query:
SELECT o.OBJECT_FULL_NAME , o.OBJECT_ID AS TRANSACTION_ID, t.RANKING FROM (SELECT o.OBJECT_ID, o.OBJECT_FULL_NAME FROM DSS_OBJECTS o WHERE o.OBJECT_TYPE_ID = 30002 ) AS o JOIN (SELECT DISTINCT l.PREVIOUS_OBJECT_ID FROM DSS_LINK_INFO l WHERE l.LINK_TYPE_ID = 11004 /* Link on table must exists */ AND l.SNAPSHOT_ID = <snapshot_id> ) AS l ON l.PREVIOUS_OBJECT_ID = o.OBJECT_ID JOIN ( SELECT transaction_detail.PREVIOUS_OBJECT_ID, SUM(r.VIOLATION_INDEX) AS RANKING FROM (SELECT DISTINCT r.OBJECT_ID, r.VIOLATION_INDEX FROM DSS_SNAPSHOT_RANKING r WHERE r.SNAPSHOT_ID = <snapshot_id> AND r.BUSINESS_CRITERION_ID = 60013 ) AS r JOIN (SELECT DISTINCT transaction_detail.NEXT_OBJECT_ID, transaction_detail.PREVIOUS_OBJECT_ID FROM DSS_LINK_INFO AS transaction_detail WHERE transaction_detail.SNAPSHOT_ID = <snapshot_id> AND transaction_detail.LINK_TYPE_ID BETWEEN 11002 AND 11005 ) AS transaction_detail ON r.OBJECT_ID = transaction_detail.NEXT_OBJECT_ID GROUP BY transaction_detail.PREVIOUS_OBJECT_ID ) t ON t.PREVIOUS_OBJECT_ID = o.OBJECT_ID ORDER BY 3 DESC
Query for Oracle
Same as Above
Query for SQL server
Same As above
Notes/comments
The appropriate snapshot_id can be obtained by running on the central the query:
select * from dss_snapshots
Related Pages