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:
- Is something like this possible?
- 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!
Shack
·1 year agoWould 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 Cavernelis
·1 year agoUnfortunately 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.
Stephen Cavernelis
·1 year agohttps://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
·1 year agoI 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.
autarch
·1 year agoI 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
·1 year agoIf 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.
Mike Moniz
·2 years agoThat 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.
Kel L
OP2 years agoThat's what I ended up doing. I found an existing SQL query and just modified it for what I needed Worked great.
Thanks.
LM User
·2 years agoI 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