Setting Red Alert
You may have noticed from some of my other posts that I use S's rather than Z's; that's because I come from a small island that once used to have an empire. Or to give it its proper name, the United Kingdom of Great Britain and Northern Ireland. Personally, I am from England, which makes travel to Huntersville in North Carolina a bit much to do on a daily basis. Because of this, I have an office at home.
Recently I have been dabbling with the idea of creating a smart home and a smart office. Finances the way they are mean creating a piecemeal approach, but in order to justify the cost, not only to myself but also to my boss (not SQL Sentry CEO Greg Gonzalez; the other one much closer to home), I needed to come up with ways that it can extend things we already do, and make them easier.
Here at SQL Sentry we believe that we have the most granular alerting system on the market. However whenever we talk about our capabilities we tend to stop at what can be done inside our solutions; much less often do we talk about integration points outside of this. Of course it's possible to link to other monitoring systems by writing to the Windows event log, using SNMP traps, passing commands to PowerShell or the command line, and so on. But what if I wanted a real red alert?
Creating a Red Alert Scenario
One of the many actions we can perform is to send an e-mail, and to be fair most alerting systems will be able to do this too. But have you ever thought about what can be done with this e-mail?
With SQL Sentry Performance Advisor you have the ability to change the format of this e-mail; for example, you might want to change it to meet the required format of a helpdesk system to raise tickets for you.
That's all a bit boring though, really helpful from a productivity point of view, but still boring. I've mentioned my dream to have a smart home and office. Would it be possible to create a scenario where, when there is a serious error, all of my lights went red? It would be an actual red alert. I know I'm a geek, but I think that's pretty cool!
So how would we go about this?
What we need for a code red
Once you have created your condition or Custom Condition and set up that you want to send an e-mail as the action, we're ready to go. All you need are a few things:
- An IFTTT account
- A GMail or Office365 account
- A Philips Hue system - OK, this last one is a little expensive.
I won't go into the full details here, but there are already recipes set up to do this kind of thing for you. You will need to set up your own channels, but these are fairly straight forward. My recipe is based off of Important E-mails Color Room. If you did want a little more information on how to set up a recipe, Jason Hall has a more thorough walkthrough where he links SQL Sentry e-mails to a Slack channel through IFTTT here.
Admittedly, chances of being able to change the colour of the lights in your office are slim. How many of us are on call though? As DBAs we're expected to work more than just 9-5, and having one of your lights flash red might be just the thing to let you know to dial in. After all, smartphone batteries aren't really built to last a full weekend on call.
If it's a super serious alert, you could set up multiple recipes to fire for the same condition; the lights could go brown, turn all your fans on and have a speaker play Chopin's Funeral March. I'm likely to save that set of recipes for Replication errors. :-)
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.