Finding the creation date of both projects and custom fields in Jira Software

[Francis Batilo]

Hi Atlassian Community folks,

I'm relatively new to using the product suite, and I wanted to know if there is a way to get either the project or custom fields creation date in Jira Software?

I have access to script runner and background knowledge of SQL. 

I have the following options, but I do not know the table names of where the data is stored.

Note: I have no idea what the data dictionary of each table is, so I do apologise if I reference column names that may not exist.

Options

1. Find the Project creation date through the project table
2. Find the project creation date by joining the project table and the board table
3. Find the project creation date by joining the project table and the issues table and find the oldest issue
4. Find the project creation date by joining it with the audit logs table

As for the custom fields, I have no clue.

Hoping to hear from you all.

Thank you in advance!

[Jonas Ekström · Accepted solution]

Hi,

Administration -> System -> Audit Log (Or use short-cut GG and type Audit log)

In the audit log you can find all the info needed, no need for any scripts or SQL queries.

[Nic Brough -Adaptavist-]

In fact, SQL queries won't get you anything more useful - the project and custom field creation dates are not recorded in the database against the items themselves.  Any SQL you run would only be able to find the data in the audit log!

[Francis Batilo]

@Jonas Ekström and @Nic Brough -Adaptavist-, Thanks for all the suggestions! I'll take note of them. 

As for an automated tool that gives you a list without going through the audit logs (e.g. projects created, say, 4 years ago), do we have that? Or is browsing through the audit logs the best we've got thus far?

[Nic Brough -Adaptavist- · Accepted solution]

Nope, as I said, the data isn't stored anywhere (other than the audit log), there's nothing for a tool to list.

The only thing I can explain that can get you a (very rough and clumsy) list is "look at the raw ID of an object".  You can use that to get at least a time-line.

Imagine your project table has a few projects in it:

10000, AAA, Project Alpha, Charlie

10010, BCD, Project Beta, Alice

10020, GAM, Project Gamma, Bob

Your best source of info on project age is likely to be a chat with Alice, Bob and Charlie, but from that raw data, you can tell that Project Alpha was created first, Gamma last, and Beta sometime between the other two.  The IDs, while not necessarily sequential, are relative - they always go up, so you know at least what order they were created in.

Given that, you can then start to do things that get you some date timelines.  In this case, I would look at the oldest existing issue in each project.  The "issue created" date on that will be after the date the project was created, and often shortly afterwards (most people will use a project as soon as they get it, not leave it lying around for three months).  Of course, it's possible AAA got created and ignored until after BCD was created, but the date on BCD-1 still at leasts tells you that the project must have been created before that point.

[Vikrant Yadav · Accepted solution]

Hi @Francis Batilo Welcome to Atlassian Community!

Check Out Database Schema,all Jira database tables, table columns, and their data type.  :- https://developer.atlassian.com/server/jira/platform/database-schema/

https://developer.atlassian.com/server/jira/platform/attachments/jira-7-9-2-database-schema.pdf

@Jonas Ekström  suggestion is right, it's better not to touch JIRA Database use REST API or ScriptRunner scripts for getting data from JIRA . 

Thanks,

Vikrant Yadav