Ad Widget

**Padawan.AVT** · 14-07-2009, 19:14

Ok, I did it!
SQL query works great!
I even managed to do multiline queries through unixODBC (although I had to rewrite some code there).

I'll post a full guide on this tomorrow night.

**jjer** · 17-07-2009, 06:43

im looking forward to the guide!

**Padawan.AVT** · 18-07-2009, 16:18

I'm sorry for took so long...

How to make SQL work on Zabbix:

At first, we need the following packages:
unixODBC 2.14 or 2.15pre (in source codes!)
Oracle client (there is ODBC library on Oracle's site, but it still needs other libs from Oracle client)

unixODBC has a few bugs, that you'll have to correct manualy before compilation that's why we need the source code.

At first, in file ./DriverManager/SQLConnect.c replace the lines

Code:

SQLUSMALLINT supported;

with

Code:

SQLUSMALLINT supported = SQL_TRUE;

(it will occur in code twice). This will fix the problem concerning

Code:

[IM003]unixODBCDriver ManagerSpecified driver could not be loaded
ISQLERROR: Could not SQLAllocStmt

error
(big thanks to the guys from Oracle forum for this fix! http://forums.oracle.com/forums/thre...t=15&tstart=60 )

Then edit file ./exe/isql.c and remove this block of code:

Code:

        if ( bNewStyle )
        {
            if ( len >= 2 && line[ 0 ] == '-' && line[ 1 ] == '-' )
            {
                /* 
                 * it can't have been malloc'd
                 */
                continue;
            }
        }

This code cause the bug when you're enabling multiline support in isql. There is a memory leak and after execution of those lines variable “len” became undefined. In any way, these lines are responsible for handling commets in Orqcle SQLs so we can safely remove them, just don't forget that from now your isql will not support comments

Also, fix this line

Code:

if ( len > 0 && line[ len - 1 ] == ';' )

as following

Code:

if ( len > 0 && line[ len - 2 ] == ';' )

(one of the oldest bugs in all history of programming – miscount on 1 char

)

Great! Now you can safely compile your unixODBC package

Code:

./configure –enable-gui=no # as I said, I did this on Solaris and I don't need any GUI in my console
make
make install

Now let's deal with Oracle.
At first, ensure that Oracle libs are in your $LD_LIBRARY_PATH:

Code:

echo $LD_LIBRARY_PATH

unixODBC by default compiles as 32-bit application, so make sure that in $LD_LIBRARY_PATH the Oracle libs are found in Oracle client's folder in directory lib32, not lib (where 64-bit libs reside)

Now check in $TNS_ORA/tnsnames.ora exist and contains correct TNS entry:

Code:

cat $TNS_ORA/tnsnames.ora

pick any SID there

Code:

tnsping <SID>

Ok. As you now ensured that Oracle is accessible by client, you should edit two files : /etc/odbc.ini and /etc/odbcinst.ini as below:

Code:

cd /etc
cat odbcinst.ini

[Oracle10g]
Description     = Oracle ODBC driver for Oracle 10g 32bit
Driver          = /data/pandora/local/10.2.0.3clnt/lib32/libsqora.so.10.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

Code:

cat odbc.ini
[DOCSDB]
Driver  = /data/pandora/local/10.2.0.3clnt/lib32/libsqora.so.10.1
#Driver  = Oracle10g
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DSN = XE2
DisableDPM = F
DisableMTS = T
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = DOCSDBP1.world  <===== This must be SID from your tnsnames.ora !!!
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
LogFile=/data/pandora/local/var/sql.log

Great! We're done! You need only to check if isql works:

Code:

isql <DSN_name> [<user>] [<password>] [-v] [-n]

-v - for verbose
-n - for multiline mode

if you turn on multiline mode, keep in mind that your SQL query must ends with semicolon.

P.S. Don't forget recompile Zabbix with unixODBC support

-----------
Best regards,
Padawan.AVT

**Padawan.AVT** · 20-07-2009, 18:58

I've stopped at the point realizing that Zabbix itself do not support multi-line queries.
Hm. I think we'll teach him to!

But first I have a question to the dev team:
In zabbix-1.6.4/src/zabbix_server/poller there is a file check_db.c

In function get_param_value there are few lines that trim EOL characters. If I modify them in a way that for parameter sql= the EOL char will be semicolon ";", will the function odbc_DBselect (and in the end the function SQLExecDirect ) accept multiline string correctly, having my isql already accepts multi-line queries.

I'm asking cause I'll be at my experimental environment only on Wednesday and I don't want to waste time knowing I may find another solution to this problem if current won't work.

**rizwank** · 01-01-2010, 02:57

Any luck getting multiline working?

**Padawan.AVT** · 15-02-2010, 19:44

Yep! The code is below:

Works for zabbix 1.6-1.8 (and may be earlier versions as well)
./src/zabbix_server/poller/checks_db.c

Code:

/*
** ZABBIX
** Copyright (C) 2000-2010 SIA Zabbix
**
** This program is free software; you can redistribute it and/or modify
** it under the terms of the GNU General Public License as published by
** the Free Software Foundation; either version 2 of the License, or
** (at your option) any later version.
**
** This program is distributed in the hope that it will be useful,
** but WITHOUT ANY WARRANTY; without even the implied warranty of
** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
** GNU General Public License for more details.
**
** You should have received a copy of the GNU General Public License
** along with this program; if not, write to the Free Software
** Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
**/

#include "common.h"

#ifdef HAVE_ODBC
#	include "zbxodbc.h"
#endif /* HAVE_ODBC */

#include "checks_db.h"
#include "log.h"

#ifdef HAVE_ODBC
/******************************************************************************
 *                                                                            *
 * Function: get_param_value                                                  *
 *                                                                            *
 * Purpose: retrieve parameter value by name                                  *
 *                                                                            *
 * Parameters: params - list of params                                        *
 *             param_name - name of requested parameter                       *
 *                                                                            *
 * Return value: NULL - if parameter missing param_name,                      *
 *               else return value in new allocated memory                    *
 *                                                                            *
 * Author: Eugene Grigorjev                                                   *
 *                                                                            *
 * Comments: this function allocates memory, required zbx_free for result!!!  *
 *           one parameter format: param1_name=param1_value                   *
 *           parameters separated by '\n'                                     *
 *                                                                            *
 ******************************************************************************/
static char* get_param_value(char* params, const char* param_name)
{
	char
		*p = NULL,
		*l = NULL,
		*n = NULL,
		*r = NULL,
		*buf = NULL;

	for(p = params; p && *p; p++)
	{
		r = NULL;

		/* trim left spaces */
		for(; *p == ' '; p++);

		/* find '=' symbol */
		for(n = p; *n && *n != '\n'; n++)
		{
			if(*n == '=')
			{
				/* trim right spaces */
				for(l = n - 1; *l == ' '; l--);
				l++;

				/* compare parameter name */
				if(l - p != strlen(param_name))		break;
				if(strncmp(p, param_name, l - p))	break;

				r = n+1;
				break;
			}
		}

		/* find EOL */
		for(p = n; *p && *p != '\n'; p++);

		/* allocate result */
		if(r)
		{
			/* trim right EOL symbols */
			while(*p == '\r' || *p == '\n' || *p == '\0') p--;
			p++;

			/* allocate result */
			buf = zbx_malloc(buf, p - r + 1);
			memmove(buf, r, p - r);
			buf[p - r] = '\0';

			break;
		}
	}

	if(buf == NULL)
	{
		/* allocate result */
		buf = zbx_malloc(buf, 1);
		*buf = '\0';
	}
	return buf;
}


/******************************************************************************
 *                                                                            *
 * Function: get_param_value_new                                                  *
 *                                                                            *
 * Purpose: retrieve parameter value by name. Difference from the previous function is that get_param_value_new works with multiple lines of parameter value                                *
 *                                                                            *
 * Parameters: params - list of params                                        *
 *             param_name - name of requested parameter                       *
 *                                                                            *
 * Return value: NULL - if parameter missing param_name,                      *
 *               else return value in new allocated memory                    *
 *                                                                            *
 * Author: Eugene Grigorjev, modifications by Roman Kharytonov                                                  *
 *                                                                            *
 * Comments: this function allocates memory, required zbx_free for result!!!  *
 *           one parameter format: param1_name=param1_value                   *
 *           parameters separated by '\n'                                     *
 *                                                                            *
 ******************************************************************************/



static char* get_param_value_new(char* params, const char* param_name)
{
	char 
		*p = NULL,
		*l = NULL,
		*n = NULL,
		*r = NULL,
		*buf = NULL;

size_t length_p;

	for(p = params; p && *p; p++)
	{
		r = NULL;

		/* trim left spaces */
		for(; *p == ' '; p++);

		/* find '=' symbol */
		for(n = p; *n && *n != '\n'; n++)
		{
			if(*n == '=')
			{
				/* trim right spaces */
				for(l = n - 1; *l == ' '; l--);
				l++;

				/* compare parameter name */
				if(l - p != strlen(param_name))		break;
				if(strncmp(p, param_name, l - p))	break;
				
				r = n+1;
				break;
			}
		}

		/* find EOL */
		for(p = n; *p && *p != '\n'; p++);
		
		/* allocate result */
		if(r)
		{

			/* trim right EOL symbols */
			
                length_p= strlen(r);

			/* allocate result */
			buf = zbx_malloc(buf, length_p + 1); 
			memmove(buf, r,length_p);

            buf[length_p+1] = '\0';

		break;
		}
	}
	
	if(buf == NULL)
	{
		/* allocate result */
		buf = zbx_malloc(buf, 1);
		*buf = '\0';
	}

	return buf;
}






#endif /* HAVE_ODBC */

/******************************************************************************
 *                                                                            *
 * Function: get_value_db                                                     *
 *                                                                            *
 * Purpose: retrieve data from database                                       *
 *                                                                            *
 * Parameters: item - item we are interested in                               *
 *                                                                            *
 * Return value: SUCCEED - data successfully retrieved and stored in result   *
 *               NOTSUPPORTED - requested item is not supported               *
 *                                                                            *
 * Author: Eugene Grigorjev, modifications by Roman Kharytonov                                                   *
 *                                                                            *
 * Comments:                                                                  *
 *                                                                            *
 ******************************************************************************/
int	get_value_db(DC_ITEM *item, AGENT_RESULT *result)
{
#ifdef HAVE_ODBC
	ZBX_ODBC_DBH	dbh;
	ZBX_ODBC_ROW	row;

	char
		*db_dsn = NULL,
		*db_user = NULL,
		*db_pass = NULL,
		*db_sql = NULL,
                *db_sql1 = NULL;
size_t length_p;
#endif /* HAVE_ODBC */

	int	ret = NOTSUPPORTED;

	init_result(result);

	zabbix_log(LOG_LEVEL_DEBUG, "In database monitor: %s", item->key_orig);

#ifdef HAVE_ODBC

	#define DB_ODBC_SELECT_KEY "db.odbc.select["

	if(strncmp(item->key, DB_ODBC_SELECT_KEY, strlen(DB_ODBC_SELECT_KEY)) == 0)
	{
		db_dsn = get_param_value(item->params,"DSN");
		db_user = get_param_value(item->params,"user");
		db_pass = get_param_value(item->params,"password");
		db_sql  = get_param_value_new(item->params,"sql");

		if( SUCCEED == odbc_DBconnect(&dbh, db_dsn, db_user, db_pass) )
		{
			if( NULL != (row = odbc_DBfetch(odbc_DBselect(&dbh, db_sql))) )
			{
				if (SUCCEED == set_result_type(result, item->value_type, item->data_type, row[0]))
					ret = SUCCEED;
			}
			else
			{
				SET_MSG_RESULT(result, strdup(get_last_odbc_strerror()));
			}
			odbc_DBclose(&dbh);
		}
		else
		{
			SET_MSG_RESULT(result, strdup(get_last_odbc_strerror()));
		}

		zbx_free(db_dsn);
		zbx_free(db_user);
		zbx_free(db_pass);
		zbx_free(db_sql);
	}

#endif /* HAVE_ODBC */

	/*
	 * TODO:
	 *
	 * db.*.select[]
	 * db.*.ping
	 *   ...
	 *
	 */

	return ret;
}

One should keep in mind that SQL parameter ALWAYS must me the last in the list of parameters for db.odbc.select[] items in Zabbix frontend:

Code:

DSN=APPUAT
user=username
password=passwd
sql=SELECT t.user_name
FROM dm_group_rp gr, dm_group_sp sp, DM_USER_S t
WHERE 
UPPER(t.user_address) = '[email protected]'
AND gr.USERS_NAMES = t.user_name 
AND sp.R_OBJECT_ID = gr.R_OBJECT_ID
AND ROWNUM=1
AND SYSDATE - 100 =SYSDATE - 100;

As you see, this query has 270 chars and 8 lines, thus surpassing the limit from my previous solution in 1 line and 255 chars per query.

And last thing: as you noticed, get_param_value_new function should be used only for db_sql variable ("sql" parameter), for all other you should use get_param_value

I'm in a hurry right now, so if you'll find any bugs or mistakes in my code, please tell me at once.

-=-=-=-=-=-=-
P.S. Now I may consider myself as true Zabbix developer

-=-=-=-=-=-=-

P.P.S. I guess, it's time for somebody to remake this thread into usefull F.A.Q. or manual on monitoring databases in Zabbix

**rizwank** · 15-02-2010, 19:59

=)

"And last thing: as you noticed, get_param_value_new function should be used only for db_sql variable ("sql" parameter), for all other you should use get_param_value "

Is this something end users have to worry about or only folks developing with the new functions?

Also, have you tested this on 1.8.1?

**Padawan.AVT** · 15-02-2010, 21:32

Only for developer folks

If you just replace the file with my modifications, you shouldn't worry about that.

I guess this will work on 1.8.1 cause source code of this .c file hasn't changed since 2005

P.S. In the nearest time (hopefully tomorrow night) I'll provide an updated version of this with fix so SQL value could be in any place in parameters, not in the very end. I already know how to do it, but I was in a hurry and couldn't implement it at once.

**Padawan.AVT** · 21-02-2010, 02:28

Sorry for the long reply, but I had to fix some bugs found while implementing this fix on Production.

As soon as I finish with the solid solution that includes all fixes and improvements, I'll post it here at once.

From the most important fixes, in function get_param_value_new please change line

Code:

 buf[length_p+1] = '\0';

to

Code:

 buf[length_p] = '\0';

cause sometimes it puts some garbage from the heap into your SQL statement and this cause ORA-911 error (invalid character)

**rizwank** · 04-05-2010, 02:17

I just tried building this patch in 1.8.2, will reply if there are any problems.

**rizwank** · 03-12-2010, 08:35

This built successfully and appears to work fine on Zabbix 1.84rc2.