Detect Consecutive Login Failure

We have an application and we have security rule that if a user fails to log in 3 times consecutively, the account should be locked. Our requirement to get the list of the user which has consecutive login failure and when the lock condition is met.

For the Demo we are doing setup as below.

Creating a table

CREATE TABLE LoginAudit (     UserName     VARCHAR(50),     LoginTime    DATETIME,     LoginStatus  VARCHAR(10)   -- SUCCESS / FAIL );   --Inserting few records INSERT INTO LoginAudit VALUES ('Amit', '2025-01-01 09:00', 'SUCCESS'), ('Amit', '2025-01-01 09:05', 'SUCCESS'), ('Amit', '2025-01-01 09:10', 'SUCCESS'), ('Amit', '2025-01-01 09:15', 'SUCCESS'), ('Amit', '2025-01-01 09:20', 'SUCCESS'), ('Ravi', '2025-01-01 10:00', 'FAIL'), ('Ravi', '2025-01-01 10:05', 'SUCCESS'), ('Ravi', '2025-01-01 10:10', 'FAIL'), ('Ravi', '2025-01-01 10:15', 'FAIL'), ('Bagesh', '2025-01-01 09:00', 'FAIL'), ('Bagesh', '2025-01-01 09:05', 'SUCCESS'), ('Bagesh', '2025-01-01 09:10', 'FAIL'), ('Bagesh', '2025-01-01 09:15', 'FAIL'), ('Bagesh', '2025-01-01 09:20', 'FAIL'), ('Nagesh', '2025-01-01 09:00', 'FAIL'), ('Nagesh', '2025-01-01 09:05', 'SUCCESS'), ('Nagesh', '2025-01-01 09:10', 'FAIL'), ('Nagesh', '2025-01-01 09:15', 'FAIL'), ('Nagesh', '2025-01-01 09:20', 'FAIL')

See the records into the table.

Here see Bagesh. Myself having poor memory and I had entered 3 times wrong password. We need to find out that the employee like me who have fail to login to the application.

WITH cte      AS (SELECT *,                 Sum(CASE                       WHEN loginstatus = 'SUCCESS' THEN 1                       ELSE 0                     END)                   OVER (                     partition BY username                     ORDER BY logintime) AS grp          FROM   loginaudit),      fail_groups      AS (SELECT username,                 grp,                 Count(*)       AS ConsecutiveFails,                 Min(logintime) AS FirstFailTime,                 Max(logintime) AS LastFailTime          FROM   cte          WHERE  loginstatus = 'FAIL'          GROUP  BY username,                    grp) SELECT username,ConsecutiveFails,FirstFailTime,LastFailTime FROM   fail_groups WHERE  consecutivefails >= 3;

 

In this way we will detect users who are failing login attempts continuously and decide when an account should be automatically locked using SQL.