Deactivate users not logged in for the last year

[Lucas Molenaar]

I like to run a query that deactivates users that have not logged in in any of the connected applications after n-months. Could someone assist with creating a query that does this (I am useless with SQL)?

I am currently using the following query to list all the users and show the last logged in date:

SELECT cwd_user.user_name, from_unixtime(cwd_user_attribute.attribute_value/1000) FROM cwd_user, cwd_user_attribute WHERE cwd_user_attribute.user_id = cwd_user.id AND cwd_user_attribute.attribute_name = 'lastAuthenticated'

Retrieved from here: https://confluence.atlassian.com/display/CROWDKB/List+the+Last+Login+Date+for+all+Users+in+Crowd

[Lucas Molenaar · Accepted solution]

Using the SQL queries below you can search for inactive users based on last login date (first query) and set them as inactive (2nd query)

Locate inactive users based on EPOCH value (so first find out the EPOCH value of a specific date and append three zeros as the DB stores the values as ms) 

select * from cwd_user where active = 'T' and id in (select user_id from cwd_user_attribute where cwd_user_attribute.attribute_name = 'lastAuthenticated' AND cwd_user_attribute.attribute_value < 1420066800000);

Deactivate these users:

update cwd_user set active = 'F' where active = 'T' and id in (select user_id from cwd_user_attribute where cwd_user_attribute.attribute_name = 'lastAuthenticated' AND cwd_user_attribute.attribute_value < 1420066800000);

Notes:

• EPOCH calculator: link (do not forget to append three zeros)