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

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

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

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

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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published Thursday in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

321 views 0 6
Read article

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you