Database Query: find out if a watcher was added to an issue at a certain date/time

[Deleted user]

Hi!

I was asked to find out, if a watcher was added to an issue at a certain date/time. It isn't recorded in the Issue History Tab...maybe via database query?

Cheers!

Sebastian

[Udo Brand · Accepted solution]

Hi Sebastian,

try this:

select pkey, cwd_user.user_name, userassociation.created watcher_since 
from userassociation, cwd_user, jiraissue
where cwd_user.lower_user_name = '<UserName>'
and jiraissue.pkey='<YourIssueKey>' --XXX-123
and jiraissue.id = userassociation.sink_node_id
and userassociation.association_type='WatchIssue'
and userassociation.sink_node_entity ='Issue'
and userassociation.source_name = cwd_user.user_name;

[Deleted user]

Hi Udo!

That works, thank you!

Regards,

Sebastian

[Philip Schlesinger]

When I tried that, it didn't show any values under pkey.  Here's my modification to Udo's SQL:

select 
  concat(project.pkey,'-',jiraissue.issuenum) as pkey_issuenum,
  cwd_user.user_name,
  userassociation.created watcher_since 
from 
  userassociation
join 
  cwd_user 
on 
  userassociation.source_name = cwd_user.user_name
join 
  jiraissue 
on 
  jiraissue.ID = userassociation.SINK_NODE_ID
join
  project 
on 
  project.id = jiraissue.PROJECT
where 
  jiraissue.id = userassociation.sink_node_id
and 
  userassociation.association_type='WatchIssue'
and 
  userassociation.sink_node_entity ='Issue'
ORDER BY 
  `cwd_user`.`user_name`  ASC

[Viacheslav Starovoytov]

Hi Philip, Hi Udo!

Is there a way to find exactly who and when made changes at the watchers list?

With regards
Vyacheslav

[Karlis Vesters]

Updated version of SQL - it is more correct in some cases

SELECT DISTINCT CONCAT(project.pkey,'-',jiraissue.issuenum) AS pkey_issuenum,
cwd_user.user_name,
userassociation.created watcher_since
-- ,userassociation.*
FROM userassociation
JOIN app_user ON app_user.user_key = userassociation.source_name
JOIN cwd_user ON app_user.lower_user_name = cwd_user.lower_user_name
JOIN jiraissue ON jiraissue.ID = userassociation.SINK_NODE_ID
JOIN project ON project.id = jiraissue.PROJECT
WHERE 
userassociation.association_type='WatchIssue' 
AND userassociation.sink_node_entity ='Issue'
-- and project.pkey = 'XXX' AND jiraissue.issuenum = 1111
ORDER BY 
`cwd_user`.`user_name` ASC

[Jonathan Hult]

Also see this issue: https://jira.atlassian.com/browse/JRA-61600