Is it possible to display user's first login (activation) date in a view?

Question

All the users are imported (only admin can create accounts), they receive an activation email to connect with a one-time login. What I would like to know is how to display the date when they actually came at first (first login) and/or activate their account with the account process (password changing and save)?

Apart from the basic profile augmented with a few fields, my users have a native role that is dropped when they activate their account just to unlock automatically a function. So if there is a lead on "when the role was removed or changed" that I can't figure out right now, I might work with that.

I didn't put https://www.drupal.org/project/login_history yet unfortunately.

Answer 1

There is no field that stores this information in the database.

The only thing you have by default are user account creation date, last login date and last access time.

Once a user has logged in for a second time those values are no use to you.

There are also not user revisions by default so you can't see when the first time a user changes their username/password/etc. you can only see the data the most recent change was made.

The only way is to use something like login history or user revisions, however that only helps you from now on, not for existing users.

Off the top of my head I can't think of any way you can get reliable data for this for existing users.

[EDIT]
Actually, the drupal watchdog log stores user login events.

When a user logs in this watchdog call happens:

watchdog('user', 'Session opened for %name.', array('%name' => $user->name));

So if you check your drupal logs for "Session opened for" you can find all user logins.
This will only help you if you have a record of all your watchdog logs since the beginning of your site (probably not likely).
If you only use the dblog module that logs to the drupal watchdog table it is likely you don't have all logs because old ones will usually be purged to keep the database table size under control.
If you use the syslog module to that logs to your web server's file system it is possible that you could have backups of all logs for the whole life of your site, however that also isn't guaranteed.
It's probable that old logs are also purged in this case too.