Dashboard widget – Top 5 failed logins – Existing Users column

I was puzzled by the “Top 5 failed logins” report on the dashboard widget, where the “Existing Users” column was filled with “Yes”, although the users did not exist in the WordPress users table. I also found there were several threads on this forum, on the same subject.

Next step: I checked the “New User Registration” emails and then I could see that those users really DID register at some moment on our site and were deleted shortly afterwards by the admin, when he made sure they were spammers.

Yet the “Existing User = YES” was rather confusing, so I dug a little in the plugin’s code and found a possible solution (already tested on our site).

Here it is: in the lib\wfActivityReport.php file I changed the SQL query from the public function getTopFailedLogins (around line #255), so that now it includes a left join with the WordPress users table:

SELECT l.*, SUM(l.fail) AS fail_count, MAX( COALESCE( u.ID, 0 ) ) AS is_valid_user
FROM {$this->db->base_prefix}wfLogins l
LEFT JOIN {$this->db->base_prefix}users u ON l.userID = u.ID
WHERE l.fail = 1 AND l.ctime > $interval
GROUP BY l.username
ORDER BY fail_count DESC
LIMIT %d

Hope that helps.

https://www.ads-software.com/plugins/wordfence/