what sql table in JIRA has the Issue Collector information.

Question

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

KJ Tan · Answer

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 · Answer

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.

albert capps · Answer

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