Is there any way to deactivate a bunch of users that have not logged for some time?

[Ibrahim Dubey]

I want to save some users licenses and there is some users that have not logged for a long time.

[Pedro Souza · Accepted solution]

I know one way to achieve this on the database, I have some SQL queries that will help.

SELECT  u.user_name as "Username", 
	u.display_name as "Full Name", 
	to_timestamp(CAST(a.attribute_value as bigint)/1000) as "Last Login" 
FROM cwd_user u
	LEFT JOIN cwd_user_attributes a ON u.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date - 365 OR a.attribute_value IS NULL
ORDER BY a.attribute_value;

This query will list the users that have not logged for 365 days.

If you want to change the time interval, just edit the current_date - 365 to whichever you want.

And then, this update query will deactivate the users returned by the query above:

update cwd_user u
set active = 0
WHERE u.id in (SELECT  u2.id
FROM cwd_user u2
	LEFT JOIN cwd_user_attributes a ON u2.id = a.user_id AND attribute_name = 'login.lastLoginMillis' 
WHERE to_timestamp(CAST(a.attribute_value as bigint)/1000) <= current_date-365 OR a.attribute_value IS NULL)

Please note, we always recommend you to take a backup before running an update query because it will change values directly in the Database, so please, before running the query, take a new backup.

[Ibrahim Dubey]

Thank you for the quick response , i'll try the queries.