SQL Queries - CAST Central Base - Queries on Metrics - How to calculate the number of bookmarks per object per metric_id

Purpose of Query

The purpose of this query is to calculate the number of bookmarks for a given snapshot for each object and for each metric_id.

Applicable CAST Version

Applicable RDBMS

Query for CSS

Note that snapshot_id has to be provided in the query below. It can be found using this page: SQL Queries - CAST Central Base - Queries on snapshots - How to get the ID and name of a snapshot for a given application

SELECT   dos.object_id       ,
         dos.object_full_Name,
         dmr.metric_id       ,
         CASE
                  WHEN dmr.position_id=0
                  THEN 1
                  ELSE COUNT(DISTINCT dcb.local_position_id)
         END AS "bookmark count"
FROM     dss_objects dos       ,
         dss_metric_results dmr,
         dss_code_bookmarks dcb
WHERE    dos.object_id  =dmr.object_id
AND      dcb.object_id  =dos.object_id
AND      dmr.snapshot_id=<snapshot_id>
AND
         (
                  dcb.position_id=dmr.position_id
         OR       dmr.position_id=0
         )
GROUP BY dos.object_id       ,
         dos.object_full_Name,
         dmr.position_id     ,
         dmr.metric_id

Query result example

916;“org.owasp.webgoat.session.CreateDB.createTransactionTable”;8105;1

 916;“org.owasp.webgoat.session.CreateDB.createTransactionTable”;8113;3

Query result interpretation

The result will provide the object_Id followed by the object full name and then the metric_id and the count of bookmarks.

In the above example this will indicate that for the createTransactionTable object there is one bookmark or there is no bookmark for the metric_id 8105 and three bookmarks for the metric_id 8113.

Query for Oracle

Enter the SQL query

Query result example

Query result interpretation

Query for SQL server

Enter the SQL query

Query result example

Query result interpretation

Notes/comments

Related Pages