Identified in Release custom field values from JIRA

[K. Praveen Kumar Reddy]

I want to extract information of "Identified In Release"
for all the defects and support requests I have in my Jira datastore.

I have drilled down till this point:

with cte as 
(
select ji.id, ji.issuenum,pk.PROJECT_KEY,cast(ci.NEWSTRING as Nvarchar) IdentifiedInRelease,ci.ID as val
from 
jiraissue ji (NOLOCK)
left join changegroup cg (NOLOCK) on cg.issueid = ji.id
left join changeitem ci (NOLOCK) on ci.groupid = cg.id
left join project_key pk (nolock) on ji.PROJECT=pk.PROJECT_ID
left join Issuestatus IST (nolock) on IST.ID=JI.ISSUESTATUS 
where ci.FIELD like '%Identified in Release%' 
),
Maxdata as
(
select id,issuenum,max(val) as maxid,PROJECT_KEY from cte group by id,issuenum,PROJECT_KEY
)
select distinct c.ID,c.issuenum,c.IdentifiedInRelease,c.PROJECT_KEY
from 
cte c
left join Maxdata md on c.ID=md.ID and c.val=md.maxid where maxid is not null

However this is not completely capturing the custom field of Identified in Release column values fully.

Appreciate your efforts to get this corrected.
Thanks in advance.

Best,
K.Praveen Kumar Reddy

[Rene C_ _Atlassian Support_]

Hi! When you mention that it is not completely capturing the custom field values, do you mean that something is not being shown?

Also, for more context, what kind of custom field is 'Identified in Release'? Some fields might have nested values that require some tweaking in your script.

[K. Praveen Kumar Reddy]

Not completely capturing : I mean that for a few Issues I am able to retrieve the details of Identified in Release field. But for others I am not able to get the values.

Inserted images showing the Identified in release field:

[Rene C_ _Atlassian Support_]

I did some tests using the same core query as yours, and in my case, I alwyas get the complete value: 

Have you verified if there is a pattern or difference in the issues that give a value vs the ones that don't?

[K. Praveen Kumar Reddy]

Hi Rene,

My Result page is empty with the "Test Multi Version" identifier :( 

I did not observe any pattern or trend as such.

Best,

KPKR

[Rene C_ _Atlassian Support_]

Hi! I believe there was a confusion there. The name 'Test Multi Version' is the name of my custom field, in your case it would still be 'Identified in Release'. If some issues are not returning results for the values, do you mean that the issues have a value in the field 'Identified in Release' but in the query it is not returning any value?

[K. Praveen Kumar Reddy]

Hi Rene,

Absolutely yes. The custom field Identified in Release is filled with a value in the JIRA UI but the same is not retrieved by the SQL query.

Best,

KPKR 

[Rene C_ _Atlassian Support_]

That is interesting, you might want to check all the values from the table changeitem, by using something like the following and see if the values are in a different column, as not all field types save the values in the newstring column:

select ji.id, ji.issuenum,pk.PROJECT_KEY, ci.*
from
jiraissue ji
left join changegroup cg on cg.issueid = ji.id
left join changeitem ci on ci.groupid = cg.id
left join project_key pk on ji.PROJECT=pk.PROJECT_ID
left join Issuestatus IST on IST.ID=JI.ISSUESTATUS
where ci.FIELD like '%Identified in Release%'

[K. Praveen Kumar Reddy]

Hi Rene,

As of now my datastore contains more than 100 thousand records but this query is only giving me 32000 records.

I did not find any pattern as such in the results we have been retrieving.

Best,

KPKR