For a count of Dataplane reports created by Jira user, run the following Jira database query.
For non-Postgres 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;
For Postgres 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;
Example 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)
Reports Created by Report Type
For a count of Dataplane reports created by type of report, run the following Jira database query.
For non-Postgres databases:
SELECT
REPLACE(REPORT_KEY, 'reportKey.', '') as report_type,
COUNT(1) as saved_reports
FROM
AO_6714C7_SAVED_REPORT_RECORD
GROUP BY
report_type
ORDER BY
saved_reports DESC;
For Postgres databases:
SELECT
REPLACE("REPORT_KEY", 'reportKey.', '') as report_type,
COUNT(1) as saved_reports
FROM
"AO_6714C7_SAVED_REPORT_RECORD"
GROUP BY
report_type
ORDER BY
saved_reports DESC;
For a count of Dataplane report gadgets added to Jira dashboards, run the following Jira database query.
Note: this query does not capture how often the reports are run, which is dependent on how widely the dashboards are shared, favorited and visited by Jira users.
SELECT
pp.username AS dashboard_owner,
pp.pagename AS dashboard,
pp.id AS dashboard_id,
COUNT(pc.portalpage) AS dataplane_gadgets
FROM
portletconfiguration pc
LEFT JOIN
portalpage pp ON pc.portalpage = pp.id
WHERE
pc.gadget_xml LIKE '%dataplane%'
GROUP BY
dashboard_owner, dashboard, dashboard_id
ORDER BY
dashboard_owner, dataplane_gadgets DESC;
Example output:
dashboard_owner | dashboard | dashboard_id | dataplane_gadgets
------------------+--------------------+--------------+-------------------
u1234 | Support Team | 12371 | 1
u7241 | Test | 13070 | 6
u7241 | Development | 12870 | 6
u7241 | Main Dashboard | 13170 | 2
u5817 | Test | 13270 | 2
u9112 | Development | 12271 | 9
u9112 | External Support | 12470 | 5
u9112 | Sales | 12273 | 3
u9112 | Support | 12270 | 3
u9112 | Personal | 12471 | 2
u9112 | Beta | 12272 | 2
(11 rows)
Report Gadgets on Confluence Pages
For a count of Dataplane report gadgets added to Confluence pages, run the following Confluence database query.
Note: this query does not capture how often the reports are run, which is dependent on how often the pages are visited by Confluence users.
SELECT
s.spacekey AS space,
c.title AS page,
c.contentid,
ROUND((LENGTH( bc.body) - LENGTH(REPLACE(bc.body, 'arsenaleDataplaneGadget', ''))) / LENGTH('arsenaleDataplaneGadget')) AS dataplane_gadgets
FROM
CONTENT c
LEFT JOIN
BODYCONTENT bc ON c.contentid = bc.contentid
LEFT JOIN
SPACES s ON c.spaceid = s.spaceid
WHERE
c.prevver IS NULL
AND c.contenttype IN ('PAGE')
AND bc.body LIKE '%arsenaleDataplaneGadget%'
ORDER BY
space, dataplane_gadgets DESC;
Example output:
+-------+-------------+-----------+-------------------+
| space | page | contentid | dataplane_gadgets |
+-------+-------------+-----------+-------------------+
| TEST | Test Home 2 | 54821015 | 5 |
| TEST | Test Home | 46432275 | 3 |
+-------+-------------+-----------+-------------------+
2 rows in set (0.05 sec)
