Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
Directory Tree
Check MSSQL Database Stats
1.1
2011-09-10
- Nagios 3.x
- Nagios XI
GPL
151569
Meet The New Nagios Core Services Platform
Built on over 25 years of monitoring experience, the Nagios Core Services Platform provides insightful monitoring dashboards, time-saving monitoring wizards, and unmatched ease of use. Use it for free indefinitely.
Monitoring Made Magically Better
- Nagios Core on Overdrive
- Powerful Monitoring Dashboards
- Time-Saving Configuration Wizards
- Open Source Powered Monitoring On Steroids
- And So Much More!
This plugin is geared explicitly for gathering information on specific databases rather than the database as a whole.
I also added a doc on the exchange, please refer to it if you're connecting to instances on an MSSQL database. It says Nagios XI on it but will still apply to Nagios Core as well:
https://assets.nagios.com/downloads/nagiosxi/docs/Nagios_XI_Installing_MSSQL_Wizard_Prereqs.pdf
Installation of the plugin is easy and involves a call to the python plugin pymssql which is included with most packaging software. For Ubuntu:
sudo apt-get install python-pymssql
For Red Hat based:
yum install pymssql # Some repos have it python-pymssql
Then it should run no problem. I am *VERY* open to feature additions and bugs, so please bring them to my attention if you find them/have a need for them.
Examples of use are:
./check_mssql_database.py -H 192.168.0.1 -U user -p password --transpsec -w 20 -c 30
Now keep in mind if you're going to using percentages the w and c get a little trickier. This plugin is Nagios Plugin compliant as far as the bounding goes, fully supporting the use of the : ~ and @ symbols. Here is an example of what you'd do for the warns and crits on Log Cache Hit Ratio:
./check_mssql_database.py -H 192.168.0.1 -U user -p password --logcachehit -w 95: -c 90:
This will return warning of the percentage goes below 95% and critical if it goes below 90%.
For information the the use of ~ : and @, refer to section 2.5 of the Nagios Plugin Guidline here: http://nagiosplug.sourceforge.net/developer-guidelines.html
Metrics that it currently covers:
--datasize Check size of database
--logfileusage Check Log File Usage
--activetrans Check Active Transactions
--transpsec Check Transactions/Sec
--logcachehit Check Log Cache Hit Ratio
--time2connect Check Time to Connect [DEFAULT]
--loggrowths Check Log Growths
--logshrinks Check Log Shrinks
--logtruncs Check Log Truncations
--logwait Check Log Flush Wait Times
--logflushes Check Log Flushes/Sec
These are for specific databases on a MSSQL, if you want to see these metrics applied on your entire database, you can specify the database _Total, but again, this plugin is geared towards checking specific databases.
Be sure to take advantage of using Python by compiling this plugin to a pyc if you're running lots of checks with it!
Changelog:
1.1 - Fixed port bug that only allowed default port. Thanks for the tip CBTSDon!
Fixed ungraceful exit if mode not specified | Thanks mike from austria
1.3 - Added ability specify instances.
I also added a doc on the exchange, please refer to it if you're connecting to instances on an MSSQL database. It says Nagios XI on it but will still apply to Nagios Core as well:
https://assets.nagios.com/downloads/nagiosxi/docs/Nagios_XI_Installing_MSSQL_Wizard_Prereqs.pdf
Installation of the plugin is easy and involves a call to the python plugin pymssql which is included with most packaging software. For Ubuntu:
sudo apt-get install python-pymssql
For Red Hat based:
yum install pymssql # Some repos have it python-pymssql
Then it should run no problem. I am *VERY* open to feature additions and bugs, so please bring them to my attention if you find them/have a need for them.
Examples of use are:
./check_mssql_database.py -H 192.168.0.1 -U user -p password --transpsec -w 20 -c 30
Now keep in mind if you're going to using percentages the w and c get a little trickier. This plugin is Nagios Plugin compliant as far as the bounding goes, fully supporting the use of the : ~ and @ symbols. Here is an example of what you'd do for the warns and crits on Log Cache Hit Ratio:
./check_mssql_database.py -H 192.168.0.1 -U user -p password --logcachehit -w 95: -c 90:
This will return warning of the percentage goes below 95% and critical if it goes below 90%.
For information the the use of ~ : and @, refer to section 2.5 of the Nagios Plugin Guidline here: http://nagiosplug.sourceforge.net/developer-guidelines.html
Metrics that it currently covers:
--datasize Check size of database
--logfileusage Check Log File Usage
--activetrans Check Active Transactions
--transpsec Check Transactions/Sec
--logcachehit Check Log Cache Hit Ratio
--time2connect Check Time to Connect [DEFAULT]
--loggrowths Check Log Growths
--logshrinks Check Log Shrinks
--logtruncs Check Log Truncations
--logwait Check Log Flush Wait Times
--logflushes Check Log Flushes/Sec
These are for specific databases on a MSSQL, if you want to see these metrics applied on your entire database, you can specify the database _Total, but again, this plugin is geared towards checking specific databases.
Be sure to take advantage of using Python by compiling this plugin to a pyc if you're running lots of checks with it!
Changelog:
1.1 - Fixed port bug that only allowed default port. Thanks for the tip CBTSDon!
Fixed ungraceful exit if mode not specified | Thanks mike from austria
1.3 - Added ability specify instances.
Reviews (8)
byWSergii, February 3, 2019
What minimum rights should the user have for the script to work correctly?
byrblaas1975, February 15, 2018
Hi,
I think you should update the documentation..
as for the check_mssql_database.py there is an option mandatory -T
I cannot read anything about this.
As said I am unable to get a database size
Could you please update the documentation?
I think you should update the documentation..
as for the check_mssql_database.py there is an option mandatory -T
I cannot read anything about this.
As said I am unable to get a database size
Could you please update the documentation?
bysnord, March 2, 2016
I have grabbed Chad's updated plugin as SQL2012 relies on sys.dm_os_performance_counters. Only the default time2connect metric works. Any other metric throws:
Caught unexpected error. This could be caused by your sys.dm_os_performance_counters not containing the proper entries for this query, and you may delete this service check.
I have verified I can get the data with a manual tsql query.
Caught unexpected error. This could be caused by your sys.dm_os_performance_counters not containing the proper entries for this query, and you may delete this service check.
I have verified I can get the data with a manual tsql query.
byChadSikorra, January 13, 2014
Thanks for this plugin. By far the easiest to setup and use for MS SQL checks. I've done some modifications to allow checking all databases in an instance in addition to just a specific one. Perhaps check it out sometime and merge the changes if possible?
https://github.com/ChadSikorra/check_mssql_collection
https://github.com/ChadSikorra/check_mssql_collection
Hello,
In http://support.nagios.com/forum/viewtopic.php?f=16&t=21791
I've mentioned a bug.
Another issue in the check_mssql_database.py we noticed is that
[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]
In fact does the same as
[code]'loggrowths' : { 'help' : 'Log Growths',
'stdout' : 'Log Growths is %s',
'label' : 'log_growths',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]
So we changed this manually to
[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Data File(s) Size (KB)',
'type' : 'standard'
},[/code]
After which we got the corrrect DB Size. Maybe you can investigate this and if you agree, update the check_mssql_database.py plugin?
In http://support.nagios.com/forum/viewtopic.php?f=16&t=21791
I've mentioned a bug.
Another issue in the check_mssql_database.py we noticed is that
[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]
In fact does the same as
[code]'loggrowths' : { 'help' : 'Log Growths',
'stdout' : 'Log Growths is %s',
'label' : 'log_growths',
'query' : BASE_QUERY % 'Log Growths',
'type' : 'standard'
},[/code]
So we changed this manually to
[code]'datasize' : { 'help' : 'Database Size',
'stdout' : 'Database size is %sKB',
'label' : 'KB',
'query' : BASE_QUERY % 'Data File(s) Size (KB)',
'type' : 'standard'
},[/code]
After which we got the corrrect DB Size. Maybe you can investigate this and if you agree, update the check_mssql_database.py plugin?
byHamzab, September 28, 2013
Thank you for this great plugin.
Though, with some databases I get the SQL Server message 208 with a message "Invalid object name 'sysperfinfo'."
any idea about the cause of that?
thanks
Though, with some databases I get the SQL Server message 208 with a message "Invalid object name 'sysperfinfo'."
any idea about the cause of that?
thanks
byazn.junix, December 13, 2011
How do i set the option to connect to SQL Server with instance?
bymeatball, November 1, 2011
Really great plugin.
The only thing i would like is to get the DB size in bytes or MB
Thanks!
The only thing i would like is to get the DB size in bytes or MB
Thanks!