You can use the sql_response probe for the connection and add the query:
DECLARE @qos VARCHAR(255),
@sampletime DATETIME = NULL,
@source VARCHAR(255),
@sql NVARCHAR(255),
@table_id INT,
@target VARCHAR(255),
@v_table VARCHAR(255);
DECLARE @qos_data TABLE (table_id INT,
qos VARCHAR(255),
source VARCHAR(255),
target VARCHAR(255) );
DECLARE CurQosList CURSOR READ_ONLY FAST_FORWARD
FOR SELECT sqs.table_id, qos, source, target, v_table FROM S_QOS_SNAPSHOT sqs
INNER JOIN S_QOS_DATA sqd ON sqs.table_id = sqd.table_id
WHERE sqs.sampletime > DATEADD(hh, -24, GETDATE())
OPEN CurQosList FETCH NEXT FROM CurQosList INTO @table_id, @qos, @source, @target, @v_table
WHILE @@FETCH_STATUS = 0
BEGIN SET @sql = 'SELECT TOP 1 @sampletime = sampletime FROM ' + @v_table + ' WHERE table_id = ' + CAST(@table_id AS VARCHAR(16)) + ' AND sampletime < DATEADD(hh, -24, GETDATE())';
EXEC sp_executesql @sql, N'@sampletime DATETIME OUTPUT', @sampletime = @sampletime OUTPUT;
IF @sampletime IS NULL BEGIN INSERT INTO @qos_data (table_id, qos, source, target) VALUES (@table_id, @qos, @source, @target);
END SET @sampletime = NULL;
FETCH NEXT FROM CurQosList INTO @table_id, @qos, @source, @target, @v_table END CLOSE CurQosList DEALLOCATE CurQosList SELECT * FROM @qos_data;
Then configure the probe to alarm on row count and/or value.
Alternatively, you can create and use a
custom checkpoint within the sqlserver probe.
Steve
------------------------------
Support Engineer
Broadcom
US
------------------------------
Original Message:
Sent: 02-07-2020 08:20 AM
From: amar kondraju
Subject: Alert when Qos is missing
Hi ,
Is there a way to monitor QOS continuously and send an alarm at that time when QOS is not there for that particular time. Please elaborate of how to do that ?
Regards
Amar
Original Message:
Sent: 02-07-2020 07:55 AM
From: Stephen Danseglio
Subject: Alert when Qos is missing
How to determine if QOS data is being updated for all tables
https://community.broadcom.com/communities/community-home/librarydocuments/viewdocument?DocumentKey=2cc9944f-55dd-46ca-af64-d444faf78132
This link should help. You can adjust the query to your needs.
Regards,
Steve
------------------------------
Support Engineer
Broadcom
US
Original Message:
Sent: 02-07-2020 05:47 AM
From: amar kondraju
Subject: Alert when Qos is missing
Hi ,
Is there a way to monitor QOS continuously and send an alarm at that time when QOS is not getting created ?
Regards
Amar