Userdefined Parameter help with monitoring into MSSQL database

[dtoepoel]

Hey folks,

I'm wanting to do some general query work on a custom application.

I've created a batch file that runs a custom query and parses the results back as a decimal number. (the query is a select count(*) )

The strange thing is... if I kick off the batch file script from the commandline, I get as output what I expect... the decimal value.

If I use zabbix_get function from the zabbix server terminal window I get ZBX_NotSupported.

I put in a Pause in my script and then instead of getting back:
19Press any key to continue
I get back
Press any key to continue

In other words. If Zabbix Agent service fires the item it won't give back the value I get if I run it manually from the command prompt.

The zabbix agent service runs as a local system service.

Here are the scripts provided.

aw_trans.cmd (Awaiting Transcription status query)

@echo off >c:\zabbix\aw_trans.txt
call c:\zabbix\zabbosql.cmd c:\zabbix\aw_trans.sql >>c:\zabbix\aw_trans.txt
type c:\zabbix\aw_trans.txt
pause --> added for debugging
rem del c:\zabbix\aw_trans.txt ; remarked for debugging.

ZABBOSQL.CMD (general script running SQLCMD for commandline query)

@echo off
setLocal EnableDelayedExpansion
set OsqlCmd="sqlcmd.exe -UNOTAREALUSER -PNOTAREALPW -SHOSTNAME -dDATABASENAME -i %1"
for /f "tokens=* delims= " %%a in ('%osqlCmd%') do (
set /a N+=1
if !N! neq 1 if !N! neq 2 if !N! neq 4 if !N! neq 5 call c:\zabbix\chomp.cmd %%a
)

Chomp.cmd (basic echo function without linefeed at the end like echo does, uses Kix32)

@ECHO OFF
:: Command line check
IF "%1"=="" GOTO Syntax
IF NOT "%OS%"=="Windows_NT" GOTO Syntax

:: Keep variables local
SETLOCAL

:: Strip leading space added by Windows NT 4
SET STRING=%*
VER | FIND "Windows NT" >NUL
IF NOT ERRORLEVEL 1 SET STRING=%STRING:~1%

:: Create temporary Kix script
:: (Kix displays strings without carriage returns by default)
(ECHO $RC=REDIRECTOUTPUT^("%%TEMP%%.\CHOMP.DAT"^)) > %TEMP%.\CHOMP.KIX
(ECHO."%STRING%") >> %TEMP%.\CHOMP.KIX
(ECHO $RC=REDIRECTOUTPUT^(""^)) >> %TEMP%.\CHOMP.KIX
:: Call the temporary Kix script
KIX32 %TEMP%.\CHOMP.KIX
:: Display the temporary file created by the temporary Kix script
TYPE %TEMP%.\CHOMP.DAT
:: Delete the temporary files
DEL %TEMP%.\CHOMP.KIX
DEL %TEMP%.\CHOMP.DAT

:: Done
ENDLOCAL
GOTO:EOF


:Syntax
(ECHO.) 1>&2
(ECHO Chomp.bat, Version 1.01 for Windows NT 4/2000) 1>&2
(ECHO Displays command line parameters on screen) 1>&2
(ECHO ^(on StdOut^) without a carriage return.) 1>&2
(ECHO.) 1>&2
(ECHO Usage:   %~n0  ^<string^>) 1>&2
(ECHO.) 1>&2
(ECHO Output:  ^<string^> without carriage return on StdOut) 1>&2
(ECHO.) 1>&2
(ECHO Remarks: [1] ^<string^> should NOT contain any double quotes!) 1>&2
(ECHO          [2] Windows 2000 will strip leading spaces from ^<string^>) 1>&2
(ECHO          [3] This batch file uses Kix, make sure it is installed) 1>&2
(ECHO              and that KIX32.EXE can be found in the PATH) 1>&2
(ECHO.) 1>&2
(ECHO Example: ^> SETDATE.BAT ECHO @ECHO OFF) 1>&2
(ECHO          ^>^>SETDATE.BAT CALL %~nx0 SET DATE=) 1>&2
(ECHO          ^>^>SETDATE.BAT DATE/T) 1>&2
(ECHO          CALL SETDATE.BAT) 1>&2
(ECHO          SET DATE) 1>&2
(ECHO will display:) 1>&2
FOR /F "tokens=*" %%A IN ('DATE/T') DO (ECHO          DATE=%%A) 1>&2
(ECHO.) 1>&2
(ECHO Written by Rob van der Woude) 1>&2
(ECHO http://www.robvanderwoude.com) 1>&2

and finally... the SQL query being executed.

Aw_trans.sql (SQL query being called in zabbosql.cmd)

select count(*) from g2document where statusid=6

;

So basically... summing up... the scripts run the aw_trans.sql and strip the output down to just the decimal value (in this case... 19)
19 gets parsed through chomp and outputted to the screen and to the .txt file... which then gets typed back to the terminal output.

Ambitious... dunno... maybe I'm doing things inefficiently?
Basically what I want to do is run a sql query, get -just- the relevant output and parse that back to Zabbix where I can set a trigger on it.

The silly thing is, is the above thing works from the command line input on the zabbix agent windows box, but not when run from zabbix_get.

I could setup a System Task to dump a new .txt file every 5 minutes overwriting the previous one and then let zabbix use type .txt filename to retrieve the new value but that kind of defeats the purpose. I want to run it all from the zabbix server side of things.

Help... anyone?

Greets, Dennis Toepoel.

[dtoepoel]

*bump*

Nobody has an idea?

Basically what I want is to just do a select count(*) on a sql database table (with sqlcmd or osql or whatnot) and have just the output value returned to zabbix as a decimal value.

Any idea how to go about that?

I think my scripted version posted earlier doesn't work because of security reasons... apparently it doesn't work calling out other commands from the Userparameter value. (like other batch files or grep or tail or whatnot)

Greets, Dennis

[GArmao]

I have the same problem, I found out why it is happening but I haven't found any solution yet.

Basically I wrote a simple .bat script that executes some commands and returns a value (0,1) based on the result of the other commands.
If I run the script locally everything works just fine the output is a number as expected, but if I run it through zabbix I get a ZBX_NotSupported.
The reason is because for some reason, all the commands run by zabbix ignore the @echo off statement, and this is what zabbix server gets as item data:

27208:20100311:134527.444 Item [ora9-10:system.run["c:\program files\zabbix agent\script\audit.bat"]] error: Type of received value [sqlplus -s %ORA_USER%/%ORA_PASS%@%ORACLE_SID% @"%ZABBIX_HOME%\script\audit.sql" %LASTDATE_AUDIT% >> %AUDIT_LOG%] is not suitable for value type [Numeric (integer 64bit)]

which is of course "not supported" as zabbix expects to receive an int value.

any hint?

[dtoepoel]

GArmao>> I actually managed to solve my problem, but you're not going to like how I did it.

One of our programmers wrote a small .exe file that used our application's ADO connection to get my SQL count statements and return only the output.

All parsing and formatting of the code was done in the .exe ; no baggage.

Though thanks for letting me know about the fact that the @echo off gets ignored. At least now I know why it wasn't working before.

Anyone has any idea on how to work around it (aside from writing your own .exe tool?)

Cheers, Dennis.