You're on your way to the next level! Join the Kudos program to earn points and save your progress.
Level 1: Seed
25 / 150 points
Next: Root
1 badge earned
Challenges come and go, but your rewards stay with you. Do more to earn more!
What goes around comes around! Share the love by gifting kudos to your peers.
Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!
Join now to unlock these features and more
The Atlassian Community can help you and your team get more value out of Atlassian products and practices.
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
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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.