How to get users who do not access jira from a certain date?I jira in mysql.

Alberto_Garcia October 25, 2016

how to get users who do not access jira from a certain date?I jira in mysql.

before bd oracle to this query:

select a.user_name, a.updated_date, a.fecha , email_address
from
(select u.user_name, u.updated_date, nvl2(l.attribute_value,to_date ('01/01/1970', 'dd/mm/yyyy')+(l.attribute_value/1000/60/60/24) ,null) fecha, email_address
from jira.cwd_user u, jira.cwd_user_attributes l
where u.id = l.user_id (+)
and l.attribute_name (+) = 'login.lastLoginMillis'
and u.active = 1) a
where (a.fecha is null or a.fecha < '31/12/2015')
and a.updated_date <'31/12/2015

2 answers

0 votes
Alberto_Garcia October 25, 2016

Thank you for your help, it works perfect!!!!

Peter Geshev
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.
October 26, 2016

Please accept the answer then, so that it is easier for others who may run into this problem later

0 votes
Peter Geshev
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.
October 25, 2016

Try the following query

SELECT u.user_name, u.email_address
FROM cwd_user u
LEFT JOIN cwd_user_attributes l ON l.user_id = u.id AND l.attribute_name = 'login.lastLoginMillis'
WHERE u.active = 1 AND (
	l.id IS NULL OR (
		TIMESTAMPDIFF(MICROSECOND, now(), '31/12/2015') &gt; (l.attribute_value) * 1000
		OR 
		TIMESTAMPDIFF(SECOND, u.updated_date, '31/12/2015') &lt; 0
	)
)

Suggest an answer

Log in or Sign up to answer