Idea for using SQL EPLs in monitoring

I was talking yesterday with a colleague about application monitoring, which lead me to think about how an EP engine could help. There are many monitoring products on the market that use rules or even a rules engine to help interpret and respond to events. What about embedding an SQL EPL (event processing language) in there?

An SQL EPL could provide some functionality that is tough using traditioinal rules. This could compliment a rules based approach, to provide more flexible monitoring.

The first feature that comes to mind is event pattern matching. Some monitoring products already support certain patterns, but AFAIK, SQL EPLs support more. All of the major SQL EPLs now support detecting several kinds of patterns in incoming events, a simple example being “event A followed by event B within X seconds”. For reference, see StreamBase SELECT FROM PATTERN, Coral8 SELECT FROM MATCHING, Esper SELECT FROM PATTERN.

This kind of pattern matching could allow for more flexible scripted monitoring. Let’s say I want to send a request to a message bus and expect a response from an application on that bus. I have seen solutions that allow for this, but configuring an advanced scenario can be tough (although I have not done much work in this area, so there are many products that I don’t know). But suppose we have a monitoring component that sends the message to the bus and a component that gets messages and sends them to an SQL EPL engine. Now we can declare patterns like “a send, not followed by a bus error, not followed by a matching response within 30 seconds”. Or a more complicated version involving several patterns:

• a send followed by a bus error results in an internal bus-down alert
• a bus-down alert, followed by a send, not followed by a bus error results in a bus-up alert
• a send, not followed by a bus error, not followed by a response within 30 seconds results in a service-down alert
• a send, not followed by a bus error, followed by a response within any time-frame results in an internal service-timing event containing the time between the send and the response

Now, add in SQL aggregation (see StreamBase GROUP BY, Coral8 GROUP BY, Esper aggregation in SELECT statements):

• First a pattern: a bus send, not followed by a bus error, followed by a response within any time-frame results in an internal service-timing event containing the time between the send and the response as well as the service-ID.
• Now some aggregation: select service-timing events where response-time is greater than 10 seconds, group by service-ID and count the events within the last minute. This would produce one service-delay-count event per minute, per service-ID
• And then a basic selection rule: select service-delay-count events where the count is greater than 3 and produce an internal service-delayed event.

And then do some simple statistics with aggregation and windows (see StreamBase CREATE WINDOW, Coral8 CREATE WINDOW, Esper CREATE WINDOW):

• Using aggregation as above, create events containing the mean and variance of service-timing over the past hour, updated every minute.
• Keep the last one of these events in a window of size one. Now we have an up-to-the-minute record of service timing, per service, for the past hour.
• Also, use aggregation to produce events containing the per-service timing for the past ten minutes.
• Join the hourly timing with the ten minute timing (see the JOIN statements from previously mentioned products), and use simple statistical methods to detect the case where the service timing is slowing down to a significant degree.

I think that it’s exciting that all of these scenarios could be detected using an SQL scripting language. The events produced by the detection scripts could be fed into an existing event management solution, a rules engine or whatever.

Some of these techniques are already used with these SQL products to detect stock market patterns, so we are using a known approach to event detection. I’ve seen an SQL system used to detect service issues in an electronic trading scenario, but it was created as a custom project and had to be manually integrated with existing monitoring solutions. I wonder what would happen if an SQL EPL were integrated tightly with a monitoring product, which would make it more friendly to the people who are already involved in monitoring.

This sounds like an exciting prospect for SQL EPLs. At the moment, SQL EPLs are used mostly in custom projects or high volume, low latency applications like electronic trading applications, custom monitoring (see StreamBase’s case studies in monitoring) or RFID. But if this technology could provide value to more general monitoring, and if it could be tightly integrated with a larger monitoring product, then it would move from a niche solution to a central component in the enterprise architecture.

P.S. I would have linked to the equivalent Aleri features in this post, but I could not find linkable online documentation.

P.P.S. SQL EPLs are not the only possibility here. My understanding is that other specialized event processing products like RuleCore support much of this functionality as well.