Nagios Support Forum

Please advise how to monitor MsSQL Jobs with XI standard plugins?

You would have to execute a query to find a running job. Here's what I have.
This returns,

mbellerue wrote:You would have to execute a query to find a running job. Here's what I have.

Code: Select all

/usr/local/nagios/libexec/check_mssql -H <IPAddress> -p <PortNum> -U <username> -P <password> -d <DBname> -q "SELECT sj.name FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL AND sj.name LIKE '<JobName>%'" -W 1 -C 2 --result name

This returns,

Code: Select all

array(2) {
  [0]=>
  array(1) {
    ["name"]=>
    string(12) "<JobName>"
  }
  [1]=>
  array(1) {
    ["name"]=>
    string(13) "<JobName2>"
  }
}
WARNING: Query result <JobName2> was higher than Query warning threshold 0.|query_duration=0.000576s;; 'name'=<JobName2>;0;

I can't execute the query for me:

Would this be a wrong command?

check_xi_mssql_query!--username "nagiosxi" --password "checkNAG1" --database Siebeldb_QA --port 1433 --query "SELECT+sj.name+FROM+msdb.dbo.sysjobactivity+AS+sja+INNER+JOIN+msdb.dbo.sysjobs+AS+sj+ON+sja.job_id+%3D+sj.job_id+WHERE+sja.start_execution_date+IS+NOT+NULL+AND+sja.stop_execution_date+IS+NULL+AND+sj.name+LIKE+%27cdc.Siebeldb_QA_capture%27" --decode --warning 50 --critical 100 --querywarning 1 --querycritical 2 --result "Expected result"

That looks generally correct. Can you try it without the query being encoded, and drop the --decode?

mbellerue wrote:That looks generally correct. Can you try it without the query being encoded, and drop the --decode?

Still no luck

Should I create a ticket

I think I've made a mistake in my command to you. I stated that you could specify a database, using the -d <DBName>. I think the master DB specifically is what tracks jobs in SQL Server. Try specifying master, and see if it comes back.

mbellerue wrote:I think I've made a mistake in my command to you. I stated that you could specify a database, using the -d <DBName>. I think the master DB specifically is what tracks jobs in SQL Server. Try specifying master, and see if it comes back.

Still no go: "CRITICAL: Could not execute the Query."

I'm not sure if it's failing because something is wrong with the query, or if something else is wrong. Let's scale this back and just see if you can run a simple select query. Try just selecting one field from one table with one result, and see if it's able to execute that.

mbellerue wrote:I'm not sure if it's failing because something is wrong with the query, or if something else is wrong. Let's scale this back and just see if you can run a simple select query. Try just selecting one field from one table with one result, and see if it's able to execute that.

Hi, query works now, but provides a strange result:

CRITICAL: Query result cdc.Siebeldb_QA_capture was higher than Query critical threshold 2.

- we need this to be critical if the job is not running (--querywarning 1 --querycritical 2 --result "Expected result")

What is the actual result of the query?