Failed and Successful Login Auditing Using xp_readerrorlog

  • Velveeta22

    Ten Centuries

    Points: 1289

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • MickyD

    SSCarpal Tunnel

    Points: 4568

    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 ?

  • Velveeta22

    Ten Centuries

    Points: 1289

    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.

    DECLARE @FirstDay DATETIME,

    @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:

    DECLARE @FirstDay DATETIME,

    @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.

  • amirmir

    SSC Eights!

    Points: 831

    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.

    Regards

    Amir

  • Velveeta22

    Ten Centuries

    Points: 1289

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the extra comments/code.

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

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