It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

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

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

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you