Forum Discussion

Kelemvor's avatar
Kelemvor
Icon for Expert rankExpert
6 months ago

Can LM run a SQL Query against a specific Server/Database and alert based on the results?

Hi,

I had a request for this today, but I've never done anything like this, so I'm not sure If it's possible or how to do it.

The original request was to run a certain query and create an alert if it returns any results.  I'm assuming that normally this table is blank, and if there's anything in it, that means something is broken, behind, etc, so they want to know.

I'm guessing we could either treat it like an Eventsource where any results just makes a stand alone alert every time it runs.  Or I could have them modify the query to return a Count of results and then maybe set it up like a Datasource with thresholds based on the Count that is returned.

So that brings me to:

  1. Is something like this possible?
  2. Does anyone have a tutorial or example on how I'd set something like this up?

I have a lot of experience using LM with the stuff it comes with, but I have little experience creating new source from scratch or doing scripting or anything like that.

Thanks!

sql
  • Mike_Moniz's avatar
    Mike_Moniz
    Icon for Professor rankProfessor
    6 months ago

    That sounds quite doable. When I want to create a new LogicModule I generally start by looking for an existing one that is close to what I want and take bits and pieces to create a new one. Perhaps try looking at DataSource "PostgresServer-" and "Azure_SQLServer_JDBC". These are code-less as they use the JDBC method. There are various examples of other SQL checks which use groovy code but I didn't really find one that was simple to follow if your not a coder. Sometimes it also is useful to look at PropertySources even if you are not looking to create one. They may be simpler than DataSources or Eventsources and you can reuse it's code.

    • Kelemvor's avatar
      Kelemvor
      Icon for Expert rankExpert
      6 months ago

      That's what I ended up doing.  I found an existing SQL query and just modified it for what I needed  Worked great.

      Thanks.

  • Anonymous's avatar
    Anonymous
    6 months ago

    I would have them modify the query to return account and use that count as a data point. That way the alert would stay open as long as the count is non-zero. 

    https://github.com/sweenig/monitoring-recipes/tree/master/DataSources/Groovy/JDBC