nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

unknown

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

Accepted answers

Udo Brand

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 = '&lt;UserName&gt;'
and jiraissue.pkey='&lt;YourIssueKey&gt;' --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;

All comments

Udo Brand

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 = '&lt;UserName&gt;'
and jiraissue.pkey='&lt;YourIssueKey&gt;' --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;

unknown

Hi Udo!

That works, thank you!

Regards,

Sebastian

Philip Schlesin

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

Jonathan Hult

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

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

watcher

database