How to bulk migrate worklogs between issues

By in
How to bulk migrate worklogs between issues

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

Leave a reply

Your email address will not be published. Required fields are marked *