Monitor database query

[Naveed]

Hello,

Can anyone let me that how to monitor database query in nagios.

I have checked database query configurations wizard. I need to know how to monitor this below query.

My query is as follows:

select CAPTION, STATUS,
CASE
WHEN STATUS = 3 THEN 'Error'
WHEN status = 4 THEN 'Finished'
WHEN status = 8 THEN 'Cancelled'
ELSE 'Unknown'
END AS JobStatus,
BATCHJOBID,
CANCELEDBY,
dateadd(hour, -@offset, STARTDATETIME) as StartTime, dateadd(hour, -@offset, ENDDATETIME) as EndTime,
format(ENDDATETIME - STARTDATETIME, 'HH:mm:ss') as RunTime
from BATCHJOBHISTORY
where dateadd(hour, -7,STARTDATETIME) >= getdate() -1
and status in (3,8)
--order by "end time" desc, status, RunTime DESC

[Naveed]

My query and command is follows:

Code: Select all

check_xi_mssql_query!--username 'abcd\test' --password "1234" --database master --port 1433 --query "select++CAPTION%2C+STATUS%2C%0D%0A++CASE+%0D%0A++++WHEN+STATUS+%3D+3+THEN+%27Error%27%0D%0A++++++++++++++++WHEN+status+%3D+4+THEN+%27Finished%27%0D%0A++++++++++++++++WHEN+status+%3D+8+THEN+%27Cancelled%27%0D%0A++++++++++++++++ELSE+%27Unknown%27%0D%0A++END+AS+JobStatus%2C+%0D%0A++BATCHJOBID%2C+%0D%0A++CANCELEDBY%2C%0D%0A++dateadd%28hour%2C+-%40offset%2C+STARTDATETIME%29+as+StartTime%2C+dateadd%28hour%2C+-%40offset%2C+ENDDATETIME%29+as+EndTime%2C%0D%0Aformat%28ENDDATETIME+-+STARTDATETIME%2C+%27HH%3Amm%3Ass%27%29+as+RunTime+%0D%0Afrom+BATCHJOBHISTORY%0D%0Awhere+dateadd%28hour%2C+-7%2CSTARTDATETIME%29+%3E%3D+getdate%28%29+-1+%0D%0Aand+status+in+%283%2C8%29%0D%0A--order+by+%22end+time%22+desc%2C+status%2C+RunTime+DESC" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200 --result Expected result

This is giving below error message

UNKNOWN: Invalid characters in the username.

[rkennedy]

Are you using a domain account, or a SQL server account? At this time I don't believe the plugin supports domain authentication.

[Naveed]

Thank you!

Yes it was domain user,

I have changed it to local user on sql server and it throws the below error now..


(No output on stdout) stderr: /bin/sh: -c: line 0: unexpected EOF while looking for matching `"'
/bin/sh: -c: line 1: syntax error: unexpected end of file

[tmcdonald]

Looks like you specify both --result "Expected result" (correct) and --result Expected result (incorrect). Get rid of the last one and try it again.

[Naveed]

Thank you!

I have removed the last entry but its still giving the same error message. I think I am missing something.

I have also tried to execute it from CLI and found the below message...

./check_mssql -H 1.2.3.4 --username 'abcd' --password '1234' --database master --port 1433 --query "select++CAPTION%2C+STATUS%2C%0D%0A++CASE+%0D%0A++++WHEN+STATUS+%3D+3+THEN+%27Error%27%0D%0A++++++++++++++++WHEN+status+%3D+4+THEN+%27Finished%27%0D%0A++++++++++++++++WHEN+status+%3D+8+THEN+%27Cancelled%27%0D%0A++++++++++++++++ELSE+%27Unknown%27%0D%0A++END+AS+JobStatus%2C+%0D%0A++BATCHJOBID%2C+%0D%0A++CANCELEDBY%2C%0D%0A++dateadd%28hour%2C+-%40offset%2C+STARTDATETIME%29+as+StartTime%2C+dateadd%28hour%2C+-%40offset%2C+ENDDATETIME%29+as+EndTime%2C%0D%0Aformat%28ENDDATETIME+-+STARTDATETIME%2C+%27HH%3Amm%3Ass%27%29+as+RunTime+%0D%0Afrom+BATCHJOBHISTORY%0D%0Awhere+dateadd%28hour%2C+-7%2CSTARTDATETIME%29+%3E%3D+getdate%28%29+-1+%0D%0Aand+status+in+%283%2C8%29%0D%0A--order+by+%22end+time%22+desc%2C+status%2C+RunTime+DESC" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200
CRITICAL: Could not execute the query.

Please suggest.
Thanks

[Naveed]

I have attached my query command and service description as well for further analysis.

kindly check and guide me if something need to be changed now.

Thank you!

[rkennedy]

Can you show us exactly what $ARG3$ is equal to? The query you've pasted above is starting with 'select', where as your XI is using SELECT.

[Naveed]

--database master --port 1433 --query "SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200

[lmiltchev]

Try running:

Code: Select all

/usr/local/nagios/libexec/check_mssql --username 'abcd' --password "1234" --database master --port 1433 --query "SELECT COUNT%28%2A%29 FROM sys.sysperfinfo" --result "Expected result" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200

Did this help?