Over the years I have written several SQL script that allow me to interrogate MS SQL Server databases and return data that gives me valuable information regarding the state of a system. The problem is that these scripts are all stored in a “scripts” directory and are only run in response to an application end user reporting issues. These scripts confirm that a system is currently experiencing issues, they do not discover issues or alert me about them. To be proactive, I would need for these scripts to warn me before the end users find out so that I can swoop in and save the day.

The simplest solution is to open a SQL query window each day, copy in all all of my monitoring scripts and run them manually every few seconds, checking the results of each. This sounds a bit cumbersome and wasteful but it does mean that if any issues occur I will detect them far quicker than if I had to wait for an application end user to let me know. Obviously there are several problems with this approach.

Cynapps IntelliAlert is a lightweight Windows scheduler that solves this problem. Here’s how it works.

Firstly, we need to tell IntelliAlert about the database we want to query. All we need is a server name, database name and login credentials if needed. Once the connection to the database is confirmed, we can start using it straight away.

Now comes the fun bit. We create a new job that will execute our query. Let’s take something simple for now. Let’s say we have an Order Management System and we want to know whether we have had any orders that have been rejected in the last minute. Simplifying the SQL to pseudo-SQL gives us the following.

SELECT COUNT(*) FROM Orders WHERE Status = ‘Rejected’ AND Added > DATEADD(“minute”, – 1, GETDATE())

We can now create a new Step within IntelliAlert that will run this SQL against the database we set up earlier.

So far so good, but how will we know whether there have been any rejections? We now need to add an alert to this Step. If the query returns anything greater than zero we know we have issues and want to be alerted in some way. Let’s add an alert to send an email when the the query returns anything greater than zero.

Finally we need to schedule this Step to run every 60 seconds.

That’s it, we’re done. We now have an email generated anytime the query above returns any value greater than zero which runs every minute. So we will now know of an order has been rejections within a minute of the rejection occurring.

Cynapps InetlliAlert allows you to string multiple steps together to create very powerful schedules that can monitor, alert and resolve everyday issues.

To find out more about the enterprise version of Cynapps InetlliAlert please contact us.