MSSQL Query Configuration Wizard - Error

[hillhealthcenter]

Hi,

I get an error when I try to use the MSSQL Query Configuration Wizard. I've used other MSSQL wizards succeessfully. This is my 1st attempt with this wizard.

UNKNOWN: Invalid characters in the hostname.

Here's the config:

Code: Select all

define service {
	host_name			centcshdb
	service_description		MSSQL Query - VisDental_SignOBSFix
	use				xiwizard_mssqlquery_service
	check_command			check_xi_mssql_query!--username sa --password "##########" --database master --query "exec+CPS.SignObservationFix" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200 --result Expected result
	max_check_attempts		5
	check_interval			5
	retry_interval			1
	check_period			xi_timeperiod_24x7
	notification_interval		60
	notification_period		xi_timeperiod_24x7
	contacts			transom
	_xiwizard			mssqlquery
	register			1
	}

[lmiltchev]

What is the version of Nagios XI that you are currently using? What about the version of the "check_mssql" plugin? Run the following command and show the output:

Code: Select all

/usr/local/nagios/libexec/check_mssql -V

Do you have any special characters in the password, such as "$"? Let's see the output of the following commands, run from the command line on your Nagios XI server:

Code: Select all

/usr/local/nagios/libexec/check_mssql_database.py -H <client ip> -U 'sa' -P '<password>' -T 'master' --time2connect
/usr/local/nagios/libexec/check_mssql -H <client ip> --username sa --password "<password>" --database master --query "exec+CPS.SignObservationFix" --result "Expected result" --decode --warning 50 --critical 200

[hillhealthcenter]

Nagios XI Installed Version: 5.4.5

Code: Select all

/usr/local/nagios/libexec$ /usr/local/nagios/libexec/check_mssql -V
0.7.3

The password does contain @ and !

Code: Select all

/usr/local/nagios/libexec$ /usr/local/nagios/libexec/check_mssql_database.py -H 192.168.103.21 -U 'sa' -P '************' -T 'master' --time2connect
OK: Time to connect was 0.0076858997345s|time=0.0076858997345s;;;;

Code: Select all

/usr/local/nagios/libexec$ /usr/local/nagios/libexec/check_mssql -H 192.168.103.21 --username sa --password "************" --database master --query "exec+CPS.SignObservationFix" --result "Expected result" --decode --warning 50 --critical 200
CRITICAL: Could not execute the query.

[lmiltchev]

Try wrapping the password or/and the query in single quotes, instead of double to see if this is going to make a difference. Can you run a different query successfully? Is this the only query you are having issues with? Can you show us the query, run locally on the MSSQL server?

[hillhealthcenter]

I'm beginning to wonder if this wizard is appropriate for our needs. Let me try to explain what we'd like to accomplish.

1. Have a schedule SQL job scheduled to run once daily.
2. We like to trigger a warning if the job runs longer than 1 minute and receive a notification.

Is this the right check based on the above criteria?

[dwhitfield]

How soon do you need to know if it's taking longer than a minute? You are currently checking every 5 minutes (with a max_check_attempts of 5). Perhaps a passive check would make more sense in this case?

For more detail on passive checks, see https://assets.nagios.com/downloads/nag ... ios-XI.pdf

[lmiltchev]

Maybe you can come up with a custom (i.e.PowerShell) script, than can be called from Nagios XI via check_ncpa.py or check_nrpe, provided you have installed an agent on the server (NCPA or NSClient++).

The PS script could be something like this:

Code: Select all

$StartTime = Get-Date
Invoke-SqlCmd -ServerInstance localhost -Database YourDB -InputFile ‘.\YourSQLFile.sql’
$EndTime = Get-Date
$Duration = New-TimeSpan -Start $StartTime -End $EndTime

You can add to it (args for warning/critical thresholds, etc.).

[hillhealthcenter]

I'll take a look at both approaches. I appreciate your help.

[lmiltchev]

You are welcome! Do you want us to keep this thread open for a while or it is ok to lock it?

[hillhealthcenter]

You can close this post.