Get all Filter id's of Jira Dashboard

[Logeshwari]

I need to update JQL in all filters of a particular dashboard

Trying to automate using jenkins job.

I am able to update single  filter JQL using Rest api.

But how do i get all filter id's of a dashboard using api or SQL

[Logeshwari]

Hi,

[Humashankar VJ]

SQL is not going to work int his scenario.

Try API, with the dashboard ID as reference we able to fetch the complete details - Let me try to go closer to this

[Radek Dostál]

There isn't really an easy list like this.

Pretty sure not with REST, and fairly sure not in SQL either (well, can be done but with major pain).

 -- Dashboards
# SELECT * FROM portalpage LIMIT 2;
  id   | username |              pagename               |        description         | sequence | fav_count | layout | ppversion
-------+----------+-------------------------------------+----------------------------+----------+-----------+--------+-----------
10252 | user123  | Dashboard for ABC           | Copy of 'System Dashboard' |          |         1 |        |         0
10253 | name123  | Dashboard for 123 | Copy of 'System Dashboard' |          |         1 |        |         0
(2 rows)

 
-- Gadgets on dashboards
# SELECT * FROM portletconfiguration LIMIT 2;
  id   | portalpage | portlet_id | column_number | positionseq |                                                 gadget_xml                                                  | color  | dashboard_module_complete_key
-------+------------+------------+---------------+-------------+-------------------------------------------------------------------------------------------------------------+--------+-------------------------------
32757 |      21320 |            |             0 |           0 | rest/gadgets/1.0/g/com.atlassian.jira.gadgets:filter-results-gadget/gadgets/filter-results-gadget.xml       | color6 |
10316 |      10181 |            |             1 |           0 | rest/gadgets/1.0/g/com.atlassian.jira.gadgets:favourite-filters-gadget/gadgets/favourite-filters-gadget.xml |        |
(2 rows)

 
-- Gadget configuration
# SELECT * FROM gadgetuserpreference LIMIT 2;
   id   | portletconfiguration | userprefkey  | userprefvalue
--------+----------------------+--------------+---------------
952200 |                29140 | filterId     | filter-34404
952201 |                29140 | isConfigured | true
(2 rows)

So as you can see, you would be able to do joins on those tables, but then you need to drill down to `gadgetuserpreference.userprefvalue` --  and this could very well differ between individual gadgets, they don't necessarily all need to use the same userprefkey, and they don't even need to be in the same userprefvalue (not even for the same gadget itself, because of backwards compatibility).

I would argue that it would be easier to either fix this globally on all filter levels (but you would need to be really good in regular expression not to mess up something else by accident), or to go on the dashboard and just do it manually by hand.

Perhaps if you just list all userprefkeys for gadgets connected to your dashboard, you could then better see/decide what to do, I'm thinking e.g. this would be a good overview to decide next steps.

SELECT DISTINCT (gup.userprefkey, gup.userprefvalue) FROM gadgetuserpreference gup JOIN portletconfiguration pc ON pc.id = gup.portletconfiguration JOIN portalpage pp ON pp.id = pc.portalpage WHERE pp.id = <dashboard id>;

[Logeshwari]

[Logeshwari]

Hi,

This is how the query returns output

[Logeshwari]

Hi,

Do we need to  get all id's from the above result? While given query is executed i got the above rows of data.

Thanks,

Logeshwari

[Logeshwari]

HI, 

Can anyone help with the above question

Thanks,

Logeshwari