MSSQL Query Issue

[FrontlineIT]

I added a screenshot of the result in SSMS when running the query

[mbellerue]

There must be an issue with the --result flag. I'll see about getting a bug filed for that. I was able to get it to work. If you make the aggregate field the last field selected, the check completes as expected.

[FrontlineIT]

You said you were able to get it to work? Can you elaborate more and provide an example?

[mbellerue]

In my setup, I had to get the AppID column to be the first column, and the aggregate column to be the second column in the result. But it's worth noting that in my setup I don't have a proper aggregate field. It might be worth going to your DBA to see if there's anything special that needs to be done, but I think you should just be able to put AppID as the first column in the select clause.

Original:
/usr/local/nagios/libexec/check_mssql -H ********** --username ***** --password "*****" --database ***** --query "select Max (DateDiff (ss, LastUpdate, GetDate())),AppID from appstatus (nolock) where appprefix = 'ComSrv' and appid = 'FE' and appinst = 51 and partitionnum >= 0 group by AppID" --result "" --decode --warning 50 --critical 200 --querycritical 180

Modified:
/usr/local/nagios/libexec/check_mssql -H ********** --username ***** --password "*****" --database ***** --query "select AppID,Max (DateDiff (ss, LastUpdate, GetDate())) from appstatus (nolock) where appprefix = 'ComSrv' and appid = 'FE' and appinst = 51 and partitionnum >= 0 group by AppID" --result "" --decode --warning 50 --critical 200 --querycritical 180

[FrontlineIT]

I edited the query to only return one column instead of two but I am still seeing the same issue. I tried with and without naming the naming the aggregate column. I also attached a screenshot from SSMS. Any suggestions?

Single Column, Aggregate Column Not Named:
--query "SELECT MAX(DateDiff (ss, LastUpdate, GetDate())) FROM appstatus (nolock) WHERE appprefix = 'ComSrv' AND appid = 'FE' AND appinst = 51 and partitionnum >= 0 GROUP BY AppID" --result "" --decode --warning 50 --critical 200 --querycritical 180


Single Column, Aggregate Column Named:
--query "SELECT MAX(DateDiff (ss, LastUpdate, GetDate())) AS [test] FROM appstatus (nolock) WHERE appprefix = 'ComSrv' AND appid = 'FE' AND appinst = 51 and partitionnum >= 0 GROUP BY AppID" --result "test" --decode --warning 50 --critical 200 --querycritical 180

[FrontlineIT]

Actually disregard that last post as I was just looking at the CCM output. See the new attached screenshot. Looks like the check is working!!!!

[FrontlineIT]

So all in all, your modified query worked. Thank you!!

Modified:
/usr/local/nagios/libexec/check_mssql -H ********** --username ***** --password "*****" --database ***** --query "select AppID,Max (DateDiff (ss, LastUpdate, GetDate())) from appstatus (nolock) where appprefix = 'ComSrv' and appid = 'FE' and appinst = 51 and partitionnum >= 0 group by AppID" --result "" --decode --warning 50 --critical 200 --querycritical 180

[mbellerue]

Excellent, glad to hear it's working! I will go ahead and close the thread.