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

[Adam Alker]

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

[Pablo Beltran]

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='...'

[Adam Alker]

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

[Susanne Götz [Tempo]]

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