How to list Dashboards and Gadgets definitions in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
This article offers a way to list Dashboards and Gadgets definitions from Jira's database.
It can be useful to recreate a deleted Dashboard or Gadget, if you can restore a database backup and use the queries output to create a new Dashboard and Gadgets and have them configured like the original ones.
Solution
This database query will list the main columns of the Dashboard and Gadgets definitions:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select d.id as "dashboard_id"
, d.pagename as "dashboard_name"
, d.username as "owner"
, d.layout as "layout"
, p.id as "gadget_id"
, p.column_number as "column"
, p.positionseq as "position"
, p.gadget_xml
, p.color as "color"
, g.userprefkey as "gadget_property_name"
, g.userprefvalue as "gagdet_property_value"
from portalpage d
left join portletconfiguration p on p.portalpage = d.id
left join gadgetuserpreference g on g.portletconfiguration = p.id
where d.id = 99999 /* Dashboard id from .../Dashboard.jspa?selectPageId=99999 */
order by d.id, p.id, g.userprefkey;Replace 99999 with the desired Dashboard Id (you can get it from Jira's URL).
Sample output (truncated for legibility):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
dashboard_id | dashboard_name | owner | layout | gadget_id | column | position | gadget_xml | color | gadget_property_name | gagdet_property_value
--------------+-------------------+-------+--------+-----------+--------+----------+----------------------------------------------+--------+----------------------+--------------------------
10100 | Example Dashboard | admin | AA | 10100 | 0 | 0 | text-gadget/gadgets/text-gadget.xml | color1 | html | Text gadget content here
10100 | Example Dashboard | admin | AA | 10100 | 0 | 0 | text-gadget/gadgets/text-gadget.xml | color1 | isConfigured | true
10100 | Example Dashboard | admin | AA | 10100 | 0 | 0 | text-gadget/gadgets/text-gadget.xml | color1 | refresh | false
10100 | Example Dashboard | admin | AA | 10100 | 0 | 0 | text-gadget/gadgets/text-gadget.xml | color1 | title | Text gadget title here
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | id | 10000
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | isConfigured | true
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | isPopup | false
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | name | Filter for SCRUM board
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | projectOrFilterId |
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | refresh | false
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | statType | statuses
10100 | Example Dashboard | admin | AA | 10101 | 1 | 0 | pie-chart-gadget/gadgets/piechart-gadget.xml | color1 | type | filterHere's the current tables definitions as of Jira 9.x (JSM 5.x):
1
2
3
4
5
6
7
8
9
10
portalpage:
id | username | pagename | description | sequence | fav_count | layout | ppversion
portletconfiguration:
id | portalpage | portlet_id* | column_number | positionseq | gadget_xml | color | dashboard_module_complete_key
gadgetuserpreference:
id | portletconfiguration | userprefkey | userprefvalue
*: deprecated columnsTo experiment, you can select d.*, p.*, g.* to fetch all columns and filter the ones you want from there:
1
2
3
4
5
6
select d.*, p.*, g.*
from portalpage d
left join portletconfiguration p on p.portalpage = d.id
left join gadgetuserpreference g on g.portletconfiguration = p.id
where d.id = 99999 /* Dashboard id from .../Dashboard.jspa?selectPageId=99999 */
order by d.id, p.id, g.userprefkey;Or edit the query to find all Dashboards with a Text Gadget, for example, bringing only the Gadgets' title and html content:
1
2
3
4
5
6
7
8
9
10
11
12
13
select d.id as "dashboard_id"
, d.pagename as "dashboard_name"
, d.username as "owner"
, p.id as "gadget_id"
, g.userprefkey as "gadget_property_name"
, g.userprefvalue as "gagdet_property_value"
from portalpage d
left join portletconfiguration p on p.portalpage = d.id
left join gadgetuserpreference g on g.portletconfiguration = p.id
where
lower(p.gadget_xml) like '%text-gadget.xml%'
and g.userprefkey in ('title', 'html')
order by d.id, p.id, g.userprefkey desc;Was this helpful?