Here is a stored procedure I am using for a Dotnetnuke custom authentication provider that I use to get the number of failed login attempts for user.

create PROCEDURE [dbo].[GetFailedPasswordAttemptsUsername]

@Username nvarchar(100)
AS select aspnet_Membership.FailedPasswordAttemptCountfrom aspnet_Usersleft join aspnet_Membership

on aspnet_Membership.UserID = aspnet_Users.UserID

where aspnet_Users.username = @username