Nagios tool for fetching database records

[RIDS_I2MP]

Hello Team,

Can we use Nagios tool to connect to SQL server which is being monitored by Nagios and fetch the data from that server?
To be more clear, can Nagios connect to SQL server and run SELECT query to fetch the data and return the output?

Please let me know if you need any information for the same.

[mcapra]

To be more clear, can Nagios connect to SQL server and run SELECT query to fetch the data and return the output?

Have you tried the MSSQL Query configuration wizard mentioned in the official SQL Server documentation:
https://assets.nagios.com/downloads/nag ... ios-XI.pdf

It would depend on what "the data" looks like. I'd start with the SQL Query wizard and see if that satisfies your use case. If not, there are alternatives to explore.

[cdienger]

The link @mcapra provided is a good place to start. Let us know how it works out for you.

There's also a wizard for MySQL queries. This one only works for queries that return integers however.

[RIDS_I2MP]

Hello,

Thanks for your reply.
Yes I referred the document and we are already monitoring some MSSQL services.

We have requirement like below:

Run a select query and provide the data like select * from employee where emp_id = 30 and get the data as we do in SQL database.

I mean, along with monitoring the service on DB server, we want to run select query and fetch the data or output of the query.

[cdienger]

Have you tried setting up the query? I'm not clear where the problem is.

Note that it seems to work a bit better if you pull a single field with the query. So instead of:

select * from employee where emp_id = 30

try something like:

select name from employee where emp_id = 30

[SteveBeauchemin]

I have some stuff I use for doing MS SQL things. I am attaching them to this post.
Some are in perl, some in shell script. You will need to edit them for your installation.
I tend to modify them all, tailor them for what I need, but never seem to update the internal comments.
Someone posted the original mssql shell script in 2003. It has been highly modified, but I still use it today.

Everything depends on the freetds install and a proper freetds config file.
If you need to use TLS 1.2, then you will need to download a new version and compile it.
This is what I used in the compile process. You may not need to do this.
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/usr/local/etc --with-gnutls

I am attaching a zip file with the scripts. I make No promises. Use them or just learn from them.
They can be instructive if you read them carefully. They will run if you satisfy the requirements.
They are working scripts have been running every 5 minutes for many years. The shell scripts simply
use tsql and pass it parameters. The perl uses modules and needs to be properly setup.

I am also including a snip of the freetds config file. I added some examples of
old 2003 server, named instances, sybase, and highly secure 2016 server using TLS 1.2

Code: Select all

# file is freetds.conf
# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
        tds version = 7.2

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
#;      dump file = /tmp/freetds.log
#;      debug flags = 0xffff

        # Command and connection timeouts
        timeout = 40
        connect timeout = 10

        # If you get out-of-memory errors, it may mean that your client
        # is trying to allocate a huge buffer for a TEXT field.
        # Try setting 'text size' to a more reasonable limit
        text size = 64512

        # Choose a default Character Set
        client charset = UTF8
# Production SQL Instances
[server1]
        host = server1.company.com
        port = 1433
[server2]
        host = server2.company.com
        port = 1433
# new server using tls version 1.2
[newserver1]
        host = newserver1.company.com
        port = 1433
        tds version = 7.3
        encryption = require
# ----- Cluster using Named Instance
[namedinst1]
        host = namedinst1.company.com
        instance = namedinstance1
# ----- Sybase Server using port 4300
[SYBASE_ATS]
        host = sybase1.comonay.com
        port = 4300
        tds version = 5.0

The .sql files go to a place in the nagios user home directory.
The script identifies the location, or you pass it as a parameter, I don't remember.

So, I guess the answer is yes - you can run any SQL you want, capture the output, and use logic to decide OK, WARN, or CRIT.

It is not rocket science, you just have to be persistent in your testing.
Enable debug, run from the command line until you succeed, and only then transplant to the GUI.

Making the perl scripts run can be a challenge. I learned that you need to have an environment variable in place
named SYBASE and it needs to aim at the tsql location. For example, my tsql is here: /usr/bin/tsql so I have to have "export SYBASE=/usr" in place for my nagios user that runs the test. That env needs to be there before you install DBD::Sybase.

Remember, google is your friend.

I'm just a user like you. Not a Nagios employee. I hope my post helps.

Steve B

[lmiltchev]

Thanks for sharing @SteveBeauchemin !