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)
Number of Reports 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)
List of Reports by Jira Dashboard
For a list of Dataplane reports used on 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.
For non-Postgres databases:
SELECT
pp.pagename AS dashboard,
pp.id AS dashboard_id,
pp.username AS dashboard_owner,
rr.name AS report,
gup.userprefvalue AS report_id,
rr.owner AS report_owner
FROM
portletconfiguration pc
JOIN
portalpage pp ON pc.portalpage = pp.id
JOIN
gadgetuserpreference gup ON pc.id = gup.portletconfiguration
JOIN
AO_6714C7_SAVED_REPORT_RECORD rr ON rr.guid = gup.userprefvalue
WHERE
pc.gadget_xml LIKE '%dataplane%' AND gup.userprefkey = 'report'
GROUP BY
dashboard, dashboard_id, dashboard_owner, report, report_id, report_owner
ORDER BY
dashboard, report_id DESC;
For Postgres databases:
SELECT
pp.pagename AS dashboard,
pp.id AS dashboard_id,
pp.username AS dashboard_owner,
rr."NAME" AS report,
gup.userprefvalue AS report_id,
rr."OWNER" AS report_owner
FROM
portletconfiguration pc
JOIN
portalpage pp ON pc.portalpage = pp.id
JOIN
gadgetuserpreference gup ON pc.id = gup.portletconfiguration
JOIN
"AO_6714C7_SAVED_REPORT_RECORD" rr ON rr."GUID" = gup.userprefvalue
WHERE
pc.gadget_xml LIKE '%dataplane%' AND gup.userprefkey = 'report'
GROUP BY
dashboard, dashboard_id, dashboard_owner, report, report_id, report_owner
ORDER BY
dashboard, report_id DESC;
Example output:
dashboard | dashboard_id | dashboard_owner | report | report_id | report_owner
--------------------+--------------+-----------------+------------------------------------------------------------------+--------------------------------------+--------------
Beta | 12272 | fred | Development Issues by Assignee | d21c0e60-d137-4ffa-b565-95a3e474d1eb | fred
Beta | 12272 | fred | Denali Backlog and Backlog Additions | 0b727c34-6412-4ccf-b4e4-ac0aabfd8e50 | fred
Client Services | 13070 | admin | Open Support Tickets | 9cc2385e-6fd2-4ca2-b785-f0003dc2ba52 | fred
Client Services | 13070 | admin | Support Tickets by Country | 0968ff9e-829a-4f78-9f85-b23d67594f6a | jeff
Denali Program | 12470 | jeff | Denali Backlog and Backlog Additions | 0b727c34-6412-4ccf-b4e4-ac0aabfd8e50 | fred
Denali Program | 12470 | jeff | Program Status - Burn Up w/ Milestones | b6602394-43d0-4b15-83bb-c52ea169670f | fred
Denali Program | 12470 | jeff | Dev Issues by Priority | 33399578-1f6f-4005-b1ce-54755f023717 | fred
...
Number of Report Gadgets by Confluence Page
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)
