Decrease number of open tickets with assignee field set for more than two years (aka cookie licking) (March-June 2020 edition)
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Aklapper
	Jul 20 2019, 11:16 PM

Description

Problem

People assign open tasks to themselves and then do not follow up to get these tasks resolved. So these tasks stay open for years without much progress, as nobody else plans to work on a task, as it looks like someone is already working on a task. This is called "cookie licking".

Original Proposal

Send an email to assignees who have open tasks assigned to themselves for a long time, and ask them to update.
After a few weeks, send a second and final email, as a reminder.
If assignees do not react, reset the assignee field of these tasks. ~~Then email these folks again and include a link to the list of tasks that they have just been unassigned from, to still allow them reclaiming if needed/wanted.~~

Social

Is this something to discuss somewhere? Probably yes. Done via a quick survey in November 2019, see T228575#5671973 below.

Existing insufficient stuff

Existing stuff in the Phab user interface (which does not cover what we want):

On https://phabricator.wikimedia.org/maniphest/report/?order=-oldest-all , "Oldest (All)" refers to the age of the task but not for long how it has been assigned so this is not what I'm looking for.
https://phabricator.wikimedia.org/maniphest/query/tIxvk_i9KoVq/#R lists ~250 open assigned tasks that have not seen any (!, not: 'assignee') updates in the last 3 years.

Actual implementation

Pull data via SQL from DB (tasks; assignees; assignees' email addresses).
Send an email per assignee who have open tasks assigned to themselves for a long time.
- Rough script to create mbox formatted emails: https://gitlab.com/aklapper/wikimedia-maniphest-assignee-nagging/
- Get current epoch ($EPOCH1) via date +%s (for later)
- Then get tasks for the last two years (63072000 seconds; exclude tasks with Patch-For-Review): SELECT u.userName, ue.address, t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_user.user_email ue JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 63072000)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND ue.userPHID = u.phid AND ue.isPrimary = 1 AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") ORDER BY u.userName, ta.dateModified;
  - (Note: Task might have been un- and re-assigned to same person in the meantime so list might not be completely correct when it comes to sheer numbers, but that is no problem.)
- See T228575#6237124 for actual final text of that first email.
Wait a few weeks
Send a second and final email, as a reminder:
- We don't want additional tasks (and assignees) which passed that 2y threshold only between the first and this reminder email, as it is supposed to be a reminder. Hence: Use $EPOCH1 of first email (e.g. 2020-03-18 = 1584546180), get current epoch $EPOCH2 (e.g. 2020-05-20 = 1589995301), then subtract $EPOCH2-$EPOCH1 (e.g. 1589995301-1584546180), add the result to 63072000, and replace 63072000 in the initial query above by 68521121 (63072000+5449121) when re-running the query.
- See T228575#6237124 for actual final text of that second email.
Wait a few weeks
Finally unassign:
- Get current epoch ($EPOCH3) via date +%s
- Must run query again (but we only need task IDs this time), as reassignments could have happened in the meantime. (Hence, replace 63072000 again by adding the result of $EPOCH3-$EPOCH1 (1592577000-1584546180 = 8030820; 63072000+8030820 = 71102820): SELECT t.id FROM phabricator_maniphest.maniphest_task t JOIN phabricator_user.user u JOIN phabricator_maniphest.maniphest_transaction ta WHERE (ta.transactionType = "reassign" AND ta.dateModified < (UNIX_TIMESTAMP() - 71102820)) AND u.phid = SUBSTR(ta.newValue, INSTR(ta.newValue, 'PHID-USER-'), 30) AND ta.objectPHID = t.phid AND t.ownerPHID = u.phid AND (t.status = "open") AND t.phid NOT IN (SELECT e.src FROM phabricator_maniphest.edge e WHERE e.type = 41 AND e.dst = "PHID-PROJ-onnxucoedheq3jevknyr") ORDER BY ta.dateModified;
- Construct URL from task IDs like https://phabricator.wikimedia.org/maniphest/?ids=x,y,z#R. Make sure to set Page Size to a large value otherwise we only get the first 100 results.
- Mass-reset the assignee field of these tasks (and add a task comment explaining the action: This task was assigned more than two years ago. Resetting task assignee due to inactivity in order to avoid cookie-licking. Please feel free to assign this task to yourself again if you still realistically work or plan to work on this task - it would be welcome! (Two emails were sent to the address of the task assignee's user account before unassigning. See T228575 for more info or potential feedback.)).