Forum Discussion

Kelemvor's avatar
Kelemvor
Icon for Expert rankExpert
2 years 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

14 Replies

  • Anonymous's avatar
    Anonymous
    2 years 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

  • Mike_Moniz's avatar
    Mike_Moniz
    Icon for Professor rankProfessor
    2 years 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
      2 years 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.

  • Stephen_C's avatar
    Stephen_C
    Icon for Neophyte rankNeophyte
    2 months ago

    I'm looking at the same problem here. I have a SQL query I am running, and if there is a result I want the output to be included in the alert. The output consists of multiple strings.

    I have currently done this via a configsource module, but it requires the user to login to LM to actually see the output.

    If I use LM logs, and the issue occurs again at each collection (which is currently at 60 mins) it would mean another alert would be raised. (Unless someone has any ideas on how to NOT raise the same alert in LM logs? ie. only raise once)

    If I use a datasource module, I could potentially have the instance name contain the details (like the SQL troubleshooter), but it is quite a bit of information in the output.

    If I use an eventsource module, that would also probably work fine, but I read in the LM documentation that eventsources will be deprecated in favour of LM logs, and the last thing I want to do is create an eventsource that will need to be rewritten as another module at some point.

    Anyone have any suggestions?

    Thanks

     

     

     

    • Mike_Moniz's avatar
      Mike_Moniz
      Icon for Professor rankProfessor
      2 months ago

      Do you have a link to where it says EventSources will be deprecated? I haven't seen that (but I'm not on top of all the news). Especially since LM Logs is a paid add-on and therefor removing EventSources would then be removing base functionality.

    • Dave_Lee's avatar
      Dave_Lee
      Icon for Advisor rankAdvisor
      2 months ago

      I've taken a similar approach to this with a few things where I've wanted to put some meaningful text into the alert itself.  Rather than using the instance description, I tend to put the extra information into instance properties though

      For example, I have a data source that checks if WMI is working properly on Windows Servers.  If it can connect over WMI it runs a few checks to ensure that we have the necessary permissions (checks it can receive OS information, checks it can query services state, etc).

      This runs in Active Discovery, rather than data collection, just so I can write text info out to instance properties.  So, if all is good, then it writes the instance property wmicheck.statusCode=0.  If not, then it writes wmicheck.statusCode=1 and wmicheck.statusCodeReason=<what the script found was the problem>.    The Active Discovery runs every 24 hours.

      Then I have the collection script just read the value of the "statusCode" instance prop and alerts if it is 1, but I can include ##auto.wmicheck.statusCode## in the alert message to get the details of where the problem is.

      • Stephen_C's avatar
        Stephen_C
        Icon for Neophyte rankNeophyte
        2 months ago

        Hi Dave,

        The problem I have with that is that active discovery only runs in 3 intervals (15mins, 1 hour, 1 day), And I don't want to risk the instance property having outdated information. 

        It would be so much more useful if we could just write to a property directly from a data collection, without going the round-about way of using the API. Or even better just be able to pass the text as a token to the alert message.

  • autarch's avatar
    autarch
    Icon for Neophyte rankNeophyte
    6 months ago

    I have this same request come my way.  I am by no means an expert in LogicMonitor or SQL so I am wondering what LogicModule you ended up using?

    • Joe_Williams's avatar
      Joe_Williams
      Icon for Professor rankProfessor
      6 months ago

      If you look at some of the Microsoft_SQLServer datasources, this seems pretty easy to accomplish. I would say, look at the Troubleshooter one. Clone it out, and see about playing with the code to make it run what you need. The Active Discovery script looks pretty simple to alter to your needs. Add in your select and do a check if its null or empty string.

  • Stephen_C's avatar
    Stephen_C
    Icon for Neophyte rankNeophyte
    2 months ago

    https://www.logicmonitor.com/support/logicmodules/eventsources/types-of-events/script-eventsources

    "Disclaimer: This content is no longer being maintained and will be removed at a future time. For more information on the recommended way to ingest logs, see Script Logs LogSource Configuration"

    • Mike_Moniz's avatar
      Mike_Moniz
      Icon for Professor rankProfessor
      2 months ago

      I think LM put that disclaimer on many of their support pages that still reference or show images from the old UI. So I think they are referring to the content of the support page and not the feature itself, while also pushing their paid add-on. There isn't a mention of an removing the feature from the EventSource support page. But if someone at LogicMonitor here can clarify that would help.

  • Shack's avatar
    Shack
    Icon for Advisor rankAdvisor
    2 months ago

    Would returning the strings to datapoints using textmatch work?  Your datapoint descriptions could contain information on what the various strings mean.

    TextMatch
    Look for presence of a string (TextMatch) tests whether a string is present within the raw output. This method supports regular expressions for text matching. If the string exists, it returns 1, otherwise 0.

     

    • Stephen_C's avatar
      Stephen_C
      Icon for Neophyte rankNeophyte
      27 days ago

      Unfortunately this wouldn't work for me. The text in the string returned varies. The mere fact that a string is returned indicates a problem, and the actual string contains the particular record information.