Post reply

Failed and Successful Login Auditing Using xp_readerrorlog

  • December 15, 2015 at 5:43 pm

    #323079

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

  • December 30, 2015 at 6:32 am

    #1848724

    Thanks for the script.

  • January 6, 2016 at 7:03 am

    #1849837

    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 ?

  • January 6, 2016 at 8:40 am

    #1849880

    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.

  • February 8, 2016 at 3:03 pm

    #1857264

    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

  • February 8, 2016 at 4:04 pm

    #1857280

    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.

  • April 26, 2016 at 7:07 am

    #1874574

    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