Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Deactivate users not logged in for the last year

Lucas Molenaar March 25, 2015

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

1 answer

1 accepted

2 votes
Answer accepted
Lucas Molenaar
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.
April 13, 2015

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)
Dhirendra_singh
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
December 27, 2019

Hi Lucas Molenaar,

Do last script help to fee up the license ?

It is disabling user and user unable to login after running / updating script but license is same for me.

Please see the below script an help if i need to update query to free up the license?

 

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) AND user_name in ('rthirutn','arokiadk','p-agillan','sboppa2b');

 

Regards,

Dhirendra

Like Markus likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events