Database query to find the count of Dataplane reports created by a Jira user

This article provides the database query to find the number of dataplane reports created by Jira users.

 Instructions

1. Login to the Jira instance database.
   Consulting with the database administrator for guidance is recommended if one requires information about the database.

2. Run the following Jira database query for a count of Dataplane reports created by a Jira user.

3. For non-PostgreSQL databases:

   SELECT au.lower_user_name as owner_username, cu.display_name as owner_name, COUNT(1) as saved_reports FROM AO_6714C7_SAVED_REPORT_RECORD srr LEFT JOIN app_user au on au.user_key = srr.owner LEFT JOIN cwd_user cu on cu.lower_user_name = au.lower_user_name GROUP BY owner_username, owner_name ORDER BY saved_reports DESC;

    

4. For PostgreSQL databases:

   SELECT au.lower_user_name as owner_username, cu.display_name as owner_name, count(1) as saved_reports FROM "AO_6714C7_SAVED_REPORT_RECORD" srr LEFT JOIN app_user au on au.user_key = srr."OWNER" LEFT JOIN cwd_user cu on cu.lower_user_name = au.lower_user_name GROUP BY owner_username, owner_name ORDER BY saved_reports DESC;

5. Sample output:

   +----------------+------------------+---------------+ | owner_username | owner_name | saved_reports | +----------------+------------------+---------------+ | u1234 | Alex Hamilton | 183 | | u5817 | Francis Karofsky | 74 | | u9112 | Fred Smith | 8 | | u1234 | Phillip Jones | 1 | +----------------+------------------+---------------+ 4 rows in set (0.00 sec)

6. Sample screenshot from a third-party DB tool, DbVisualizer tool:

   Open