Failed and Successful Login Auditing Using xp_readerrorlog

  • Comments posted to this topic are about the item Failed and Successful Login Auditing Using xp_readerrorlog

  • Thanks for the script.

  • Ryan great script thank you.

    Would it be possible to provide a script that writes to tables (not Temp) within a database call ZZZ (as an example ?)

    For me the ideal would be to run this often during the day and retain the table contents without overwriting but adding Failed attempts count.

    Then extract details from the tables when I need to review login details.

    Then once a month truncate the table and start to load with fresh data for the next month ?

    Is this something you could help with ?

  • Although submitted as an ad hoc script with no permanent tables, I actually use it in my own environment to send previous months aggregated login data into permanent tables on my "ServerAudit" database.

    The current script is setup to give you monthly aggregated data, but it sounds like you are looking for daily aggregates. Edit your @FirstDay and @LastDay variables.


    @LastDay DATETIME

    SET @FirstDay = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, 0); --Edit start date here, default first day of previous month

    SET @LastDay = GETDATE(); --Edit end date of log pull here

    To something like this:


    @LastDay DATETIME

    SET @FirstDay = DateAdd(DD, DateDiff(DD, 0, GETDATE())-1, 0)

    SET @LastDay = DateAdd(DD, DateDiff(DD, 0, GETDATE()), 0)

    Leave the #TempLog tables, just edit the SELECT statements from those temp tables into INSERT INTO statements to your permanent tables. You could then put the code into an agent job to load previous days data just after midnight.

  • Hello Ryan, thanks for script.

    I keep getting an error Invalid length parameter passed to the LEFT or SUBSTRING function and its for the first substring line the script i.e. SUBSTRING(TEXT,(CHARINDEX('''',TEXT)+1),(CHARINDEX('''',TEXT,24)-(CHARINDEX('''',TEXT)+1))) AS [User]. What is the purpose of this line and why does it error out for me? Will appreciate if anyone can help me with this.



  • The purpose is to pull the user out of the text string in a friendly readable format. If you are getting an error here, then you have an oddball row in your log that the query is not accounting for. You would have to isolate this row and add an exclusion.

  • Thanks for the extra comments/code.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply