Last active
April 5, 2022 10:05
-
-
Save schuhwerk/850289927ffbe31dd4307e4c201514ac to your computer and use it in GitHub Desktop.
Get the login-times of users (no plugin required).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* Get last login time of WordPress users. | |
* WordPress stores the last time a user logged in in a serialized array in the usermeta - table. | |
* Your prefix might be different (xyz_usermeta instead of wp_usermeta). | |
* @see https://shocksolution.com/2019/04/16/find-last-login-time-for-wordpress-users-in-the-sql-database/ | |
*/ | |
select | |
wu.user_login, | |
metatable.*, | |
FROM_UNIXTIME(metatable.login_time) | |
from | |
( | |
select | |
user_id, | |
REGEXP_REPLACE( | |
meta_value, | |
'.*((?<=login";i:)([0-9]*)|:).*', | |
'\\2' | |
) as login_time -- extract 'login' from serialized array (if exists) | |
from wp_usermeta | |
where meta_key = 'session_tokens' -- session_tokens is a serialized array. | |
) metatable -- subquery, so we can use the extractet login_time. | |
left join wp_users as wu on wu.ID = metatable.user_id | |
where metatable.login_time > 0 -- only users with a login time | |
ORDER BY metatable.login_time ASC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment