Data Collection From a Database

gok

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?

gok

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?

gok

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?

Victor Kirhenshtein

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

gok

:)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.

Victor Kirhenshtein

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

gok

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

Victor Kirhenshtein

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

Best regards,
Victor

odbcquery.nsm

gok

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

When will the fix be included in a release (including 64bit agents)?
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.

Victor Kirhenshtein

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

odbcquery.nsm

gok

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!

NetXMS Forum

Data Collection From a Database