How can I access or modify monitoring data?

How can I access monitoring database to select certain data or make bulk changes to monitor properties?

Q: Can I access monitoring database directly, to retrieve data I need or to update it as necessary?

A: You can access monitors data with isql.exe utility (shipped with our software distribution), from both remote and local computer, from both Windows and Linux. The data can be obtained in CSV format, and used afterwards to generate Excel charts, calculating response time variation with a custom script and so on.

Alternate means to access database is to use an ODBC driver for Firebird. Please keep in mind that we only supply database schema upon a user’s request.

To perform a remote access, one should use credentials from [Database] section in nms.ini file (located in

"C:\ProgramData\IPHost Network Monitor"

folder). Default DB access credentials, depending on access method:

Monitoring database is a regular Firebird database, and thus can be accessed with such tools as Flamerobin or by using isql.exe Firebird standard command line utility. Executing the “CONNECT” command like the following one:

C:\Program Files\IPHost Network Monitor\firebird\bin>isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT 'localhost/3055:C:\ProgramData\IPHost Network Monitor\data\nms.fdb' user 'SYSDBA' password 'masterkey';
Database:  'localhost/3055:C:\ProgramData\IPHost Network
Monitor\data\nms.fdb', User: SYSDBA
SQL> SELECT count(*) FROM monitors;

COUNT
=============
       107

One can obtain output in CSV format, an example:

SELECT
   '"'   || HOSTS.IP ||
   '","' || RESULT_LOG.START_TIME ||
   '","' || RESULT_LOG.STATE || '"'
FROM
   MONITORS
   INNER JOIN HOSTS ON (MONITORS.HOST_ID = HOSTS.ID)
   INNER JOIN RESULT_LOG ON (MONITORS.ID = RESULT_LOG.MONITOR_ID)
WHERE
   HOSTS.DISPLAY_NAME = 'hostname'
   AND RESULT_LOG.STATE IS NOT NULL
ORDER BY
   RESULT_LOG.START_TIME;

The above query gets CSV list of known monitors states for given host, and time moments where the state was entered.

Note: if you wish to alter monitoring database in batch mode, you can do that by creating two files. First is command file (hereinafter db-maintenance.cmd):

@echo off
net stop "IPHost Network Monitor"
"C:\Program Files (x86)\IPHost Network Monitor\firebird\bin\isql.exe" -q -i "C:\scripts\db-maintenance.sql"
net start "IPHost Network Monitor"

and another, db-maintenance.sql

CONNECT "localhost/3055:C:\ProgramData\IPHost Network Monitor\data\nms.fdb" user 'sysdba' password 'masterkey';
SELECT "DISPLAY_NAME" FROM MONITORS;

Important:

• you should run the script under the same account you used to install IPHost Network Monitor, or via “Run as Administrator”
• “C:\ProgramData\IPHost Network Monitor\data\nms.fdb” in ‘.sql’ file specifies where monitoring data are located; correct the path if necessary
• you can place arbitrary amount of SQL statements after CONNECT in .sql; use tool like FlameRobin to test all the statements first
• do NOT perform all the testing on actual monitoring database; stop monitoring service and make a copy of the base, change CONNECT statement correspondingly
• it is assumed in ‘.cmd’ file that ‘.sql’ file is located in C:\scripts – alter the actual path to it as required

For your convenience, you can download the above two files with all required comments: db-maintenance.zip (1276 bytes).

Related topics:
Are raw data on monitor polls available?
SOA monitoring