JIRA - Access to Database for Timetracking Data

[Gaurav Thaker]

Hi Guys,

I am using JIRA detault timetracking feature. With this I am able to provide estimate for each cases and can fill up timesheets by means of logging time under each case by each users.

Somehow, from the default reports that JIRA provides, they do not serve my purpose of Management Reporting.

To overcome this issue, I am thinking to connect to JIRA Database and writing my own SQL to fetch the data I need and create my custom reports.

Can someone advise on how do I connect to JIRA Database for this purpose?

Thanks,

Gaurav Thaker

[Nic Brough -Adaptavist- · Accepted solution]

Do not use the database directly.  It is not optimised or designed for reporting and you'll quickly find yourself trying to write hideously complex SQL that can become a performance problem and still not give you the data you need.

Please, use the REST API for extracting reports - it's much more logical, faster, respects permissions, and doesn't need you to understand how the Jira database works.

(And if you do connect to the database despite that, make sure you only have a read-only account - never never write to an active Jira database, you WILL damage it)

[Gaurav Thaker]

Hi Nic,

Thank you very much for guidance here, using API makes better sense, I will avoid using direct Database access. If possible, could you please any guidance on using REST API with JIRA for this purpose?

Thanks,

Gaurav

[Nic Brough -Adaptavist-]

@Andrew  beat me to the links you need for the REST API.  As they say, you have no database access to Cloud anyway.

[Andrew]

Hi @Nic Brough -Adaptavist- ,

API https://developer.atlassian.com/cloud/jira/platform/rest/v3/?_ga=2.258445834.1096858283.1559544512-115832795.1554099184

B.R.

[Gaurav Thaker]

Hi @Nic Brough -Adaptavist-  & @Andrew ,

Thank you to both of you. I think below is the API I was looking for. I will give try with this for what I want to achieve.

https://developer.atlassian.com/cloud/jira/platform/rest/v3/#api-rest-api-3-issue-issueIdOrKey-worklog-get

Thanks,

Gaurav

[Andrew · Accepted solution]

Hi @Gaurav Thaker ,

Information about DB you can find in admin panel 'system info' 

http://localhost:8087/secure/admin/ViewSystemInfo.jspa

Try find 'Database URL' parameter, but this not show credentials.

Info with credential You can find in C:\Atlassian\Application Data7\JIRA\dbconfig.xml. (My path for example, you need only xml file).

But will be very careful. Change directly in DB may break all system.

B.R.

[Gaurav Thaker]

Dear B.R.,

Thank you very much for quick response. Apology, but I forgot to mention that we are using On Demand (Cloud) and not local set up. Would you be able to provide instruction for that please?

Thanks,

Gaurav

[Andrew]

Looks like only way it is use API 

https://community.atlassian.com/t5/Jira-questions/Is-there-a-way-to-connect-to-the-jira-cloud-database-using-sql/qaq-p/676203

https://community.atlassian.com/t5/Jira-questions/Access-to-the-database-from-Jira-on-the-cloud/qaq-p/5755

B.R.

[Gaurav Thaker]

Thank you B.R., I will try to play with that.

Regards,

Gaurav