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.
“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().
Is there a way to modify this to show users who have NEVER logged in? Thanks.
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
Unexpected character. (near “{” at position 77)
Unexpected character. (near “}” at position 83)
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’;
Thank you so much! Here is a query I wrote to get Last Login and IP Address:
SELECT user_id as ID,
substring_index(substring_index(substring_index(meta_value, ‘”ip”;s:’, -1), ‘”;s:2:”ua”‘, 1), ‘”‘, -1) as IP_Addr,
FROM_UNIXTIME(substring_index(substring_index(meta_value, ‘”login”;i:’, -1), ‘;}}’, 1)) as Last_Login
FROM wp_usermeta
WHERE meta_key=’session_tokens’;
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?
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!
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”;
}
Thank you so much. How do we run this?