NetXMS Support Forum

I've been evaluating NetXMS for a day or so now. Have managed to get DCIs set up to query service stats and set up actions to carry out if a threshold is breached. All fine.....

I want to monitor a database on a server. In particular the number of rows in particular tables.

Is there any way of setting up a data collector to run some SQL on database?

There are two options:

1) use odbcquery subagent (it works with unixODBC too):
You need to configure desired queries along with pool interval in agent config, they will be executed automatically by agent. Subagent provide couple of DCIs to get status of these configured queries (ODBC.QueryResult, ODBC.QueryStatus, ODBC.QueryStatusText)

2) add ExternalParemeter to agent's config, like this:ExternalParameter = SQL.CountEventLog:echo 'SELECT count(*) from event_log' | psql -tA netxms_db

Thanks for your quick response on this.

I've added the entry into the agent configuration file

LogFile = {syslog}
Servers = 127.0.0.1
InstallationServers = 192.168.100.149
FileStore = C:\NetXMS\var
RequireAuthentication = no
SubAgent = winperf.nsm
SubAgent = portcheck.nsm
SubAgent = odbcquery.nsm

I now get the DCIs

How do I do this bit, writing the sql is no problem. I just need to know where to put it etc.....

You need to configure desired queries along with pool interval in agent config

If I use an external parameter I could write something like this....

ExternalParameter = SQL.CountEventLog:echo 'SELECT count(*) from event_log' | psql -tA netxms_db

My database is an sqlserver instance, how do I format the psql -tA netxms_db bit to connect to it?

Ok, tried this.

ExternalParameterShellExec = SQL.CountUnits1:sqlcmd -S .\MYINSTANCE -U sa -P password -d Comms_Bridge -h-1 -Q "SELECT count(*) FROM Com_Units"

Communication Failure each time. Do I need to escape the above? Is it too long? 

Ok, given up on trying to use ExternalParameterShellExec. Went back to trying to use the odbcquery.nsm idea. (The above seems to not work with sqlcmd and with other shell commands like ipconfig returns just the first line)

Opened up the source code. Eventually worked out the format in the config file should be something like

*ODBC
Query = SQL.MYTEST:VMIgreenlight:"SELECT count(*) FROM Com_Units":10

This doesn't error but I get no results when I do a get. Why is this?

Can you please try to run agent in debug mode? To do so, change logging to a file by adding

LogFile = some_log_file

to agent's config, and run agent in foreground as

nxagentd -D 9

Best regards,
Victor

 :)Sorted!

Step 1:
Create a system DSN on the target machine
Making sure you have changed the default database to your target
Make sure that authentication mode is integrated windows and your database is configured to use this authentication method

Step 2:
Edit your target Agent config file, adding the following

SubAgent = odbcquery.nsm

*ODBC
Query = SQL.MySQLQuery:YOURDSNNAME:SELECT Count(*) FROM YourTable:60

Step 3:
Push the config to the agent
Check that ODBC.* data collection items are now available

Step 4:
Set up a Data Collection Item for the target machine of type ODBC.QueryResult where the parameter is the name you used above. In my case SQL.MySQLQuery

If you get problems try the ODBC.QueryStatusText as this should return a string telling what your problem might be.

Note: I tried to get SQLServer authentication working by defining the DSN explicitly in the YOURDSNNAME section but could not get this to work. It should be possible to use something like DRIVER={SQL Server};SERVER=hrserver;UID=Smith;PWD=Sesame so that an explicit DSN on the target should not be required.

If anyone gets this working please, please, please let me know.

Currently odbcquery subagent uses SQLConnect call which does not support connection strings. I will try to add automatic detection if connection string is given instead of DSN name (probably by checking for presence of = characters) and use SQLDriverConnect in this case.

Best regards,
Victor

Still having problems with this. The agent appears to allow one collection only. I then get this error. Here is the log

[05-Nov-2010 16:27:13] Additional configs was loaded from C:\nxagentd.conf.d
[05-Nov-2010 16:27:13] Debug level set to 9
[05-Nov-2010 16:27:13] Subagent API initialized
[05-Nov-2010 16:27:13] Subagent "WINNT.NSM" loaded successfully
[05-Nov-2010 16:27:15] Counter set B is empty, collector thread for that set will not start
[05-Nov-2010 16:27:15] Subagent "winperf.nsm" loaded successfully
[05-Nov-2010 16:27:15] Counter set C is empty, collector thread for that set will not start
[05-Nov-2010 16:27:15] Subagent "portcheck.nsm" loaded successfully
[05-Nov-2010 16:27:15] ODBC: query "SELECT Count(*) as col1 FROM Com_RemoteDeviceData" successfully registered
[05-Nov-2010 16:27:15] Subagent "odbcquery.nsm" loaded successfully
[05-Nov-2010 16:27:16] Trying to bind on 0.0.0.0:4700
[05-Nov-2010 16:27:16] Listening on socket 0.0.0.0:4700
[05-Nov-2010 16:27:17] NetXMS Agent started
Agent running. Press ESC to shutdown.
[05-Nov-2010 16:27:17] ODBC query error: Error binding result column ()
[05-Nov-2010 16:27:19] ODBC query error: Error binding result column ()
[05-Nov-2010 16:27:21] Incoming connection from 192.168.100.149
[05-Nov-2010 16:27:21] Connection from 192.168.100.149 accepted
[05-Nov-2010 16:27:21] [session:0] Received control message CMD_GET_NXCP_CAPS
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_NXCP_CAPS (size 16)
[05-Nov-2010 16:27:21] [session:0] Received message CMD_KEEPALIVE
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_REQUEST_COMPLETED (size 32)
[05-Nov-2010 16:27:21] [session:0] Session with 192.168.100.149 closed
[05-Nov-2010 16:27:21] Incoming connection from 192.168.100.149
[05-Nov-2010 16:27:21] Connection from 192.168.100.149 accepted
[05-Nov-2010 16:27:21] [session:0] Received control message CMD_GET_NXCP_CAPS
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_NXCP_CAPS (size 16)
[05-Nov-2010 16:27:21] [session:0] Received message CMD_KEEPALIVE
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_REQUEST_COMPLETED (size 32)
[05-Nov-2010 16:27:21] [session:0] Received message CMD_ENABLE_AGENT_TRAPS
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_REQUEST_COMPLETED (size 32)
[05-Nov-2010 16:27:21] [session:0] Received message CMD_GET_PARAMETER
[05-Nov-2010 16:27:21] [session:0] Requesting parameter "Net.Interface.AdminStatus(12)"
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_REQUEST_COMPLETED (size 48)
[05-Nov-2010 16:27:21] [session:0] Received message CMD_GET_PARAMETER
[05-Nov-2010 16:27:21] [session:0] Requesting parameter "Net.Interface.Link(12)"
[05-Nov-2010 16:27:21] [session:0] Sending message CMD_REQUEST_COMPLETED (size 48)
[05-Nov-2010 16:27:21] ODBC query error: Error binding result column ()
[05-Nov-2010 16:27:23] ODBC query error: Error binding result column ()
[05-Nov-2010 16:27:25] ODBC query error: Error binding result column ()
Agent shutting down...
[05-Nov-2010 16:27:26] Shutdown() called
[05-Nov-2010 16:27:26] Trap sender thread terminated
[05-Nov-2010 16:27:27] [session:0] CommSession::disconnect()
[05-Nov-2010 16:27:27] [session:0] Session with 192.168.100.149 closed
[05-Nov-2010 16:27:27] ODBC query error: Error binding result column ()
[05-Nov-2010 16:27:28] Session Watchdog thread terminated
[05-Nov-2010 16:27:28] Listener thread terminated
[05-Nov-2010 16:27:28] Collector thread for counter set A terminated
[05-Nov-2010 16:27:28] NetXMS Agent stopped

I have fixed few bugs in odbcquery subagent. Could you please try attached version?

Best regards,
Victor

Hi Victor,

Thanks again for your quick response to this. I have some encouraging news.

I tried the odbcquery.nsm on my target machine and it didn't work (wouldn't load). I assumed this is because you built an x86 version and this is a 64bit machine.

So I uninstalled the Agent and reinstalled the 32 bit version
I then set up a 32bit ODBC Dsn
I then ran the agent

It appears to work! Brilliant

1) When will the fix be included in a release (including 64bit agents)?
2) Do you you know when you extend it to allow 'automatic detection if connection string is given instead of DSN name (probably by checking for presence of = characters) and use SQLDriverConnect in this case'

Item (2) will be great as this will mean I will not have to set up a DSN on all of my target machines.

Thanks Again.

Hi!

Attached is 64 bit version. I plan to make a bugfix release in a few days. About 2nd question - in fact, attached version already supports connection strings in place of DSN names. The only limitation now is that connection string should not contain colon ( : ) character.

Best regards,
Victor

More good news.

The 64bit version appears to work.

I tested using configurations

Query = SQL.RDD1:MYDSN:SELECT Count(*) FROM MyTable:60
Query = SQL.RDD2:DRIVER={SQL Server};SERVER=.\MYINSTANCE;UID=sa;PWD=MyPassword;DATABASE=MyDB:SELECT Count(*) FROM MyTable:60

Both work! This means no more setting up of DSNs on each target machine!