Database: gadget usage Server/Data Center

The following tips will help you establish your instance’s gadget usage. You can fetch that information using SQL queries run in your Jira database.

SQL snippets have been tested in Postgres and MySQL databases but should work in all other Jira-supported databases either as is or with minimal modifications.

How to fetch the gadget usage

Gadgets usage overview

This SQL query produces the details of how many gadgets are in your Jira grouped by gadget type.

Postgres

SELECT substring(pc.gadget_xml, 'gadget/gadgets/(.*).xml') as "gadget_type", count(pc.gadget_xml) as "gadget_count" FROM portletconfiguration pc, portalpage pp WHERE pc.portalpage = pp.id AND pc.gadget_xml LIKE '%net.brokenbuild.agile-reports%' GROUP BY pc.gadget_xml;

MySQL

SELECT trim(trailing '.xml' from substr(pc.gadget_xml, length(substring_index(pc.gadget_xml, '/', 6))+2)) as "gadget_type", count(pc.gadget_xml) as "gadget_count" FROM portletconfiguration pc, portalpage pp WHERE pc.portalpage = pp.id AND pc.gadget_xml LIKE '%net.brokenbuild.agile-reports%' GROUP BY pc.gadget_xml;

Result column / value details

Column

Value

Column

Value

gadget_type

Type of gadget:

  • epics-burnup (Team/Cross-team Burnup chart for Epics)

  • scrum-team-burnup (Team/Cross-team Burnup chart for Scrum boards)

  • kanban-velocity (Team/Cross-team Velocity chart for Kanban boards)

  • kanban-burnup (Team/Cross-team Burnup chart for Kanban boards)

  • sprint-burndown (Team/Cross-team Burndown chart for Scrum boards)

  • business-velocity (Team/Cross-team Velocity chart for JQL filters)

  • business-burnup (Team/Cross-team Burnup chart for JQL filters)

  • scrum-team-burndown (Team Burndown chart for Sprints)

  • scrum-cross-team-velocity (Team/Cross-team Velocity chart for Scrum boards)

  • scrum-personal-velocity (Individual Velocity chart for Scrum boards)

  • initiatives-burnup (Team/Cross-team Burnup chart for Initiatives)

  • scrum-benchmark (Team/Cross-team Benchmarking chart for Scrum boards)

gadget_count

number of gadgets

If some gadget type is absent in the result, then it is not used in your Jira at all.

SQL query output example

Gadgets usage per dashboard

image-20240411-080231.png
number of gadgets grouped by type

This SQL query produces the details of dashboards and the number of gadgets added to them:

Postgres

SELECT jira.baseurl || '/secure/Dashboard.jspa?selectPageId=' || pp.id as "dashboard_url", pp.pagename as "dashboard_name", pp.username as "dashboard_owner", count(pc.id) as "gadget_count" FROM portletconfiguration pc, portalpage pp, (select propertyvalue "baseurl" from propertyentry PE join propertystring PS on PE.id=PS.id where PE.property_key = 'jira.baseurl') "jira" WHERE pc.portalpage = pp.id AND pc.gadget_xml LIKE '%net.brokenbuild.agile-reports%' GROUP BY pp.id, jira.baseurl ORDER BY "gadget_count" DESC;

MySQL

Result column / value details

Column

Value

Column

Value

dashboard_url

dashboard url that can be used for quick access to dashboard in the browser

dashboard_name

dashboard name

dashboard_owner

dashboard owner (Jira user name)

gadget_count

number of gadgets on the dashboard

SQL query output example

image-20240411-080346.png
number of gadgets per dashboard

Gadget details with dashboard info

This SQL query produces details about gadgets, gadget settings, dashboard of the gadget:

Postgres

MySQL

Result column / value details

Column

Value

Column

Value

dashboard_url

dashboard url that can be used for quick access to dashboard in the browser

dashboard_name

dashboard name

dashboard_owner

dashboard owner (Jira user name)

gadget_id

gadget ID

gadget_type

Type of gadget:

  • epics-burnup (Team/Cross-team Burnup chart for Epics)

  • scrum-team-burnup (Team/Cross-team Burnup chart for Scrum boards)

  • kanban-velocity (Team/Cross-team Velocity chart for Kanban boards)

  • kanban-burnup (Team/Cross-team Burnup chart for Kanban boards)

  • sprint-burndown (Team/Cross-team Burndown chart for Scrum boards)

  • business-velocity (Team/Cross-team Velocity chart for JQL filters)

  • business-burnup (Team/Cross-team Burnup chart for JQL filters)

  • scrum-team-burndown (Team Burndown chart for Sprints)

  • scrum-cross-team-velocity (Team/Cross-team Velocity chart for Scrum boards)

  • scrum-personal-velocity (Individual Velocity chart for Scrum boards)

  • initiatives-burnup (Team/Cross-team Burnup chart for Initiatives)

  • scrum-benchmark (Team/Cross-team Benchmarking chart for Scrum boards)

gadget_settings

link to gadget settings in JSON format

Gadget settings JSON output

Use URL from gadget_settings column to get gadget settings in the browser:

SQL query output example

Server/DC to Cloud feature parity

Functionality of Agile Reports and Gadgets for Jira Cloud is 100% the same as for Jira Server/DC

Server/DC to Cloud development roadmap

We don’t have any development plans at the moment.

Support and questions

If you have any questions or need any help, please contact our Support team: