How to check SQL jobs (particular jobs)

[dlukinski]

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

[mbellerue]

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;

[dlukinski]

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"

[mbellerue]

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

[dlukinski]

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

[mbellerue]

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.

[dlukinski]

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."

[mbellerue]

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.

[dlukinski]

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")

[mbellerue]

What is the actual result of the query?