Versions Compared

Old Version 7

changes.mady.by.user David Goldstein

Saved on

compared with

New Version Current

changes.mady.by.user David Goldstein

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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:

Code Block
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:

Code Block
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:

Code Block
+----------------+------------------+---------------+
| 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:

Code Block
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:

Code Block
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:

Code Block
           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:

Code Block
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:

Code Block
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.

Code Block
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:

Code Block
  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:

Code Block
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:

Code Block
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 gup.userprefkey = 'report'
ORDER BY
	dashboard, report_id DESC;

Example output:

Code Block
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 ServicesBeta | 1307012272 | adminfred | Development OpenIssues Supportby TicketsAssignee | 9cc2385ed21c0e60-6fd2d137-4ca24ffa-b785b565-f0003dc2ba5295a3e474d1eb | fred
Client ServicesProgram | 1307012470 | adminjeff | Denali SupportBacklog Ticketsand byBacklog CountryAdditions | 0968ff9e0b727c34-829a6412-4f784ccf-9f85b4e4-b23d67594f6aac0aabfd8e50 | jeff
Denalifred
Program | 12470 | jeff | DenaliDev BacklogIssues andby Backlog AdditionsPriority | 0b727c3433399578-64121f6f-4ccf4005-b4e4b1ce-ac0aabfd8e5054755f023717 | fred
Denali
Program | 12470 | jeff | Program Status - Burn Up w/ Milestones | b6602394-43d0-4b15-83bb-c52ea169670f | fred
Denali Program
Support | 13070 | admin | Open Support Tickets | 9cc2385e-6fd2-4ca2-b785-f0003dc2ba52 | fred
Support | 1247013070 | jeffadmin | DevSupport IssuesTickets by PriorityCountry | 333995780968ff9e-1f6f829a-40054f78-b1ce9f85-54755f023717b23d67594f6a | fredjeff
...

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.

Code Block
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:

Code Block
+-------+-------------+-----------+-------------------+
| space | page        | contentid | dataplane_gadgets |
+-------+-------------+-----------+-------------------+
| TEST  | Test Home 2 |  54821015 |                 5 |
| TEST  | Test Home   |  46432275 |                 3 |
+-------+-------------+-----------+-------------------+
2 rows in set (0.05 sec)





Panel

Page Contents

Table of Contents