I use SentryOne’s SQL Sentry for my SQL Server monitoring, and it does an excellent job. However I recently had a situation where I was unable to get email alerts from SQL Sentry. This was by no fault of SQL Sentry, our email sever was down so I couldn’t get email from anyone. Even though I watch the SQL Sentry client tool to keep an eye on things, I rely heavily on email alerts for things that may not be obvious through the client dashboard. So, while my email is down, I start getting the jitters thinking that everything is collapsing behind the scenes now that I’m not watching.
Thankfully, all it took was a quick peek into the SQL Sentry database to find the information I craved. As I suspected, there is already a view available for querying the alerts that I should have gotten via email. Now, I will be the first to admit that I know very little about the SQL Sentry database, or the inner workings of it. The view I am querying may not be the only, or best, place to get this information. I did not contact SentryOne to verify. I simply queried the view and saw that the results were consistent with the normal alert notifications that I get via email, and that was enough to soothe my jitters until my email is fixed.
The query below pulls the alerts for the last 7 days. I am also filtering out the notices about failure sending email, since I know that my mail server is down and SQL Sentry can’t send me email. If you are going to use this query, I would recommend running it first with no filters so it will return all alerts, then review through those and filter out the ones you do not need.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT [CategoryName] ,[EventStartTime] ,[EventEndTime] ,[ObjectTypeName] ,[ObjectName] ,[ConditionTypeName] ,[ActionTypeName] ,[Message] ,[ParentObjectName] FROM [SQLSentry].[dbo].[vwObjectConditionActionHistory] WHERE [EventStartTime] >= DATEADD(d,-7,getdate()) AND [ConditionTypeName] NOT IN ('Monitoring Service: Action Failed') ORDER BY [EventStartTime] DESC |
This query does not select every column available in the view, I filtered the result set down to just what I wanted to see. Several of the columns I omitted are GUID IDs back to other tables, so you can expand the query to join out to other tables if you are looking for more information.