Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Does anyone know how to create a custom alert using the the Advisors icons?

For example I need to send an alert when the information icon on the queries in the Advisor tab turns red. I have a custom alert for long running queries but you either have to have a blanket level or set one for each query. Since the Advisor has already determined the critical level for the each of the top queries i'd like to set the alert using the logic already in use for setting the critical icon.

I believe I have found the data I need for the custom alert

Find more posts tagged with

Accepted answers

All comments

jaminsql

Good idea. The advise table would be CONSPA_X where X is the number from COND of the monitored instance. I didn't find an alert but, there are two reports here on THWACK that might give you a start on a draft.

Custom Report - Top Plan Advice

Top 20 Advice SQL

jet

Here is the custom alert I did

/DESCRIPTION
This alert is for SQL Server instances only and will tell you when SQL statements have been marked as 'Critical' for the past 2 days.

ALERT DEFINITION
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:
Alert Name: SQL Server - SQL marked as Critical in Advisor

Execution Interval: 10 minutes

Notification Text: These queries have been labeled as Critical during the past 2 days.

SQL:

select 'Query: ' +Item + ' has been labled as critical', Count(item)
from CON_PROBLEM_SUMMARY_#DBID# ps
INNER JOIN CON_PROBLEM_ANALYSIS_#DBID# pa
ON pa.ID = left(ps.problemid,Charindex('-',ps.problemid)-1)
WHERE ps.ALARMLEVEL = 'Critical'
and DATEDIFF(day,pa.STARTTIME, CURRENT_TIMESTAMP) < 2
group by item

Execute Against: Repository

Description: Returns the SQL Hash of those queries labeled as critical

Units: items

High Threshold: 1 min, max empty

m60freeman

If I plug the resulting hash value into this and run it in the instance triggering the alert, I get nothing:

SELECT st.text

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

WHERE qs.query_hash = CONVERT(VARBINARY(8), <HashFromAlert>);

Any ideas?

jaminsql

This is because DPA stores a hash that is not the full varninary SQL_handle but, is a hash based on that value. Please up vote storing the full value display feature request.

Currently the only place to get the full value is in the Current tab and then under show active sessions we display the DPA hash and the sql_handle. The drawback here is you have to use the value you have and then look up the varbinary. you also have to catch it while it is running (or idle with show idle sessions sessions selected).

jaminsql

Did you add this also on the content exchange ?

m60freeman

I upvoted the feature request.

That workaround might be barely acceptable if it actually worked, but there is too much likelihood that the query won't be listed there. In my case, I did find it, but when I stick a 0x on the front and plug it in to a query and run it on the correct instance, I still get nothing:

SELECT SQLText = st.text

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)st

WHERE qs.sql_handle = 0x0100070068A27D1060D25B461000000000000000

Neither of the queries shown in the Advisors tab have the sql_hash value shown by the alert: