Create Alert for Number of connection

Question

Post reply

Create Alert for Number of connection

Nita Reddy

SSCarpal Tunnel

Points: 4171
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 SSC Eights! Points: 939 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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

ratbak SSC Eights! Points: 939 More actions · Answer 3

ratbak

SSC Eights!

Points: 939

May 19, 2022 at 8:36 pm

#4035839

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: 4171 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.