...
| Info |
|---|
SQL snippets are tested in Postgres and MySQL database, but should work in all other Jira supported databases either as is or with minimal modifications. |
Migration guide
Gadgets usage overview
This SQL query produces the details of how many gadgets are in your Jira grouped by gadget type.
...
| Code Block | ||
|---|---|---|
| ||
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.velocity-chart%'
GROUP BY pc.gadget_xml; |
Result column / value details
Column | Value |
|---|---|
gadget_type | Type of gadget:
|
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
This SQL query produces the details of dashboards and the number of gadgets added to them:
...
| Code Block | ||
|---|---|---|
| ||
SELECT concat(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') as jira
WHERE pc.portalpage = pp.id
AND pc.gadget_xml LIKE '%net.brokenbuild.velocity-chart%'
GROUP BY pp.id, jira.baseurl
ORDER BY "gadget_count" DESC; |
Result column / value details
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
...
Gadget details with dashboard info
This SQL query produces details about gadgets, gadget settings, dashboard of the gadget:
...
| Code Block | ||
|---|---|---|
| ||
SELECT concat(jira.baseurl, '/secure/Dashboard.jspa?selectPageId=', pp.id) as "dashboard_url",
pp.pagename as "dashboard_name",
pp.username as"dashboard_owner",
pc.id as "gadget_id",
trim(trailing '.xml' from substr(pc.gadget_xml, length(substring_index(pc.gadget_xml, '/', 6))+2)) as "gadget_type",
concat(jira.baseurl, '/rest/dashboards/1.0/', pp.id, '/gadget/', pc.id, '/prefs.json') as "gadget_settings"
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') as jira
WHERE pc.portalpage = pp.id
AND pc.gadget_xml LIKE '%net.brokenbuild.velocity-chart%'
ORDER BY pp.id; |
Result column / value details
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:
|
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
| Tip |
|---|
Functionality of Velocity Chart 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:
...