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

Deleted user May 22, 2014

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

3 answers

1 accepted

0 votes
Answer accepted
Udo Brand
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 22, 2014

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 May 22, 2014

Hi Udo!

That works, thank you!

Regards,

Sebastian

2 votes
Philip Schlesinger November 9, 2015

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 May 31, 2017

Hi Philip, Hi Udo!

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

With regards
Vyacheslav

 

Like John Cobbs likes this
Karlis Vesters January 6, 2020

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
1 vote
Jonathan Hult January 18, 2017

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events