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 password is ‘masterkey’.
Monitoring database is a regular Firebird 2.5 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
To obtain data in CSV format one can use the following SQL query:
SELECT '"' || HOSTS.IP || '","' || RESULT_RAW.WHEN_ || '","' || RESULT_RAW.PERFORMANCE || '"' FROM MONITORS INNER JOIN HOSTS ON (MONITORS.HOST_ID = HOSTS.ID) INNER JOIN RESULT_RAW ON (MONITORS.ID = RESULT_RAW.MONITOR_ID) WHERE HOSTS.DISPLAY_NAME = 'hostname' AND RESULT_RAW.PERFORMANCE IS NOT NULL ORDER BY RESULT_RAW.WHEN_;
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;
- 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).