Is there no issue key (work item key) in the Atalassian Data Share jira_issue_field table?

[Joanna Waligora]

Originally posted in a less correct sub-forum, tough I may still not be where I should be...

The key e.g. PROJ-123 is the basis of human representation of jira/jsm tickets. For those needing reporting extracted via Delta Sharing, it seems like using the jira_issue_enhanced_table is the only option. That table comes in with a 5-8hr delay. Having to rely on it for the Key value renders all other approaches using 'live' tables critically deficient. Are there other field sources for this info? I see an issuekey field, but it appears to be null for all our projects. 

I'm thinking I may need to pull this via API, in combination with enhanced table queries for historical data, but that seems rather extreme for something so rudimentary, hence I think I may be missing something.

schema ref: https://support.atlassian.com/analytics/docs/data-share-schema-for-jira-family-of-products/#Tables-specific-to-Jira-Service-Management

[Joanna Waligora · Accepted solution]

Findings

Soo...in my specific case which may or may not be unique...
(with basic SQL used for simplicity's sake)

SELECT * 
from 
   jira_issue_field_metadata 
where 
   issue_field_id LIKE '%issuekey' 

returned a hit, BUT

select * 
from 
   jira_issue_field 
where 
   field_id like '%issuekey' 
   and `value` is not null

returned NO hits. 

This is in stark contrast to: 

SELECT * 
from 
   jira_issue_field_metadata 
WHERE 
   issue_field_id LIKE '%number'

which returned NO hit, YET

Select * 
from 
   jira_issue_field 
where 
   field_id like '%number' 
   and `value` is not null

gave me exactly what I was looking for (or at least the numerical portion of it, but concatenating it with the project key is easy enough now that I found it). 

Conclusion 

I needed to look in the `value` of field_id ending in "number", but didn't think to do so due to said field_id having no metadata associated with it. (I'm having our Jira admins look into why).

[Robert]

Hey @Joanna Waligora ! 

You're right in that the issue key isn't directly stored in the jira_issue_field table.

It looks like you got your answer, but for the sake of an example you can "generate" it by joining the number field with the project key from jira_project:

SELECT CONCAT(jp.key, '-',jif.value) as `Issue Key`
FROM jira_issue_field jif
JOIN jira_project jp ON jp.project_id = jif.project_id
WHERE jif.field_type_key = 'number' 

This should give you a list of issue (or work item :) ) keys across your data share.  

Please let me know if you have any issues getting other fields in the jira_issue_enhanced_table from the sub-tables and I'd be happy to take a look too. 

[Joanna Waligora]

Thanks @Robert Any idea why there are 3 issue_field_ids that are not present in jira_issue_field_metadata? They're ['number', 'softArchived', 'defaultRank'] (prefix stripped for clarity).

[Robert]

Hi @Joanna Waligora, 

That could indicate those fields aren't supported in the data share tables at this time.

If you would like to dig a bit deeper, I recommend submitting a support ticket over at support.atlassian.com/contact and we can take a closer look with you.

[Joanna Waligora]

@Robert - They're "supported" in the sense that they appear in the jira_issue_field.field_id column. Our software team just recently completed migrations to cloud, and they'll be doing some resets for performance reasons. If nothing changes after that, I'll take you up on the 'ticket' creation. Thanks!

[Robert]

If they were part of a cloud migration it may require a data reload by our support team (ANALYTICS-105). There are known data consistency issues that can crop up after a migration.

So we can certainly help with that if you don't improvement!