0

I'm sorry, I couldn't come up with a better title.

I have a table that has a row inserted every time someone hits my JSON api. In essence it has 2 columns, Ipaddress (nvarchar) and date (datetimeoffset).

Part of my problem is, I'm not sure exactly how to define what data I want, but overall I'm trying to figure out if someone is playing shenanigans on my api.

I think what I'm looking for is average requests per second/minute per ip address.

Could anyone help me write a query that does that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Erick
  • 853
  • 8
  • 17
  • You should define what "playing shenanigans" is to you. (Is it 10,20,50 requests per minute? Depends on what type of application you have) – Francis P Jan 21 '13 at 22:20

1 Answers1

0

To see today, by IP, per minute:

DECLARE @day DATE = CURRENT_TIMESTAMP;

WITH x AS (
  SELECT Ipaddress, m = DATEADD(MINUTE, DATEDIFF(MINUTE, @day, [date]), @day)
  FROM dbo.tablename
  WHERE CONVERT(DATE, [date]) = @day
)
SELECT Ipaddress, m, c = COUNT(*)
  FROM x 
  GROUP BY Ipaddress m
  ORDER BY c DESC; -- OR ORDER BY m

You could further derive averages from that data and filter depending on what you consider to be indicative of "shenanigans"...

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, this got me pointed in the right direction. Could you possibly elaborate on why you are using a CTE for this? It seems like I can run a very similar query without one. I'm only aksing because I've never used a CTE before and would like to understand more. Thanks. – Erick Jan 22 '13 at 15:53
  • @Erick just preference for the more modern style, and it is much more tolerable when you have multiple subqueries instead of just one. You could just as easily take everything out of the CTE and insert it between `FROM` and `x`. – Aaron Bertrand Jan 22 '13 at 16:01