Deleting inactive users crowd

[Vegard Haneberg]

I would like to delete the five users who have been inactive the longest i Crowd. Is there any way to see wich users that have been inactive the longest?

[Craig Castle-Mead]

Hi @Vegard Haneberg 

if you have access to the database behind Crowd, you can do a left join between cwd_user and cwd_user_attribute 

something like this should work (tweaked on my phone from a more complicated query, so may need to be tweaked, and for psql so if you’re running my/ms/oracle may need to be adjusted).

SELECT

  u.lower_user_name AS username,

 FROM_UNIXTIME(ua.attribute_lower_value/1000) AS lastLogin

FROM

  cwd_user AS u

    LEFT JOIN cwd_user_attribute AS ua ON ua.user_id = u.id AND attribute_name = 'lastAuthenticated'

ORDER BY

  ua.attribute_lower_value, u.lower_user_name;

CCM