Enabling verbal alerting through SQL Sentry

A question that comes up a lot during demos and proof of concepts is “Can your software tell me when there is a problem?”. The answer to that is of course, there are many integration points to notify you. In my last post; Setting Red Alert, I spoke about how it is possible to extend what is already a fantastic offering around alerting you to SQL Server woes. This was achieved by turning on lights via IFTTT. In this post, I’m going to show you how we can tell you there is an issue that needs to be addressed. When I say “tell you” I don’t mean just an e-mail, I mean an actual voice telling you there is an issue.

Pretty cool, eh?

As with my last post these are all integrations that we need to set up outside of the SQL Sentry tools after the conditions you are interested in have been set up. The only configuration you will need to do in our tools is to ensure that you can receive conditions via e-mail.

What’s the point?

To some this may sound a frivolous exercise, but how many people turn on the desktop alerts for e-mail? Can you be sure that you will see an urgent e-mail as it comes in? Even with two screens I don’t always see e-mails for some time after they have arrived. By default an e-mail notification sound won’t distinguish between a Nigerian Prince claiming to be your long lost cousin or the fact your Tier 1 production critical server has just gone down.

Yes, it does sound frivolous, but it does have a great business case. It allows you to respond faster to priority events.

Let’s get started

Right, in order to do this you are going to need the following:

• A really lowly paid member of staff to read all your e-mails out loud to you (Not really conducive to a great how to blog post).

OR

• A Windows machine (7 or higher).
• Microsoft Office.
• A pair of headphones (Be kind to your colleagues).

The later versions of Microsoft Windows has text to speech software that we can call and make use of. Unfortunately Outlook doesn’t have this capability directly (that I could find), but Microsoft Excel does. What we will be doing in this post is creating an Outlook rule to identify the e-mails we wish to be told about, then use Excel’s integration with Text to Speech to be notified about them.

The starting point – code

I’m not a developer by trade, so you’ll have to forgive me if it is not the most elegant solution. It’s been written to be pretty generic, just in case you want to use the code for more than one sender. If that is the case, then you can amend the ReadOutText variable inside another piece of logic depending on what you would like it to say. You may also want to play around with the settings to only inform you about e-mails that are sent with high importance for example.

If you would like to expand upon the code below then let me know what you’ve added in the comments below. The following link will provide more information about other properties of MailItem https://msdn.microsoft.com/en-gb/library/microsoft.office.interop.outlook.mailitem_properties.aspx

As a disclaimer, neither SQL Sentry or myself are liable for the outcome of any e-mail subject read out in your office. You forget I know what you lot are like!

All you need to do is to open Microsoft Outlook and press “Alt + F11”. This will open up the VBA Editor. Copy and paste the code below and then hit save. We will need to call this code later on in the tutorial.

Public Sub SQLSentryShoutOut(Item As Outlook.MailItem)
 
    Dim ExcelApp As Object
 
    Dim strFrom As String
    Dim strMessageType As String
    Dim ReadOutText As String
 
    Set ExcelApp = CreateObject("Excel.Application")
 
    strFrom = Split(Item.SenderName, " ")(0)
    strMessageType = Right(Item.Subject, 3)
 
    ReadOutText = "Hear thee! hear thee! SQL Sentry brings you tidings of woe" & _
                  " from your SQL Server estate."
 
    Select Case strMessageType
      Case "RE:"
        ReadOutText = ReadOutText & strFrom &_
                      " replied to an e-mail regarding "
      Case "FW:"
        ReadOutText = ReadOutText & strFrom &_
                      " Forwarded an e-mail regarding "
      Case Else
        ReadOutText = ReadOutText & "You have an e-mail from " &_
                      strFrom & " regarding "
    End Select
 
    ReadOutText = ReadOutText & Item.ConversationTopic & "."
 
    ExcelApp.Speech.Speak ReadOutText // key line here
    ExcelApp.Quit
    Set ExcelApp = Nothing
End Sub

For more information about accessing speech through Excel, refer here: Speech Object (Excel). While researching for this post I also found out that Excel has speech recognition too. Right now I can’t think of a good reason to blog about it, but I’m sure to find a way eventually. In the meantime you could look at this link on it https://support.microsoft.com/en-us/kb/288979

One rule to rule them all

My suggestion to you, is only to enable this Outlook rule when sent by certain accounts. You may however decide that you wish to tighten this predicate to also include things like subjects (and possibly time to not freak out shift workers). At this time I should point out the big caveat; because this is a rule running on your client on your Windows machine, you won’t be notified in the same manner elsewhere – your smartphone for example. I’ll look for a way of being verbally notified on Android devices at a later date. It should be possible with something like Tasker.

If we are looking to run this rule for just e-mails coming from SQL Sentry then the first stage is to find out which e-mail addresses SQL Sentry is sending from. The easiest way is to look at your SMTP settings.

• Expand the Monitoring Service tree at the bottom of your Navigator pain in the SQL Sentry Client.
• Right click on Settings.
• This should open up a new tab, the first sub tab should be “SMTP Config”. Click on the ellipsis button (the 3 dots).
• Choose the SMTP Server you send alerts from and click “Edit”.
• The default From address should now be displayed on a properly configured system.

It is possible to send e-mails from other addresses inside of SQL Sentry. Each condition with an e-mail action associated with it has the potential to be sent from a different address. Rather than look through each condition with an e-mail action, it would be easier to just run a query against the repository.

SELECT DISTINCT FromAddress 
  FROM SQLSentry.dbo.ObjectActionEmailPreference
  WHERE FromAddress IS NOT NULL;

Creating an Outlook Rule

Typically the easiest way to create an Outlook rule is to pick an e-mail as a starting point. Right click on that e-mail and then choose “Rules” and then “Create Rules”.

The benefit of starting with an existing e-mail rather than starting from scratch, is that it will populate the From and Subject attributes for you, as indicated by the red boxes in the image below. Once you’re at this stage, click on the “Advanced Options” button, which I have highlighted in blue.

After clicking on Advanced Options, a new window will appear called “Rules Wizard” it has defaulted to only pick up e-mails with the from address in the original e-mail we chose. In step 1 we can add further predicates to help reduce the amount of times that this is going to fire. You may wish to do this to cut on the noise. Normally when I say “to cut down on the noise” I’m referring to e-mail, in this case it really will be a noise!

If you wanted to change any of the values chosen in step one, then click on the appropriate hyperlink in Step 2 of the wizard and it will allow you to change it.

Click “Next”.

Once you have clicked Next, a new window will pop up. For some reason Microsoft have decided to reuse the terminology Step 1 and Step 2 rather than increasing them to 3 and 4. Anyway, on this window you will be choosing which actions you wish to perform based on the conditions within the rule we are setting up. In this case we want to check the box for “run a script”. Click on the “a script” hyperlink in Step 2 of the actions screen and select the script we created earlier. If you didn’t change the name, then it should be Project1.SQLSentryShoutOut.

Click OK

Click Next

After clicking Next, another window appears. Again you may be forgiven because it uses Step 1 and Step 2 again, grrr.
This new window allows you to add in exclusions.

Check the box if you would like to exclude on any of these things and then once again edit those by clicking and setting the appropriate values in the 2nd step (of 6, for the THIRD time).

This could get noisy!

At this point I recommend that you find some headphones, plug them in, change your default sound device to those headphones, and then set the volume at a sensible level. You can always have it blasting out at silly volumes over wireless multi room speakers when you have it working the way you want!

Go back to the Rules Wizard and click “Next”.

In my case I changed the name of the rule from the e-mail address to something more meaningful – “Voice alert from SQL Sentry.” I’ve also changed the Setup rule options to run now on messages in this folder rather than turning on the rule. This allows me to test the rule quite safely as I am not doing anything other than read it out. If you have added in extra steps to move or delete e-mails then you might want to change that.

If you’re not happy with the voice that Text to Speech is using then you can change it. In my case things are shut down by Group Policy, however in Windows 10 you will be able to type in “Text to Speech.”

Hopefully this post has provided you with a taster of the kinds of things you can do to extend an already extremely flexible alerting engine in our monitoring solutions. I’d love to hear from you if this has positively impacted your working practice.

Richard Douglas

Richard (@SQLRich) is a Principal Solutions Engineer at SentryOne, specializing in our SQL Server portfolio offering in EMEA. He has worked with SQL Server since version 7.0 in various developer and DBA roles and holds a number of Microsoft certifications. Richard is a keen member of the SQL Server community; previously he ran a PASS Chapter in the UK and served on the organizing committee for SQLRelay.