what sql table in JIRA has the Issue Collector information.

[albert capps]

Trying to look into jira at the database level and see all the issuer collectors. 

[KJ Tan]

Select TOP 1000 pe.ENTITY_NAME AS IssueCollectorID, 
(SELECT psname.propertyvalue FROM dbo.propertyentry AS pename INNER JOIN dbo.propertystring AS psname ON pename.ID = psname.ID WHERE pename.ENTITY_NAME = pe.ENTITY_NAME AND pename.PROPERTY_KEY = 'name') AS IssueCollectorName,
(SELECT p.pname FROM dbo.propertyentry AS peprojectid INNER JOIN dbo.propertystring AS psprojectid ON peprojectid.ID = psprojectid.ID INNER JOIN dbo.project p ON p.ID = CAST(CAST(psprojectid.propertyvalue AS Varchar) AS int) WHERE peprojectid.ENTITY_NAME = pe.ENTITY_NAME AND peprojectid.PROPERTY_KEY = 'projectId') AS ProjectName,
(SELECT pscreator.propertyvalue FROM dbo.propertyentry AS pecreator INNER JOIN dbo.propertystring AS pscreator ON pecreator.ID = pscreator.ID WHERE pecreator.ENTITY_NAME = pe.ENTITY_NAME AND pecreator.PROPERTY_KEY = 'creator') AS Creator,
(SELECT psdesc.propertyvalue FROM dbo.propertyentry AS pedesc INNER JOIN dbo.propertystring AS psdesc ON pedesc.ID = psdesc.ID WHERE pedesc.ENTITY_NAME = pe.ENTITY_NAME AND pedesc.PROPERTY_KEY = 'description') AS Description
from dbo.propertyentry AS pe WITH (nolock)
WHERE pe.PROPERTY_KEY = 'collectorId'

[albert capps]

I found my answer in the JIRA tables
•dbo.propertyentry
•dbo.propertystring

this query will pull back the all Issue Collector Information from JIRA.

Key info is that PropertyType = 5 (Issue Collector)

and in our case Enity_ID = 1

Query..

Select * from dbo.propertyentry AS pe WITH (nolock)

INNER JOIN dbo.propertystring AS ps WITH (nolock)

   ON ps.ID = pe.ID

INNER JOIN dbo.project AS p WITH (Nolock)

   ON p.ID = CAST(CAST(ps.propertyvalue AS varchar) AS Int)

INNER JOIN (SELECT pe.ID AS PropertryEntryID

                 , pe.ENTITY_NAME

                 , pe.PROPERTY_KEY

                 , ps.propertyvalue

           FROM dbo.propertyentry AS pe WITH (nolock)

           INNER JOIN dbo.propertystring AS ps WITH (nolock)

                 ON ps.ID = pe.ID

           WHERE (pe.propertytype = 5)

              AND (pe.ENTITY_ID = 1)

              AND (pe.PROPERTY_KEY = 'collectorId')) AS C

     ON C.ENTITY_NAME = pe.ENTITY_NAME

output Columns :

Collector_Property_Key

CollectorID_PropertyEntryID

CollectorID

Property_KEY

PropertyEntryID

Entity_Name

ProjectID

ProjectName

ProjectKey

ProjectDescritpion

[albert capps]

One issue I have is the ProjectID is not working now. I commented out 

INNER JOIN dbo.project AS p WITH (Nolock)

and it still works but no project tie.

[Brittany Wispell]

Hey @albert capps 

There is documentation here Advanced Use of Jira Issue Collector that might have your answer. 

If you have any other questions please let me know!