It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to export data from worklog via sql query including customfield_10000 (account)

Adam Alker Jul 30, 2015

Hello,

I am using default script to export worklogs from Tempo Timesheet to Excel (namely: Tempo/Timesheet/List/Export to Excel) but since last 3 month it's working extremely slowly and crashes web browser. It is obviously because of the amount of data (during monthly period we are generating about 15k records).

Hence we tried to use Tempo API/GetWorklog Function ( http://blog.tempoplugin.com/2012/creating-excel-reports-using-high-level-permissions-api-export/), but this time Excel is crashing and we've found out major differences between data values in Timesheet in JIRA and Excel report.

So, the best way for us is to create SQL query. Unfortunately I am not able to take value from "customfield_1000 - Account" (we need to aggregate hours under account). Maybe you can help me in this (I mean how to display customfield value) or point out another efficient way of exporting data from worklog report to external file (*.xlsx, *.xml, *.csv).

 

Thank you in advance,

Adam

3 answers

0 votes

Hi Adam,

This amount of data exported to Excel will cause performance problems. We do recommend that you shorten the period to be exported. Some users with this number of worklogs for one month do even run a worklog export on a hourly basis.

Regarding the mismatch of data values in Tempo Timesheets and JIRA, can you create an issue in our Tempo support system (https://tempoplugin.jira.com/wiki/display/JTS/Getting+Help+and+Support) and add screenshots and more information so we can better investigate what is causing this.

Regards,

Susanne

0 votes
Adam Alker Jul 31, 2015

Dear Susanne,

thank you very much for your replay and suggestion. Unfortunately we cannot switch to shorten periods right now, however we are still considering this option, because we are closing account monthly.

Maybe you can help me with further question from my post regarding SQL query which is similar to Tempo/Timesheet/List/Export to Excel script? Right now I've got something like this:

select 
round(timeworked/3600,2) as "time",
  display_name,
  summary,
  c.customfield,
  stringvalue,
to_char(startdate,'yyyy-mm-dd') as data
from worklog w 
inner join jiraissue i on
i.id=w.issueid
inner join
customfieldvalue c on c.id=i.id
inner join cwd_user u on
u.user_name=w.author
where startdate >
'2015-07-01' 

OUTPUT:


2.00;"User1";"Task1";10201;"analiza_11.05";"2015-07-16"


1.00;"User2";"Task2";10000;"";"2015-07-16"


4.00;"User3";"Task3";10216;"0.0.1.2";"2015-07-15"

 

and as you can see value for Account is empty - and this is all I need to have ready to go report with optimal generation time.

 

I'll be grateful for your help.

 

Adam

0 votes
Pablo Beltran Nov 08, 2015

You could get it with the SQL for JIRA plugin. A query summarizing the timespent for each account (custom field) would be similar to:

 

select 
	cf.account as "Account", 
	sum(w.timespent) as "Time Spent" 
from 
	issues i 
	inner join issuecustomfields cf on cf.issueid=i.id 
	inner join issueworklogs w on w.issueid=i.id group by cf.account 
where JQL='...'

 

 

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Marketplace Apps & Integrations

Demo Den Ep. 4: Continuous Integration with CircleCI & Jira Software

Get ready! Demo Den Episode 4 is coming your way on Tuesday, May 28, 2018 with a Continuous Integration and Delivery special demo. CircleCI Director of Solutions Engineering, Eddie Webb will show us ...

454 views 0 4
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you