Search through SQL Server Error Logs

Question

Hello, I have SQL Server 2014 and within Server Properties -> Security -> Login auditing -> Both failed and successful logins enabled.

I have its Management -> SQL Server Logs -> Configure SQL Server Error Logs set to 31 (about 1 month worth)

A user has a pretty long list around over 400 Logins and would like to know if there had been any login attempts for each of them.

Is there a script or a way to query the SQL Server Error Logs and copy/paste all 400+ Logins in an 'IN ('Login1', 'Login2', Login3', etc...' or 'WHERE' clause?

(I can write a simple formula in Excel to format the 'IN' or 'WHERE' clause with all of the 400+ Logins and then copy/paste into SQL query)

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

You can try to use below T-SQL to find logins. Below example is to find logins last 7 days. If you want to go back further than 7 days, you change the 7 values below to match your needs.

CREATE PROC usp_GetLoginsListFromLastWeek  
AS  
BEGIN  
   SET NOCOUNT ON  
  
   DECLARE @ErrorLogCount INT   
   DECLARE @LastLogDate DATETIME  
  
   DECLARE @ErrorLogInfo TABLE (  
       LogDate DATETIME  
      ,ProcessInfo NVARCHAR (50)  
      ,[Text] NVARCHAR (MAX)  
      )  
     
   DECLARE @EnumErrorLogs TABLE (  
       [Archive#] INT  
      ,[Date] DATETIME  
      ,LogFileSizeMB INT  
      )  
  
   INSERT INTO @EnumErrorLogs  
   EXEC sp_enumerrorlogs  
  
   SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])  
   FROM @EnumErrorLogs  
  
   WHILE @ErrorLogCount IS NOT NULL  
   BEGIN  
  
      INSERT INTO @ErrorLogInfo  
      EXEC sp_readerrorlog @ErrorLogCount  
  
      SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])  
      FROM @EnumErrorLogs  
      WHERE [Archive#] > @ErrorLogCount  
      AND @LastLogDate > getdate() - 7   
    
   END  
  
   -- List all last week logins count of attempts   
   SELECT COUNT (TEXT) AS NumberOfAttempts, TEXT AS Details, MIN(LogDate) as MinLogDate, MAX(LogDate) as MaxLogDate  
   FROM @ErrorLogInfo  
   WHERE ProcessInfo = 'Logon'  
      AND LogDate > getdate() - 7  
   GROUP BY TEXT  
   ORDER BY NumberOfAttempts DESC  
  
   SET NOCOUNT OFF  
END                      

We execute the procedure:

exec usp_GetLoginsListFromLastWeek  

Drop the procedure after getting the information.

DROP PROCEDURE usp_GetLoginsListFromLastWeek;    
GO  

Please refer to this third blog to get more explain about this query.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Maybe this blog post will give you support for further investigation.

https://www.sqlshack.com/read-sql-server-error-logs-using-the-xp_readerrorlog-command/

Maybe you can load those logs into a temp-table and query that one for your required information.

Answer 3

Thanks everyone for the replies and Cathyji-msft nice Stored Proc script.