Find Last Login Time for Wordpress Users in the SQL Database

April 16, 2019

Most answers say to install some plugin, and there are dozens that will show the date and time when each Wordpress user last logged in. However, plugins are a problem, because they always need to be kept up to date, and many plugins get abandoned. Now, you have a potential security problem. Also, this approach assumes that your Wordpress front-end is up and running; if your site has been hacked or accidentally broken, you need to go into the database to do some forensics.

If you are a developer or sysadmin, it’s a lot easier to get the user’s last login from the SQL database. Unfortunately, I couldn’t find documentation for that anywhere, so I made some. First, look up the database name and credentials in the file wp-config.php in the root directory of your Wordpress installation. If you don’t understand this step, stop reading; this post is not for you. Look for the following statements:

define('DB_NAME', 'database_name');
define('DB_USER', 'some_user');
define('DB_PASSWORD', 'some_password');

Log into the SQL server (typically MySQL) with the information found in the previous step:

# mysql -u user_name -p database_nameEnter password: some_password

The last login for every user is found within their last session token, which is stored in table wp_usermetawith a key calledsession_tokens.

select user_id,meta_value from wp_usermeta where meta_key='session_tokens';

Alternatively, if you want to look up just one user, find their ID from the wp_user table and then add it to the where clause:

select user_id,meta_value from wp_usermeta where meta_key='session_tokens' and user_id=1;

The session token looks a bit like JSON, but seems to actually be a Wordpress-specific string representation of a nested data structure. In the database, the string has no whitespace, so I’ve created a pretty-printed sample below:

a:1:{     s:64:"64 character pseudorandom token goes here";     a:4:{         s:10:"expiration";         i:1555596230;         s:2:"ip";         s:10:"192.0.2.10";         s:2:"ua";         s:121:"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.103 Safari/537.36";         s:5:"login";         i:1555423430;     } }

The format seems to be <type code><optional length><content>. At the top level, a:1 indicates a data structure with one field. Below that, s:64 indicates a string with 64 characters, and a:4 indicates a data structure with four fields. Within that data structure, each field has a “key” string and a “value” which may be a string or an integer. s:5:“login”;is the key for the last login timestamp andi:1555423430; is the value as a UNIX epoch timestamp. Copy the integer and convert it to a human-readable date. You can paste the value into an online converter like <https://<www.epochconverter.com/» or just run the following in MySQL:

select from_unixtime(1555423430);

Table wp_usermeta is indexed by user ID. To find out which human corresponds to ID 1, run this query:

select * from wp_users where ID=1;

Note that the apparent IP address of the last login is also stored, which can be helpful for determining whether you’ve been hacked. If a user based in the US recently logged in with an IP from kyrgyzstan, it’s probably malicious.

References

https://usersinsights.com/wordpress-user-database-tables/

Comments

Comment #1 by Baker

Baker - Mar 3, 2020

“The session token looks a bit like JSON, but seems to actually be a WordPress-specific string representation”. No, is PHP serialize() function. You can convert it to an array using unserialize().

Comment #2 by isak

isak - May 5, 2020

Is there a way to modify this to show users who have NEVER logged in? Thanks.

Comment #3 by Bojan

Bojan - Jun 5, 2020

This is a query to get the last login time from database: SELECT user_id, FROM_UNIXTIME(substring_index(substring_index(meta_value, ‘“login”;i:’, -1), ‘;}}’, 1)) FROM wp_usermeta WHERE meta_key=‘session_tokens’ AND user_id=1

Comment #4 by locus

locus - Jun 1, 2020

Unexpected character. (near “{” at position 77) Unexpected character. (near “}” at position 83)

Comment #5 by Steve Smith

Steve Smith - Sep 3, 2020

Not all of my users that have logged in have a record for a meta_key called session_token in the usermeta table. Why could that be?

Comment #6 by Isaac Zhao

Isaac Zhao - Nov 1, 2020

Inspired by author’s great insight, I wrote a php script to list all users’ last login date. list_last_login.php: (put at root directory of WordPress) include ‘wp-load.php’; $sSql = “SELECT m.user_id, u.user_login, m.meta_value FROM {$wpdb->usermeta} m, {$wpdb->users} u WHERE meta_key = ‘session_tokens’ AND m.user_id = u.id”; $aResults = $wpdb->get_results( $sSql ); $aFinals = array(); foreach($aResults as $oRow) { $a1 = unserialize ($oRow->meta_value); $aKey = array_keys ($a1); $iLastLoginTimeStamp = $a1[$aKey[count ($aKey)-1]][’login’]; $sLastLoginTimeStamp = date(“Y-m-d\TH:i:s”, $iLastLoginTimeStamp); $aFinals[] = [$oRow->user_id, $oRow->user_login, $sLastLoginTimeStamp]; } foreach ($aFinals as $sRow) { echo “$sRow[0], $sRow[1], $sRow[2] \n”; }

Comment #7 by Mmacneil

Mmacneil - Mar 2, 2021

Punctuation problem - make sure your single quotes are really singe quotes and the quotes are really double quotes and not ‘smart quotes’: SELECT user_id, FROM_UNIXTIME(substring_index(substring_index(meta_value, ‘“login”;i:’, -1), ‘;}}’, 1)) as Last_Login FROM kcm_usermeta m WHERE m.meta_key=‘session_tokens’;

Comment #8 by Mmacneil

Mmacneil - Mar 2, 2021

Me too, looks like nothing before Mar 2018 has a session token! My guess is it was added then unless it requires permission of some sort that you can block in your browser…would be good to know!

Comment #9 by Bhaskar

Bhaskar - Jul 4, 2023

Thank you so much. How do we run this?

Comment #10 by Bhaskar