Tempo Worklog Attribute SQL Query

[Alexey Suslin]

I want to select through MS SQL checkbox attribute of Tempo Worklog (It's called Overtime)

This is how it works before

SELECT 
W.ID
,[IsOvertime] = CONVERT(bit, IIF(PS2.propertyvalue LIKE '_Overtime_=_Overtime_', 1, 0))
FROM jira.worklog W
LEFT JOIN jira.jiraissue JI ON JI.ID = W.issueid
LEFT JOIN jira.propertyentry PE2 ON PE2.ENTITY_NAME = 'Tempo.Worklog' AND PE2.PROPERTY_KEY = 'Tempo.WorklogAttributes' AND PE2.ENTITY_ID = W.ID AND PE2.propertytype=5
LEFT JOIN jira.propertystring PS2 ON PS2.ID = PE2.ID 

I don't remember the date it has changed... probably Tempo ver 8 or earlier, however right now it's not working and I cannot find where Tempo stores attributes of worklog.

Please suggest

[Susanne Götz _Tempo_ · Accepted solution]

Hi @Alexey Suslin

The work attribute information is no longer stored in the JIRA properties tables but has been migrated to AO tables. 

The tables that are now in use are:

AO_013613_WORK_ATTRIBUTE  which stores information about the work attributes configured 

AO_013613_WA_SL_VALUE  which stores the options that have been configured in a work attribute of type Static List

AO_013613_WA_VALUE which stores the information which work attribute is linked to a worklog.

Information about all database tables that are added when Tempo is installed can be found in JIRA Administration, System, Plugin Data Storage.

Best regards,
Susanne Götz
Tempo team 

[Alexey Suslin · Accepted solution]

JOIN [dbo].[AO_013613_WA_VALUE] AOWAV ON AOWAV.WORKLOG_ID = W.ID
JOIN [dbo].[AO_013613_WORK_ATTRIBUTE] AOWA ON AOWA.ID = AOWAV.WORK_ATTRIBUTE_ID