Using mSSQL for jira

Question

Need to disable user who have not logged in for past 2 years in jira. Please help. I need select query for this.

I used various query but I was not getting this select query result correctly.

Payne · Answer

Here is a similar query designed for MySQL, to list all users who have not logged in within the last 30 days:

SELECT display_name,email_address,FROM_UNIXTIME(left(attribute_value,char_length(attribute_value)-3)) AS 'last_successful_login'
FROM jira.cwd_user
LEFT JOIN jira.cwd_user_attributes on jira.cwd_user.ID = jira.cwd_user_attributes.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE active = 1
HAVING last_successful_login < now() - interval 30 day
ORDER BY attribute_value DESC

This won't work in MS SQL Server, but it shows how to join the tables. You may could do something like this:

SELECT display_name,email_address,attribute_value AS 'last_successful_login'
FROM jira.cwd_user
LEFT JOIN jira.cwd_user_attributes on jira.cwd_user.ID = jira.cwd_user_attributes.user_id AND attribute_name = 'login.lastLoginMillis'
WHERE active = 1 and attribute_value < 1519171200000
ORDER BY attribute_value DESC

Where 1519171200000 is the timestamp value (in milliseconds) for 2/21/18.

Jack Brickey · Answer

The way I do this is to export the customer list and use excel to find those that have not logged in since a certain date.