Auditing Dataplane Use

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)

Report Gadgets on Jira Dashboards

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)

Page Contents

1 Reports Created by Jira User
2 Reports Created by Report Type
3 Report Gadgets on Jira Dashboards
4 Report Gadgets on Confluence Pages