Find Last Login Time for WordPress Users in the SQL Database

Question: What are the timestamps for the last login for every WordPress user in this WordPress site?

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_name
Enter password: some_password

The last login for every user is found within their last session token, which is stored in table wp_usermeta with a key called session_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 and i: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

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

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.