How to gather issue collector data from the database
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
At times, when a issue collector page becomes inaccessible due to permissions or errors, the data contained in the issue collector can be gathered from the database
Environment
8.20.10, 9.x
Solution
The following query is tested for postgres only. There is a prerequisite to install table functions to run the query.
1
CREATE EXTENSION tablefunc;After installing the tablefunc extension by running the above command in the psql prompt, run the below query
1
2
3
4
5
6
select tbl.EntitytName , tbl.collectorId , tbl.creator ,tbl.customMessage ,tbl.customTemplateFields ,tbl.customTemplateLabels ,tbl.customTemplateTitle ,tbl.description ,tbl.enabled ,it.pname , tbl.name , p.pname , tbl.recordWebInfo , tbl.reporter ,tbl.templateId , tbl.triggerPosition , tbl.triggerText , tbl.useCredentials from
(Select * from crosstab($$select pe.entity_name,pe.property_key,ps.propertyvalue from propertyentry pe join propertystring ps on ps.id = pe.id where pe.entity_name in (select entity_name from propertyentry where property_key = 'collectorId' and propertytype = 5 and entity_id = 1 order by entity_name) order by pe.entity_name,property_key$$,
$$
select distinct property_key from propertyentry where entity_name in (select entity_name from propertyentry where property_key = 'collectorId' and propertytype = 5 and entity_id = 1 order by entity_name) order by property_key
$$)
AS final_result(EntitytName TEXT, collectorId TEXT, creator TEXT,customMessage TEXT,customTemplateFields TEXT,customTemplateLabels TEXT,customTemplateTitle TEXT,description TEXT,enabled TEXT,issueTypeId TEXT, name TEXT, projectId TEXT, recordWebInfo TEXT,reporter TEXT,templateId TEXT, triggerPosition TEXT, triggerText TEXT, useCredentials TEXT))tbl join project p on p.id = tbl.projectId::integer join issuetype it on it.id = tbl.issueTypeId;The output of the above query would look like below
1
2
3
4
5
6
7
8
entitytname | collectorid | creator | custommessage | customtemplatefields | customtemplatelabels | customtemplatetitle | description | enabled | pname | name | pname | recordwebinfo | reporter | templateid | triggerposition | triggertext | usecredentials
-------------+-------------+------------------+-------------------------------------+--------------------------------------------------------------------------------------------+----------------------+---------------------+-------------+---------+-------+--------------------------+-------+---------------+------------------+------------+-----------------+------------------+----------------
56d649c9 | 56d649c9 | shri@example.com | Please provide your feedback below: | summary,description,priority | | | | true | Task | Issue_Collect_Demo | SCRUM | true | shri@example.com | custom | CUSTOM | Provide feedback | false
77cf1060 | 77cf1060 | shri@example.com | Please provide your feedback below: | summary,environment,description,priority | | | | true | Bug | Issue_Collect_custom_bug | SCRUM | false | shri@example.com | custom | CUSTOM | Provide feedback | false
8f989151 | 8f989151 | shri@example.com | Please provide your feedback below: | summary,components,description,priority,versions,customfield_10201,screenshots,environment | | | | true | Bug | Issue_Collect_custom | SCRUM | true | shri@example.com | custom | CUSTOM | Provide feedback | false
a71bd2e6 | a71bd2e6 | shri@example.com | Please provide your feedback below: | summary,description,priority,customfield_10201,screenshots | | | | true | Task | Issue_Collect | SCRUM | true | shri@example.com | custom | CUSTOM | Provide feedback | false
db9b7c37 | db9b7c37 | shri@example.com | Please provide your feedback below: | summary,components,description,priority,customfield_10201 | | | | true | Task | Test_Collector_For_SQL | SCRUM | true | shri@example.com | custom | TOP | Provide feedback | false
Was this helpful?