Reports Created by Jira User

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;

Example output:

           report_type            | saved_reports 
----------------------------------+---------------
 currentIssueValuesReport         |          5378
 issuesByDateReport               |          4871
 currentStatSumReport             |          2053
 pivotTableReport                 |          2049
 historicalSnapshotReport         |          1797
 issuesTableReport                |          1793
 projectLinksReport               |          1153
 timeFromStatusIndirectToStatus   |           899
 statSumByDateReport              |           896
 closureTimeReport                |           640
 issuesEnteringStatusByDateReport |           514
 issuesResolvedByDateReport       |           514
 issuesWorkLogReport              |           513
 timeInStatusReport               |           258
 historicalFlowReport             |           256
 issuesCreatedByDateReport        |           256
 issueFixVersionsReport           |           128
 issueComponentsReport            |           128
 workLoggedByDateReport           |             3
 issueReportersReport             |             2
 issuePrioritiesReport            |             1
(21 rows)

Dataplane Gadget Use on Jira Dashboards

For a count of Dataplane gadgets/reports added to Jira dashboards, run the following Jira database query.

Note: this query does not capture how widely these dashboards are shared, favorited and used by other 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)

Dataplane Gadget Use on Confluence Pages

For a count of Dataplane gadgets/reports added to Confluence pages, run the following Confluence database query.

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)


Page Contents

Copyright © 2019 Arsenale Systems, Inc. · All rights reserved · Contact Us · Powered by Atlassian Confluence