Create Alert for Number of connection

Question

Create Alert for Number of connection

Nita Reddy

SSCarpal Tunnel

Points: 4177
More actions
May 19, 2022 at 6:24 pm

#4035787

I have 2 columns , Count and Datetime
I need to send alert for count greater than 100 and ACTIVE in last 30 min (the data get inserted every 5 minutes).
It has to be greater than 300 for past 30 minutes.
Please see the below scenario it has data more than 300 for past 30 minutes. So i need alert for that how to write query for that.
Count Time
610 2022-05-19 13:55:00.107
771 2022-05-19 13:50:00.057
301 2022-05-19 13:45:01.010
302 2022-05-19 13:40:00.883
303 2022-05-19 13:35:00.837
304 2022-05-19 13:30:01.897
305 2022-05-19 13:25:00.973
-----------------------
Scenario 2nd : It has data less than 300 and it is less than 30 minutes so I don't want that alert
Count Time
310 2022-05-19 13:55:00.107
311 2022-05-19 13:50:00.057
200 2022-05-19 13:45:01.010
400 2022-05-19 13:40:00.883
500 2022-05-19 13:35:00.837
545 2022-05-19 13:30:01.897
361 2022-05-19 13:25:00.973
May 26, 2022 at 7:58 am

This was removed by the editor as SPAM
June 10, 2022 at 9:42 am

This was removed by the editor as SPAM

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

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

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Note: In the first paragraph you state it has to be greater than 100, but then say it has to be greater than 300 in the next two.

It sounds like you need to send alert if there is data for the last 30 minutes, & there are no cases w/ counts greater than 100 in that 30 minute period.

IF EXISTS (SELECT * FROM UnnamedTable WHERE UnnamedTable.[Time] >= DATEADD(MINUTE,-30,SYSDATETIME())) AND
NOT EXISTS (SELECT * FROM UnnamedTable
            WHERE UnnamedTable.[Time] >= DATEADD(MINUTE,-30,SYSDATETIME())
            AND UnnamedTable.[Count] < 300
           )
BEGIN
    PRINT 'Send Alert!'
END

~~Please don't use reserved words like "COUNT" and "TIME" for column names.~~ Count & Time are not TSQL reserved keywords.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 2

ratbak wrote:

Please don't use reserved words like "COUNT" and "TIME" for column names.

Please don't state that "COUNT" and "TIME" are reserved words in SQL Server because they are not.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ratbak Ten Centuries Points: 1358 More actions · Answer 3

Thanks for the correction. I was looking at Reserved Keywords (Transact-SQL), but looked past the TSQL section into the ODBC section.

Nita Reddy SSCarpal Tunnel Points: 4177 More actions · Answer 4

Thanks I will not put column name like count and time going forward.

Sorry for the typo mistake, it should be 300 and not 100.

I insert data every 5 minutes and my goal is to capture if the data remains 300 and above for more than 30 minutes than only send alert. if some data are less than 300 but falls under 30 minutes than NO alert.