Is there SQL query that will pull all JIRA Dashboards which use "Shared Ownership for Dashboards"?

Allison Kellner

I am trying to efficiently identify all JIRA dashboards which use the "Shared Ownership for Dashboards" plugin. Unfortunately, the query I typically use to pull back boards which may use certain gadgets or plugins does not render any data back when searching for the "Shared Ownership for Dashboards" plugin. 

 

select pagename
from portalpage p
where p.id in (
select PORTALPAGE
from portletconfiguration
where GADGET_XML like '%com.qotilabs.jira.shared-ownership-for-dashboards-and-filters%'

)

 

Any suggestions would be greatly appreciated!

Andy Heinzer

Hi Allison,

I understand you want to find out which dashboards were utilizing the share ability of the plugin Shared Ownership for Dashboards/Filters.  Just some back story for others that might find this post, this plugin provided the ability to share filters and dashboard ownership with other users in Jira.  This is something that came to Jira natively in 7.12, in turn this plugin has been made obsolete in Jira 7.12 and higher versions.

I dug into this to find more details how you could find this information. In my setup I used a 7.11.0 Jira and installed this plugin.  I had to turn on SQL logging in Jira to learn more about how this plugin was storing its data into Jira's database.  But this lead me to be able to recreate the action of adding a few new users to an existing dashboard, and then check the logs really quick to see what table it stored this data, I saw this in the logs:

UPDATE public.entity_property SET UPDATED = '2019-05-21 12:55:36.445' , json_value = '["admin","gg","ahuser124","agrant-sd-demo"]' WHERE ID='11800'"

This lead me to take a closer look at the entity_property table. This table can store all sorts of data, not just plugin info.  I found that all the entries that this plugin makes in that table were also setting an entity_name of the pluginkey: com.qotilabs.jira.shared-ownership-for-dashboards-and-filters:PortalPage

In turn I was able to craft a SQL query that will return all the shares created by that plugin for dashboards and return the name of the dashboard as well:

select pp.pagename, ep.* from entity_property ep
join portalpage pp on pp.id = ep.entity_id
where entity_name='com.qotilabs.jira.shared-ownership-for-dashboards-and-filters:PortalPage';

In addition to that query, you can also find all the filters that might be using that plugin's abilities with the SQL query:

select sr.filtername, ep.* from entity_property ep
join searchrequest sr on sr.id = ep.entity_id
where entity_name='com.qotilabs.jira.shared-ownership-for-dashboards-and-filters:SearchRequest';

I hope this helps.

Andy