Usage statistics using SQL

In the Administration

Requirement Yogi has a few usage metrics in the administration:

However, the API of Confluence doesn’t allow us to perform proper aggregations. So, if you are a database administrator, you may want to understand how the data is stored, to be able to extract information yourself.

Please check out this tutorial for a quick overview: https://youtu.be/ruOAucnSxno?si=07gnqio1jHgeibZu&t=242

Disclaimer

Direct SQL access is not supported

Accessing the database directly is neither supported nor covered by our agreement, since:

Any upgrade may require that you change the way to extract data,
You may extract wrong data,
SQL is hard and you may make mistakes that erase or corrupt data.

Accessing the Requirement Yogi database in Confluence

Prefix: AO_32F7CE

All our tables are stored with the prefix “AO_32F7CE” (“AO_42D07A” in Jira, “AO_B895AB” for PSEA, it’s easy, it is the md5 of the plugin key).

Tip: Most databases assume that table names are lowercase, even when you type SELECT SPACEKEY, KEY, BASELINE FROM AO_32F7CE_DBREQUIREMENT. So you have to put double-quotes ("), or backticks in weird databases (`) : SELECT "SPACEKEY", "KEY", "BASELINE" FROM "AO_32F7CE_DBREQUIREMENT".

Table AO_32F7CE_DBREQUIREMENT:

ID: Technical primary key
SPACEKEY*: The space key
KEY*: The key of the requirement
BASELINE*: The baseline (for ARCHIVED requirements only), or null (for ACTIVE only).
STATUS: ACTIVE, ARCHIVED, MOVED, DELETED.

*Primary key

Usual queries

Get the count of requirements by space and status:

select "SPACEKEY",
       SUM(CASE "STATUS" WHEN 'ACTIVE' THEN 1 END) "COUNT_CURRENT",
       SUM(CASE "STATUS" WHEN 'ARCHIVED' THEN 1 END) "COUNT_BASELINED",
       SUM(CASE "STATUS" WHEN 'DELETED' THEN 1 END) "COUNT_DELETED"
from "AO_32F7CE_DBREQUIREMENT"
group by "SPACEKEY"
order by "COUNT_CURRENT" DESC
;

We’ll write more queries here, as customers ask.

Database schema

Please go to Database schema for details about the SQL schema.