Forum Discussion

JDH212's avatar
JDH212
9 months ago

How to return text results from an SQL query

Hello,

Relatively new to LM and have been tasked with trying to get a datasource set up that will show all the values in 1 column off the back of a script in MySQL.

These values will all be job names ie report_job, send_job etc. My understanding is the JDBC collection only returns numerical values?

if this is the case is there any sort of documentation or a “beginners guide” on how I can go about getting something setup where the data returned will be my list of job names based on certain criteria?

Thanks in Advance.
Jonathan

  • Anonymous's avatar
    Anonymous
    9 months ago

    Do you just want the names of the jobs but you don’t need to monitor anything about them? In that case, you could use JDBC discovery to create the instances and have a dummy datapoint for monitoring.

    Yes, all DataSource monitoring can only do numeric data for collection. But the instances that are being monitored have text based names and can have any number of instance level properties associated with them. 

    A different route would be a configsource where the configsource script queries the database and returns the data. The goal there would be to just have a blob of text that is monitored for changes. You’d want to make sure that your script outputs the jobs in the same order every time to prevent false alerts indicating a change.

  • John_Cutsinger's avatar
    John_Cutsinger
    Icon for Employee rankEmployee
    9 months ago

    The best way to start with MySQL modules is to look at some of the LM core modules and modify the code to get the fields you want.   See https://www.logicmonitor.com/support/monitoring/applications-databases/mysql/ for the MySQL modules.  Some of them are pretty simple.  I’d probably start with Oracle_MySQL_Databases as a start.

    You’re right that you can only store numerical data.  You can create instances with text data but instances can get out of hand pretty quickly so be careful creating too many of them.

  • JDH212's avatar
    JDH212
    9 months ago

    Do you just want the names of the jobs but you don’t need to monitor anything about them? In that case, you could use JDBC discovery to create the instances and have a dummy datapoint for monitoring.

    Yes, all DataSource monitoring can only do numeric data for collection. But the instances that are being monitored have text based names and can have any number of instance level properties associated with them. 

    A different route would be a configsource where the configsource script queries the database and returns the data. The goal there would be to just have a blob of text that is monitored for changes. You’d want to make sure that your script outputs the jobs in the same order every time to prevent false alerts indicating a change.

    Hello,

    Effectively I want to run a MySQL Script that will output job names. For example:

     “Select Job_name where success = ‘0’” and timestamp > 1 hour

    and then those Job names that are outputted I want to display in some form of widget that shows the support team which jobs have failed so that rather than seeing “jobs failed in last hour = 1” they’ll see the actual job name of what has failed.

    Thanks
    Jonny

  • JDH212's avatar
    JDH212
    9 months ago

    Do you just want the names of the jobs but you don’t need to monitor anything about them? In that case, you could use JDBC discovery to create the instances and have a dummy datapoint for monitoring.

    Yes, all DataSource monitoring can only do numeric data for collection. But the instances that are being monitored have text based names and can have any number of instance level properties associated with them. 

    A different route would be a configsource where the configsource script queries the database and returns the data. The goal there would be to just have a blob of text that is monitored for changes. You’d want to make sure that your script outputs the jobs in the same order every time to prevent false alerts indicating a change.

    Yeah so effectively I need to do an SQL script which will be something like below example:

    Select Job_names where success = ‘0’ and timestamp > 1 hour ago

    Then I need to display those results in a dashboard so the team can identify which jobs have failed within the last hour so the team know which ones to investigate, rather than the current view of just how many jobs have failed.

    Thanks

    Jonny