Skip to content

Instantly share code, notes, and snippets.

@schuhwerk
Last active April 5, 2022 10:05
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save schuhwerk/850289927ffbe31dd4307e4c201514ac to your computer and use it in GitHub Desktop.
Save schuhwerk/850289927ffbe31dd4307e4c201514ac to your computer and use it in GitHub Desktop.
Get the login-times of users (no plugin required).
/*
* 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