How to List Filter Subscription of an Issue Filter in JIRA
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
The purpose of this article is to show how to get a list of filter subscription of an issue filter in JIRA. The filter subscription can be seen at the "Details" of the issue filter. This article will help on getting a list of filter subscription of an issue filter in JIRA by executing a SQL command against the JIRA database.
Solution
Workaround
Log in to JIRA database.
Run the following SQL command to list the subscription:
1
select * from filtersubscription where filter_i_d = (select id from searchrequest where filtername = '<filter name>');ℹ️ Substitute "<filter name>" in the query with your issue filter name
Example result will look like the following:
1
2
3
4
id | filter_i_d | username | groupname | last_run | email_on_empty
-------+------------+----------+---------------------+----------+----------------
10001 | 10001 | admin | jira-administrators | | false
(1 row)⚠️ For MSSQL, the SQL query will be:
1
SELECT * FROM [<your JIRA database name>].[dbo].[filtersubscription] WHERE [FILTER_I_D]=(SELECT ID FROM [<your JIRA database name>].[dbo].[searchrequest] WHERE [filtername]='<filter name>')ℹ️ Substitute "<your JIRA database name>" and "<filter name>" in the query with your JIRA database name and your issue filter name
Additional information
The subscription details such as 'schedule' or 'next run' are stored in the clusteredjob database table. To retrieve these data, run the following steps.
Note the
idof thefiltersubscriptionentry from the query above.Append it to the following query
1select * from clusteredjob where job_id = 'com.atlassian.jira.issue.subscription.DefaultSubscriptionManager:<subscription_id>';ℹ️Substitute "<subscription_id>" in the query with the filtersubscription id from step 1
Example result will look like following:
1 2 3 4 5 6 7 8 9 10 11id | 11323 job_id | com.atlassian.jira.issue.subscription.DefaultSubscriptionManager:10002 job_runner_key | com.atlassian.jira.issue.subscription.DefaultSubscriptionManager sched_type | C interval_millis | first_run | cron_expression | 0 0 1 ? * 2,3,4 time_zone | next_run | 1553446800000 version | 1 parameters | BINARY, 333 Bytes
This can be translated into:
Column | Value | Human Readable Format |
|---|---|---|
| 0 0 1 ? * 2,3,4 | At 01:00:00am, on every Monday, Tuesday and Wednesday, every month via freeformatter |
| 1553446800000 | Monday, March 25, 2019 1:00:00 AM GMT+08:00 via epochconverter |
Was this helpful?