Options
clearing all alerts SQL Monitor 5.0.1.1302
karl.oliver
Posts: 33 Bronze 1
hello
how do i clear all alerts- over 1000, the 2 post i have found on the forum so far dont work one of them being the sql below
using SQL Monitor 5.0.1.1302
this sql did not clear them
DECLARE @Alerts TABLE (AlertId INT, Date BIGINT)
INSERT INTO @Alerts
( AlertId, Date )
SELECT A.AlertId, ASev.Date
FROM alert.Alert A INNER JOIN alert.Alert_Severity ASev ON A.AlertId = ASev.AlertId
INNER JOIN alert.Alert_Type AType ON A.AlertType = AType.AlertType
WHERE A.TargetObject LIKE '%<server/cluster name>%'
AND AType.Name = '<alert type>'
INSERT INTO alert.Alert_Cleared
( AlertId, Date, Cleared )
SELECT A.AlertId, A.Date, 1
FROM @Alerts A
UPDATE alert.Alert SET [Read]=1
WHERE AlertId IN
(SELECT AlertId from @Alerts)
how do i clear all alerts- over 1000, the 2 post i have found on the forum so far dont work one of them being the sql below
using SQL Monitor 5.0.1.1302
this sql did not clear them
DECLARE @Alerts TABLE (AlertId INT, Date BIGINT)
INSERT INTO @Alerts
( AlertId, Date )
SELECT A.AlertId, ASev.Date
FROM alert.Alert A INNER JOIN alert.Alert_Severity ASev ON A.AlertId = ASev.AlertId
INNER JOIN alert.Alert_Type AType ON A.AlertType = AType.AlertType
WHERE A.TargetObject LIKE '%<server/cluster name>%'
AND AType.Name = '<alert type>'
INSERT INTO alert.Alert_Cleared
( AlertId, Date, Cleared )
SELECT A.AlertId, A.Date, 1
FROM @Alerts A
UPDATE alert.Alert SET [Read]=1
WHERE AlertId IN
(SELECT AlertId from @Alerts)
Comments
Thanks for your inquiry about SQL Monitor.
To clear the alerts:,
1. Go to the Alerts page on the SQL Monitor GUI,
2. Select the "SELECT" drop down button and click the option ALL (Be sure to click the "Select all alerts across all pages" if you want to delete alerts from more than one page)
3. To clear the alerts select the CLEARED button to the right of the SELECT button.
Sean Quigley | Product Support Engineer | Redgate Software
Have you visited our Help Center?
This qeury is used by me for the past 6 months. Seems to do the trick:
DECLARE @now BIGINT
SET @now = utils.DateTimeToTicks(GETUTCDATE())
;WITH cte AS (
SELECT acu.[AlertId]
FROM [alert].[Alert_Current] acu
LEFT JOIN [alert].[Alert_Cleared] ac
ON ac.AlertId = acu.AlertId
WHERE (ac.AlertId IS NULL) AND (acu.[Event]=1 OR acu.[LastSeverity] = 0)
)
INSERT INTO alert.[Alert_Cleared]
(
[AlertId],
[Date],
[Cleared]
)
SELECT
[AlertId],
@now,
1
FROM
cte
GO