Select next 24 hours for every record in postgresql

Question

My Table looks like this:

| Attackerip | timestamp |

Now I want to check how many attempts per 24 hours from the first attempt each IP has made. I want to have all results with less than 5 attempts in this timespan.

For example: Attacker A has attacked at 23.05.2016 03:00PM, now the query should select the next 24 hours starting from that attempt and check if A has tried more than 5 times in this period. If A has indeed done this I want to return the Ip of A, the timespan(if that is possible) and the number of attempts.

SELECT table1.attackerip, table1.stamp::date, count(*) 
FROM thesislog_v2 table1 INNER JOIN thesislog_v2 table2 
ON table1.attackerip = table2.attackerip AND table2.stamp <= table1.stamp 
AND table2.stamp >= table1.stamp -  interval '24 hours' 
GROUP BY table1.stamp::date, table1.attackerip HAVING COUNT(*) <=5       
ORDER BY table1.attackerip asc

I am not sure whether this query does what I think it does though, because I do not know how GROUP BY with a condition works. I think it also includes Results where an attackerip has 5 attempts on this day and not only in the 24 hour span.

Additionally it would be great if there was a possibility to exclude all records where the timespan between the last attempt and the next attempt is less than 24 hour. But I do not think that this is even possible. For example in a 24 hours timespan someone has 5 total attempts. After the last attempt in that timespan another attempt is immediately started. My current query would include this result, which it should not.

Edit with sample Data (I hope this is enough, otherwise I can upload some sample Data):

|"183.3.202.190" | "2016-05-07 09:36:55.294747" |
|"183.3.202.190" | "2016-05-07 10:36:55.294747" |
|"183.3.202.190" | "2016-05-07 11:36:55.294747" |
|"183.3.202.190" | "2016-05-07 12:36:55.294747" |
|"183.3.202.190" | "2016-05-07 13:36:55.294747" |
|"183.3.202.191" | "2016-05-07 09:36:55.294747" |
|"183.3.202.191" | "2016-05-07 10:36:55.294747" |
|"183.3.202.191" | "2016-05-07 11:36:55.294747" |

Should return:

183.3.202.190" | 5 
183.3.202.191  | 3

because it is less than 5 attempts in a 24 hour window starting from the first attempt

|"183.3.202.191" | "2016-05-07 09:36:55.294747" |
|"183.3.202.191" | "2016-05-07 10:36:55.294747" |
|"183.3.202.191" | "2016-05-07 11:36:55.294747" |
|"183.3.202.191" | "2016-05-07 12:36:55.294747" |
|"183.3.202.191" | "2016-05-07 13:36:55.294747" |
|"183.3.202.191" | "2016-05-07 13:36:55.294747" |

Should return NULL because it is more than 5 attempts in a 24 hour window starting from the first attempt

Answer 1

try this :

 select attackerip,stamp_init,count(*) attack_count from (
   select a.attackerip,stamp,min(stamp_init) stamp_init,min(stamp_24) stamp_24 from thesislog_v2 a
join (
  select attackerip,stamp stamp_init,stamp+'24 hours'::interval stamp_24 from thesislog_v2
   ) b on (a.attackerip=b.attackerip and a.stamp between b.stamp_init and b.stamp_24) 
  group by 1,2
) c group by 1,2
having count(*) <=5

On your first sample data the result is the same as what you expect On the second that return 5 attacks because you have two row with the same attackerip/stamp (Is it normal ?) , if you add one millisecond at the last timestamp , the query find 6 rows and return null like it's expected. And if you add one line to your first sample like this :

|"183.3.202.190" | "2016-05-07 09:36:55.294747" |
|"183.3.202.190" | "2016-05-07 10:36:55.294747" |
|"183.3.202.190" | "2016-05-07 11:36:55.294747" |
|"183.3.202.190" | "2016-05-07 12:36:55.294747" |
|"183.3.202.190" | "2016-05-07 13:36:55.294747" |
|"183.3.202.191" | "2016-05-07 09:36:55.294747" |
|"183.3.202.191" | "2016-05-07 10:36:55.294747" |
|"183.3.202.191" | "2016-05-07 11:36:55.294747" |
|"183.3.202.191" | "2016-05-08 11:37:55.294747" |

The result is :

 183.3.202.191  2016-05-08 11:37:55.294747  1
 183.3.202.190  2016-05-07 09:36:55.294747  5
 183.3.202.191  2016-05-07 09:36:55.294747  3

Is it what you want ?