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
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;
Hi Udo!
That works, thank you!
Regards,
Sebastian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Philip, Hi Udo!
Is there a way to find exactly who and when made changes at the watchers list?
With regards
Vyacheslav
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.