How to find users and filters that have specific fields visible on Search Results through the database on Jira
プラットフォームについて: Data Center のみ - この記事は、Data Center プラットフォームのアトラシアン製品にのみ適用されます。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
As a consequence of the problems described on article JQL Issue searches are very slow only for a specific user (Server and Data Center), an admin may need to review which users or filters have a specific field being displayed on their searches. This can be relevant since some fields can affect performance when displayed for a large result set - commonly scripted fields or third-party fields.
This article refers to the columns visible on the Issue Navigator as in the screenshot below:
環境
Jira 8.20.20, 9.4.14, 9.12.2
ソリューション
These queries where written and tested for PostgreSQL but they may require minor tweak depending on your database product and your configuration.
参考:
- columnlayoutitem table contains a record for each column layout ID and column they have on their Issue Search. The customfield is stored is the "customfield_xxxx" format under the fieldidentifier column.
- columnlayout links a user or a filter with the the records from the table above.
Find users with the field on their Issue Navigator configuration
- Replace xxxxx with the ID of the customfield.
select cl.username,au.lower_user_name, cli.fieldidentifier from columnlayout cl
join columnlayoutitem cli on cl.id=cli.columnlayout
join app_user au on cl.username=au.user_key
where cli.fieldidentifier in ('customfield_xxxxx');Find filters with the field on their Issue Navigator column configuration
- This does not affect the filter results nor does is affect performance when the filter is used as a board or plan source. It's only used when the user has the Filter option selected:
- Replace xxxxx with the ID of the customfield.
select sr.filtername,sr.id,sr.authorname, au.lower_user_name, cli.fieldidentifier from columnlayout cl
join columnlayoutitem cli on cl.id=cli.columnlayout
join searchrequest sr on cl.searchrequest=sr.id
join app_user au on sr.authorname=au.user_key
where cli.fieldidentifier='customfield_xxxxx';