Auditing Dataplane Use
David Goldstein
Owned by David Goldstein
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.
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' 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.
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