Page 1 of 3
Monitor database query
Posted: Tue Aug 09, 2016 5:41 am
by 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
Re: Monitor database query
Posted: Tue Aug 09, 2016 8:46 am
by 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.
Re: Monitor database query
Posted: Tue Aug 09, 2016 9:46 am
by rkennedy
Are you using a domain account, or a SQL server account? At this time I don't believe the plugin supports domain authentication.
Re: Monitor database query
Posted: Tue Aug 09, 2016 10:10 am
by 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
Re: Monitor database query
Posted: Tue Aug 09, 2016 4:26 pm
by 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.
Re: Monitor database query
Posted: Wed Aug 10, 2016 5:01 am
by 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
Re: Monitor database query
Posted: Wed Aug 10, 2016 6:57 am
by 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!
Re: Monitor database query
Posted: Wed Aug 10, 2016 2:37 pm
by 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.
Re: Monitor database query
Posted: Wed Aug 10, 2016 3:41 pm
by 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
Re: Monitor database query
Posted: Thu Aug 11, 2016 10:49 am
by 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?