Using Collector to Run SQL Query....
We have a custom datapoint we built that runs a basic "select top 1" command to get application exceptions (which the app stores in SQL) from a remote sql server.
It appears that LM can only pull down integer data when doing this?
In other words, this table we are looking in is for application "ExceptionLog" and has columns for "User", "Severity" and "Title" and "EventID". If any of the columns are anything other than an integer, it returns "No Data" in LM. There are particular service account we want to watch out for - so ideally our query can just "select top 1 User" every few minutes, then configure alert any time user == service account.
Is this expected behavior, and has anyone found a workaround for this?
John_Lockie
Posted 4 years ago·Last reply 2 years ago
9 comments
Cole McDonald
·2 years agoOne of the ways I’ve done this in the past in a windows / powershell environment is to have the DS write errors to the windows log with specifics… then write an eventSource to pick up that failure with the text I want delivered to the monitoring package.
canderson
·2 years agoI have been wanting to do this too. I have no problem with returning numbers but I would like to return some text for the alert message. Otherwise I am stuck with a message that says “Something happened, run this query to figure out what it is.”
LM User
·4 years agoAnd that'll teach me to stay logged in with my test account.
LM User
·4 years agoAirbrake + EventSource/LM Logs + Distributed Tracing
Mike Moniz
·4 years agoCan you add new steps into the automated process? Perhaps JobMonitor would help assuming you can add a step to the process that can report the status/result to LogicMonitor. The scripts that come with JobMonitor was designed to wrap around the full job, but with some (unsupported?) modifications you can just make it just a step in an existing process.
John_Lockie
OP4 years agoThis is great feedback....thank you both. Let me take this back to the team and see if we can use any of these ideas.
Mike: to your comments in your 2nd and 3rd paragraphs: we initially assumed these errors would be in a $log file somwhere, and we could just monitor that. But as it turns out, this application only logs these exceptions in SQL (from what we have found so far). These are 1 time events, and the log itself is fairly inactive/only throws events when there are exceptions (failed login, failed ETL job, etc.). To that point, we had an automated process fail because SMTP was not working upstream. So the ability to send data blob out through SMTP did not work. And our secondary stages for this ETL process depend on an automated mailbox task, etc. So we did not know there was an issue until $business told us...which is not good. Ideally, when the app throws this exception in SQL logs we are alerted. We will have to manually check why an exception was thrown and then check further...but it's better than nothing. In meantime, I am working w/ devs to find out if there's another point in the process we can detect failures. But it's very tricky to do dead man switches in email....
mnagel
·4 years agoYep, datasources can only do numbers and LM refuses to improve on that by adding number to string mappings (enums) within datasource, instead relying on graph legends to impart that information. I submitted a F/R on that a while back, but F/R posts are rarely considered (sometimes, though, so always worth a shot).
Mike Moniz
·4 years agofor DataSources all the Datapoints must eventually result in a number, so that is true. You can write scripts or do some basic processing in LM to convert information into a number. Like converting Severity so that OK=0, Warning=1, etc. Or make a lookup table in code to convert your exception messages to codes. If you make the DataSource into a multi-instance, you will be able to create instances with custom names, descriptions and instance properties to get some of that data over but can be limited depending on what you need to store (and a bit of a hack). Getting that information out of LM into alerts and dashboards this can be hard or not possible. Also instance creation only run at-most every 15 minutes so it adds a lot of delay.You can look at the various "Troubleshooter" DataSources for examples of this trick.
DataSources would normally be used for something that has a state that you can keep querying and determine if it cleared. For example asking for free space on a drive. You can ask for the space left over and over and determine each time if that alert is active. If are looking for something like Exception event logs, they typically just have a one-time event without a start or end time. So asking over and over can't determine if the cause of the exception even cleared or not. For something like that EventSources should be used in those cases. If you do have a case where your SQL table will have no entries if there is no current exceptions (like select top 1 returns 0 rows), then DataSources would work.
You might also want to look at other features in LM (paid or otherwise), like LMConfig and LMLogs to see if those would help.
LM User
·4 years agoThis is expected behavior for a datasource. Datasources can only return numerical values.
However, you could switch from a JDBC datasource to a scripted datasource (using JDBC) and use groovy to map the possible text values to integers (think error codes).
Although, it sounds like you may do better having a scripted EventSource use JDBC and check. Then you could have the eventsource check all the lines returned for any specific criteria you want and open an alert for each offending line.