Getting custom field's last updated and creation date in jiradb database

[Sadaf Chowdhury]

Is there a way to get a custom field's last updated as well as the creation date in jiradb? The customfieldvalue table gives the key "UPDATED: but unfortunately I got nothing.

Any ideas? 

[Nic Brough -Adaptavist- · Accepted solution]

You need to join changeitem to changegroup to jiraissue, reading jiraissue for the issue details, changegroup for date of updates and changeitem to find the change of field data.

[Sadaf Chowdhury]

Thanks Nic,

Is there a way to check when a custom field was created/updated in the application/UI site? Or is it only available in the audit log? 

Thank you! 

[Nic Brough -Adaptavist-]

The custom field header is only tracked in the audit log.

[Sadaf Chowdhury]

So I wrote PSQL code to get the information for all of this but my dates do not align with what I get in the audit log. They are a couple of days off..is this normal or does the date align more with when the issue was created not when the actual custom field was made? 

psql \
   -d jiradb \
   -c "\\copy
     ( SELECT cf.customfieldtypekey, cf.ID, cf.cfname, ji.CREATED, ji.UPDATED
     FROM customfield cf
     JOIN changeitem ci
       ON cf.id=ci.id
     JOIN changegroup cg
       ON cg.id=cf.id
     JOIN jiraissue ji
       ON cg.id=ji.id
     WHERE cf.id=ji.id
     TO custom_fields.csv
     WITH CSV" \

[Nic Brough -Adaptavist-]

No, hang on, I've misled you.

• The audit log is tracking configuration changes, such as the creation of and changes to the field, as I thought you asked in your comment.
• The history items are tracking changes to issues, including changes to the values of fields, as suggested by your question.

I should have made that distinction earlier - reading your question and comment back, I had made different assumptions about each because of the slightly different structure of the sentences.  Much as I like playing games with English, sometimes, it's really unclear, even to native speakers, and slight nuances can lead anyone in the wrong direction!

[Sadaf Chowdhury]

Hey Nic thanks for the reply.

Sorry but I am still a little bit confused. The dates I get from created and updated in jiradb have nothing to do with when a customfield is created and updated right? 

I checked the audit_log database as well which returns the same created date found in jiraissue. I'm assuming the ID from customfield shares the same ID as projects? Seems like the only reasonable explanation. 

[Nic Brough -Adaptavist-]

No, none of that is right.

A field is an object that defines how you add data to an issue.  A field has a name, a type and some other stuff that defines it and its behaviour.  For now, let's call that the field header.  If you change any of these definitions, that is logged in the administrative audit log. 

Then there is the data that goes on to the issue.  This is the field value, which is constrained and defined by the field header, and you can have 0, 1 or many values for it on 0, 1 or many issues.  Changes to these values are logged in the change history.

I think the problem now is that it is not clear to me which one of these two items you actually want.

[Sadaf Chowdhury]

Basically I just want to know when a field was created like it shows in the audit log as well as when the last time this field was updated/edited.

Thanks! 

[Nic Brough -Adaptavist-]

You need to stop mixing up the field header with the field values.  Please have another read of my previous comment, where I explain they are totally different.

[Sadaf Chowdhury]

I think I get it now. A CF being updated is talking about the field value. But the creation relates to the field header. 

Still, I noticed that whatever ID I used from the customfield table did not align with the proper dates under the audit_log table and the jiraissues table. For example I have a customfield created in April 28, 2016 but shows as created on July 21st, 2014. This is why I assumed the customfield ID was shared with another ID.