Audit Dataplane use

Number of reports 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)

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;

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)

List of all reports

For a list of all Dataplane reports created by users, run the following Jira database query.

For non-postgres databases:

SELECT
	owner as report_owner,
	name as report_name,
	guid as report_id
FROM
	AO_6714C7_SAVED_REPORT_RECORD
ORDER BY
	report_owner, report_name;

For postgres databases:

SELECT
	"OWNER" as report_owner,
	"NAME" as report_name,
	"GUID" as report_id
FROM
	"AO_6714C7_SAVED_REPORT_RECORD"
ORDER BY
	report_owner, report_name;

Number of report gadgets by Jira dashboard

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

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'
ORDER BY
	dashboard, report;

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'
ORDER BY
	dashboard, report;

Example output:

dashboard | dashboard_id | dashboard_owner | report | report_id | report_owner 
--------------------+--------------+-----------------+------------------------------------------------------------------+--------------------------------------+--------------
Beta | 12272 | fred | Denali Backlog and Backlog Additions | 0b727c34-6412-4ccf-b4e4-ac0aabfd8e50 | fred
Beta | 12272 | fred | Development Issues by Assignee | d21c0e60-d137-4ffa-b565-95a3e474d1eb | fred
Program | 12470 | jeff | Denali Backlog and Backlog Additions | 0b727c34-6412-4ccf-b4e4-ac0aabfd8e50 | fred
Program | 12470 | jeff | Dev Issues by Priority | 33399578-1f6f-4005-b1ce-54755f023717 | fred
Program | 12470 | jeff | Program Status - Burn Up w/ Milestones | b6602394-43d0-4b15-83bb-c52ea169670f | fred
Support | 13070 | admin | Open Support Tickets | 9cc2385e-6fd2-4ca2-b785-f0003dc2ba52 | fred
Support | 13070 | admin | Support Tickets by Country | 0968ff9e-829a-4f78-9f85-b23d67594f6a | jeff
...

Number of report gadgets by Confluence page

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

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 Number of reports by Jira user
2 Number of reports by report type
3 List of all reports
4 Number of report gadgets by Jira dashboard
5 List of reports by Jira dashboard
6 Number of report gadgets by Confluence page