フィルター・ダッシュボードの共有範囲を一覧で確認・出力したい

1 回答

1.  

   1

   0

   -1

   • Kengo Ohsaki
   • 2016/08/30

   以下REST APIで取得していく…あまり効率的ではないですね…

   https://docs.atlassian.com/jira/REST/server/#api/2/filter-getFilter

   DB操作でよければ…

   JIRA get list of all filters shared with everyone のSQL（全員に共有してるフィルター・ダッシュボードを一覧にするSQL）を参考にして…

   非常に安直なSQLですが以下のような形で一覧化できるかと思います。

    

   フィルター

   SELECT sr.id, sr.filtername, sp.sharetype AS current_share_state, p.pname AS project_or_group, pr.name AS role, sr.username AS owner_name, sr.reqcontent AS JQL
   FROM ((searchrequest sr INNER JOIN sharepermissions sp ON sp.entityid = sr.id) LEFT JOIN project p ON p.id = CAST(sp.param1 as NUMERIC)) LEFT JOIN projectrole pr ON pr.id = CAST(sp.param2 as NUMERIC)
   WHERE sp.sharetype='project' and sp.entitytype ='SearchRequest'
   UNION
   SELECT sr.id, sr.filtername, sp.sharetype AS current_share_state, g.group_name AS project_or_group, null, sr.username AS owner_name, sr.reqcontent AS JQL
   FROM ((searchrequest sr INNER JOIN sharepermissions sp ON sp.entityid = sr.id) LEFT JOIN cwd_group g ON g.lower_group_name = sp.param1)
   WHERE sp.sharetype='group' and sp.entitytype ='SearchRequest'
   UNION
   SELECT sr.id, sr.filtername, sp.sharetype AS current_share_state, null, null, sr.username AS owner_name, sr.reqcontent AS JQL
   FROM ((searchrequest sr INNER JOIN sharepermissions sp ON sp.entityid = sr.id) LEFT JOIN cwd_group g ON g.lower_group_name = sp.param1)
   WHERE sp.sharetype='global' and sp.entitytype ='SearchRequest'

   ダッシュボード

   SELECT DISTINCT pp.id as Dashboard_Id, pp.pagename AS Dashboard_name, sp.sharetype AS current_share_state, p.pname AS project_or_group, pr.name AS role, pp.username AS owner_name
   FROM ((portalpage pp INNER JOIN sharepermissions sp ON sp.entityid = pp.id) LEFT JOIN project p ON p.id = CAST(sp.param1 as NUMERIC)) LEFT JOIN projectrole pr ON pr.id = CAST(sp.param2 as NUMERIC)
   WHERE sp.sharetype='project' and sp.entitytype ='PortalPage'
   UNION
   SELECT DISTINCT pp.id as Dashboard_Id, pp.pagename AS Dashboard_name, sp.sharetype AS current_share_state,  g.group_name AS project_or_group, null, pp.username AS owner_name
   FROM ((portalpage pp INNER JOIN sharepermissions sp ON sp.entityid = pp.id) LEFT JOIN cwd_group g ON g.lower_group_name = sp.param1)
   WHERE sp.sharetype='group' and sp.entitytype ='PortalPage'
   UNION
   SELECT DISTINCT pp.id as Dashboard_Id, pp.pagename AS Dashboard_name, sp.sharetype AS current_share_state, null, null, pp.username AS owner_name
   FROM (portalpage pp INNER JOIN sharepermissions sp ON sp.entityid = pp.id)
   WHERE sp.sharetype='global' and sp.entitytype ='PortalPage';
   
   

    

    PostgreSQL環境で確認。それ以外の環境は適切に修正してください。

   ポイントは、searchrequestにフィルター、portalpageにダッシュボード、sharepermissions テーブルに夫々の共有設定が入っている。

   sharepermissions の sharetype が global で全員、project でプロジェクト共有限定、groupでグループ共有限定。sharepermissions の param1に project の場合は、プロジェクトID、groupの場合はグループ名（小文字正規化）、param2にproject の場合はロール（NULLの場合はすべて）、groupの場合はNULLです。

    

   •  コメント

   1. 矢矧

      • 2016/09/01

      回答ありがとうございます。

      DB操作ですか。。
      トライしてみます！ 

   Commentコメントを追加...