Correlating log entries, such as failed login attempts efficiently

I am looking for an efficient way to identify account brute forcing.
My log database contains authentication log entries. Each entry has:

  • time stamp
  • username
  • IP address
  • login attempt results (success / fail )

I want to produce a report which indicates that which logins have been attacked. Attacked is defined as: unsuccessful login attempts not followed by a successful login attempt within N minutes (e.g. 10) from same IP address. The test cases are:

  • Unable to run an XMLQuery on a XML statement
  • Update fails after repeating deadlocked query in pymssql
  • wpf : how to show a data set in a grid view?
  • CAST vs ssis data flow implicit conversion difference
  • MySQL REGEXP to SQL Server
  • Select data in date format
    • user/ip combo has attempted to login unsuccessfully twice and succeeded on third time (no attack)
    • user/ip combo has attempted to login unsuccessfully twice and succeeded on third time, while same user, but different ip has tried to unsuccessfully log in (attack on second user/ip combo)

    I can imagine one solution with O(n*log(N)) solution: a cursor goes over each record and then does lookups with another cursor for later records to determine activity. Quite inefficient.

    DB doesn’t matter: SQL, MySQL, nosql, etc as data can be converted easily.

  • Exporting large data into CSV from sqlserver using java
  • some queries regarding updates/inserts rate limits for a database(SQL-based or NoSQL based)
  • CQRS: write to RDBMS, read from NoSQL?
  • Using SqlBulkCopy with MongoDB
  • mongo db to sql conversion 1
  • How to model a Recurring Query based calendar with dependencies on resources like recrurring tasks and timekeeping
  • 2 Solutions collect form web for “Correlating log entries, such as failed login attempts efficiently”

    Group log items by 5min time intervals. For all groups which exceed half your alarming thresholds perform a more expensive but entirely correct check.

    That will probably filter out almost all log items which are not a real attack. And a grouping operation is easy to program and quick to execute.

    Depending on how much work you’re willing to spend on this Complex Event Processing could be an option. Theres frameworks such as Esper if you do Java.

    The idea is to create an event stream based on your server logs (or SQL result) and have Esper check for correlations. See example query.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.