Sometimes there`s a need to bulk move worklogs from one issue to another. If we`re talking about couple of them, you can easily do the transfer manually. But when they come in dozens it can take a lot of time. Here I`m providing you the bash script which can do all the work for you.
Since Jira nor Tempo Timesheets don`t provide such function, bulk moving worklogs can be achieved in a database only. You can still vote for this idea, hoping it`s gonna be implemented one day. You can also find some plugins that do the work, but they`re not free. Following script was written by me to use on PostgreSQL database, but you can easily adjust it to different database engines. Let`s move on.
Important notes
– Jira should be turned off before applying any changes in a database
– Script should be run by the user that has database access (i.e. postgres)
– You should run the script on a test environment first
– Script was tested on PostgreSQL database only
– You should have Tempo Timesheets app installed and turned on
What the script does
The script is basically moving worklogs between issues by changing issueid value in the worklog table for a given worklog id. You can find worklogs you want to migrate by providing parameters such as source_issue_key, updated_date, author, worklog_body and time_worked. Lastly you need to provide target_issue_key and you`re ready to migrate. Before applying changes script will summarize them and ask for your confirmation. The code itself could be much shorter but I provided some output for your comfort.
You can read the script explanation below and/or watch the video that contains some migration examples. Whole script can be found in my repository. I have put some comments there but If you have any question on its use, please don`t hesitate to ask: contact@jiraforthepeople.com.
And here comes the code
First you need to provide some parameters of worklogs that you want to migrate. SOURCE_ISSUE_KEY and TARGET_ISSUE_KEY are mandatory. If you don`t specify any other arguments script will move all worklogs from on issue to another.
#Optional parameters UPDATED_DATE="" #worklogs updated date, you need to provide operator and enclose date in single quotes. CREATED_DATE="" #worklogs created date, you need to provide operator and enclose date in single quotes. START_DATE="<='2020-12-31'" #worklogs start date, you need to provide operator and enclose date in single quotes. AUTHOR="" #login of the worklog author. WORKLOG_BODY="" #string that have to be in a worklog body TIME_WORKED="" #value in seconds UPDATE_REMAINING_TIME=false #set to true if you want Remaining Time Estimate (Remaining) to be updated, default to false
Both SOURCE_ISSUE_KEY and TARGET_ISSUE_KEY needs to be translated to issue id. In order to do it script:
– splits the issuekey (i.e. TEST-123) to project key (TEST) and issue number (123),
– use the project key to get the project id
– use the project id and issue number to get the issue id
issue_id() {
local ISSUE_KEY=$1
if [ ${#1} -gt 0 ]
then
local PROJECT_KEY=$(echo "$ISSUE_KEY" | cut -d "-" -f 1)
local ISSUE_NUM=$(echo "$ISSUE_KEY" | cut -d "-" -f 2)
#getting target project id
local PQUERY="SELECT id from project where pkey = '$PROJECT_KEY';"
local PROJECT_ID=$(psql jira -XAqt -c "$PQUERY" 2> /dev/null)
#getting target issue id
local IQUERY="SELECT id from jiraissue where project = $PROJECT_ID and issuenum = $ISSUE_NUM;"
local ISSUE_ID=$(psql jira -XAqt -c "$IQUERY" 2> /dev/null)
#checking if issue with provided key existst
if [ ${#ISSUE_ID} -eq 0 ]
then
echo "No issue found with key $1"
exit 1
fi
echo $ISSUE_ID
else
echo "you need to pass TARGET_ISSUE_KEY"
exit 1
fi
}
#checking if the target issue was provided, if so, it needs to be translated to issue id
TARGET_ISSUE_ID="$(issue_id $TARGET_ISSUE_KEY)"
SOURCE_ISSUE_ID="$(issue_id $SOURCE_ISSUE_KEY)"
Then we need to construct the whole query based on the parameters provided.
#constructing query for getting worklog ids to migrate
PARAMS=""
PARAMS+=" worklog.issueid = $SOURCE_ISSUE_ID "
if [ ${#UPDATED_DATE} -gt 0 ]
then
PARAMS+=" and worklog.updated $UPDATED_DATE "
fi
if [ ${#CREATED_DATE} -gt 0 ]
then
PARAMS+=" and worklog.created $CREATED_DATE "
fi
if [ ${#START_DATE} -gt 0 ]
then
PARAMS+=" and worklog.startdate $START_DATE "
fi
if [ ${#AUTHOR} -gt 0 ]
then
PARAMS+=" and worklog.author = '$AUTHOR' "
fi
if [ ${#WORKLOG_BODY} -gt 0 ]
then
PARAMS+=" and worklog.worklogbody $WORKLOG_BODY "
fi
if [ ${#TIME_WORKED} -gt 0 ]
then
PARAMS+=" and worklog.timeworked $TIME_WORKED "
fi
Now it`s time to check if there are worklogs to migrate.
CHECK_QUERY="SELECT worklog.id from worklog INNER JOIN jiraissue on worklog.issueid = jiraissue.id INNER JOIN project on project.id = jiraissue.project where $PARAMS;"
CHECK=$(psql jira -XAqt -c "$CHECK_QUERY")
if [ -z "$CHECK" ]
then
echo There are no worklogs to migrate
exit 1
fi
If you also want to update Remaining Estimate value in a source and a target issue script will sum up the values for you and count the new ones.
#TIMEWORKED SUM IN A SOURCE ISSUE
TIMEWORKED_QUERY="SELECT worklog.timeworked from worklog INNER JOIN jiraissue on worklog.issueid = jiraissue.id where $PARAMS;"
TIMEWORKED_QUERY_EXECUTION=$(psql jira -XAqt -c "$TIMEWORKED_QUERY")
TIMEWORKED_ARR=($TIMEWORKED_QUERY_EXECUTION)
for t in "${TIMEWORKED_ARR[@]}"; do
TIMEWORKED_SUM=$((TIMEWORKED_SUM+t))
done
#TIMESPENT SUM IN A SOURCE ISSUE
TIMESPENT_SOURCE_QUERY="SELECT timespent from jiraissue where id = $SOURCE_ISSUE_ID;"
TIMESPENT_SOURCE_SUM=$(psql jira -XAqt -c "$TIMESPENT_SOURCE_QUERY")
NEW_TIMESPENT_SOURCE_SUM=$(($TIMESPENT_SOURCE_SUM-$TIMEWORKED_SUM))
#TIMESPENT SUM IN A TARGET ISSUE
TIMESPENT_TARGET_QUERY="SELECT timespent from jiraissue where id = $TARGET_ISSUE_ID;"
TIMESPENT_TARGET_SUM=$(psql jira -XAqt -c "$TIMESPENT_TARGET_QUERY")
NEW_TIMESPENT_TARGET_SUM=$(($TIMESPENT_TARGET_SUM+$TIMEWORKED_SUM))
if [ $UPDATE_REMAINING_TIME = true ]
then
TIMEESTIMATE_SOURCE_QUERY="SELECT timeestimate from jiraissue where id = $SOURCE_ISSUE_ID;"
TIMEESTIMATE_QUERY_SOURCE_EXECUTION=$(psql jira -XAqt -c "$TIMEESTIMATE_SOURCE_QUERY")
if [ -z "$TIMEESTIMATE_QUERY_SOURCE_EXECUTION" ]
then
TIMEESTIMATE_QUERY_SOURCE_EXECUTION=0
fi
NEW_TIMEESTIMATE_SOURCE_SUM=$(($TIMEESTIMATE_QUERY_SOURCE_EXECUTION+$TIMEWORKED_SUM))
TIMEESTIMATE_TARGET_QUERY="SELECT timeestimate from jiraissue where id = $TARGET_ISSUE_ID;"
TIMEESTIMATE_QUERY_TARGET_EXECUTION=$(psql jira -XAqt -c "$TIMEESTIMATE_TARGET_QUERY")
if [ -z "$TIMEESTIMATE_QUERY_TARGET_EXECUTION" ]
then
TIMEESTIMATE_QUERY_TARGET_EXECUTION=0
fi
NEW_TIMEESTIMATE_TARGET_SUM=$(($TIMEESTIMATE_QUERY_TARGET_EXECUTION-$TIMEWORKED_SUM))
#if new time estimate is less than 0, it stays at 0 value
if [ $NEW_TIMEESTIMATE_TARGET_SUM -lt 0 ]
then
NEW_TIMEESTIMATE_TARGET_SUM=0
fi
fi
Finally, here comes the summary of the operation, waiting for you to confirm.
#confirmation: script summarizes changes before applying them and waits for your confirmation
echo "Worklog params: $PARAMS"
TQUERY="SELECT worklog.id as worklog_id,worklog.issueid as issue_id,worklog.author as worklog_author,worklog.startdate::date as start_date,worklog.updated::date as updated_date,worklog.created::date as created_date,worklog.timeworked,concat(project.pkey, '-', jiraissue.issuenum) as issuekey from worklog INNER JOIN jiraissue on worklog.issueid = jiraissue.id INNER JOIN project on project.id = jiraissue.project where $PARAMS;"
CONFIRMATION_QUERY=$(psql jira -XAq -c "$TQUERY")
echo "Following worklog entries will be moved from $SOURCE_ISSUE_KEY to $TARGET_ISSUE_KEY."
#converting query to array which makes output more readable
CONF_ARR=($CONFIRMATION_QUERY)
for w in "${CONF_ARR[@]}"; do
echo $w
done
read -p "Do you want to move worklogs from $SOURCE_ISSUE_KEY to $TARGET_ISSUE_KEY (y/n)?" -n 1 -r
And the migration itself.
if [[ $REPLY =~ ^[yY]$ ]]
#getting worklogs ids after your confirmation
then
#updating source issue timespent
TIMESPENT_SOURCE_UPDATE_QUERY="UPDATE jiraissue set timespent = $NEW_TIMESPENT_SOURCE_SUM where id = $SOURCE_ISSUE_ID"
psql jira -XAqt -c "$TIMESPENT_SOURCE_UPDATE_QUERY" 2> /dev/null
#updating target issue timespent
TIMESPENT_TARGET_UPDATE_QUERY="UPDATE jiraissue set timespent = $NEW_TIMESPENT_TARGET_SUM where id = $TARGET_ISSUE_ID"
psql jira -XAqt -c "$TIMESPENT_TARGET_UPDATE_QUERY" 2> /dev/null
#updating remaining timeestimate
if [ $UPDATE_REMAINING_TIME = true ]
then
#updating target issue remaining estimate
TIMEESTIMATE_TARGET_UPDATE_QUERY="UPDATE jiraissue set timeestimate = $NEW_TIMEESTIMATE_TARGET_SUM where id = $TARGET_ISSUE_ID"
psql jira -XAqt -c "$TIMEESTIMATE_TARGET_UPDATE_QUERY" 2> /dev/null
#updating source issue remaining estimate
TIMEESTIMATE_SOURCE_UPDATE_QUERY="UPDATE jiraissue set timeestimate = $NEW_TIMEESTIMATE_SOURCE_SUM where id = $SOURCE_ISSUE_ID"
psql jira -XAqt -c "$TIMEESTIMATE_SOURCE_UPDATE_QUERY" 2> /dev/null
fi
WQUERY="SELECT id from worklog where $PARAMS;"
WORKLOG_IDS=$(psql jira -XAqt -c "$WQUERY" 2> /dev/null)
WORKLOGS_ARR=($WORKLOG_IDS)
#moving worklogs to another issue
for w in "${WORKLOGS_ARR[@]}"; do
UQUERY="UPDATE worklog set issueid = $TARGET_ISSUE_ID where id = $w;"
psql jira -XAqt -c "$UQUERY" 2> /dev/null
done
echo "Worklogs migration complete!"
fi