Geneos
Geneos
Recommended Reads

Database Logging

Database Logging

Gateway supports logging of data values or events to a database. These records allow users to perform historical search or analysis of monitored data, which can then be used to improve system reliability and performance.

Gateway currently supports the following databases for logging values to:

  • MySQL or MariaDB
  • Sybase Adaptive Server Enterprise (ASE)
  • Oracle Database
  • Microsoft SQL Server. Please refer to these instructions on how to configure the set-up.

For information on supported database versions, see Database support in Geneos Compatibility Matrix.

Note: All setup, configuration, etc. for MariaDB is identical to MySQL. Any references to MySQL can also be taken as referring to MariaDB.

Apart from logging data values, Gateway logs following events or conditions to the database by default, unless explicitly turned off (e.g. actions) in database logging -> advanced section:

Event Logged to table
A Ticker event is generated default ticker events event_table
Attributes assigned/changed for a managed entity attribute_table
Hostname assigned/changed for a managed entity hostname_table
Action fired on a user assignment (if such action configured) action_table
Action fired on a user unassignment (if such action configured) action_table
Action fired from a Rule action_table
Summary Action fired as a result of another Action being throttled action_table
Escalation Action being fired as a result of another Action being escalated action_table
Alert fired alert_table
Summary effect fired as a result of Alert being throttled alert_table
Directory item is snoozed snooze_table
Directory item is unsnoozed snooze_table
Directory item is assigned to a user user_assignment_table
Directory item is unassigned to a user user_assignment_table
Directory item is made active by a rule. active_table
Directory item is made inactive by a rule. active_table

Database Schema

To log to a Geneos database a number of mandatory and user defined tables must be created in the target database. Template scripts for creation of these tables for your chosen database vendor can be found within your Gateway installation directory, in:

gateway/resources/databases

Within the databases directory, there are the following directories:

  • mysql
  • oracle
  • sqlServer
  • sybase

Each of those directories has files related to your chosen database. More information and full installation instructions can be found at Databases.

The scripts in each directory allow you to create the mandatory database logging tables described below in a new database or to upgrade an existing Geneos database to the latest schema version.

The schemas provided with the gateway allow a user to do the following:

  1. Upgrade from a previous schema version.
  2. Apply the schema to create a brand new set of tables to log to

Note: Upgrades must be performed between consecutive schema versions e.g. If you have a schema v1.0 then first you would need to upgrade to schema v1.1. Then you would upgrade to schema v1.2. You should never upgrade from 1.0 directly to 1.2.

Mandatory Tables

version_table

Stores the version of the schema. This table should be populated by the user when the Database is created. The current schema version is 1.8.

Field Description Type
major Major schema version number int
minor Minor schema version number int
node_ref_table

Stores references for Gateways and ManagedEntities that are being logged. The Gateway populates this table. It is recommended to use an index on this table to speed up queries.

Field Description Type
ref Node ref int
gateway Name of gateway varchar 50
Node Name of node/Managed Entity varchar 50
var_ref_table

Stores references for variables that are being logged. The Gateway populates this table. It is recommended to use an index on this table to speed up queries.

Field Description Type
ref Variable ref int
varname Name of variable varchar 200
tablename Name of table to which the variable will be logged varchar 50
event_table

Table to store logged events. The Gateway populates this table. It is recommended to use an index on this table to speed up queries.

Field Description Type
ref Event ref int
timestamp Time of event int
node_ref Reference into node_ref table for this event int
varname The variable name varchar 200
severity Severity of the event (0=OK, 1=Warn, 2=Fail, -1=Item deleted) int
description Textual description of the event varchar 250
gateway The Gateway name varchar 200
probe The Probe on which the event occurred varchar 200
managed_entity The managed entity on which the event occurred varchar 200
sampler The sampler on which the event occurred varchar 200
type The sampler type for which the event occurred varchar 200
dataview The dataview on which the event occurred varchar 250
headline The headline on for which the event applies varchar 200
rowname The dataview row for which the event applies varchar 200
columnname The dataview column for which the event applies varchar 200

Note: When an item with severity greater than or equal to Warning goes away from the system, an event is logged. In this case, the severity of this item is logged as -1 to indicate this item has been deleted. The event description also says "Item deleted".

attribute_table

Table to store the attributes of each managed entity. The Gateway populates this table on gateway start up, gateway setup change and Self-Announcing Probe instantiation. When the attribute value on a managed entity is changed then the value in the database is also changed. There is no history of old attribute values. However, if a managed entity is removed from the gateway, the attributes (like the node_ref) are not deleted. This table is used by the express reporting module to group reported values.

Field Description Type
node_ref Reference into node_ref table for this attribute int
name The attribute name varchar 255
value The attribute value varchar 255
hostname_table

Table to store the hostname of each managed entity. The Gateway populates this table on gateway start up, gateway setup change and Self-Announcing Probe instantiation (it is not populated for Floating probes or for virtual probes). It uses the value provided in the gateway setup (or provided by the Self Announcing probe when it is attached to the gateway). When the hostname of a managed entity is changed then the value in the database is also changed. There is no history of old hostnames. However, if a managed entity is removed from the gateway, the hostname (like the node_ref) is not deleted. This table is not used by the geneos system. It is provided to allow easier integration with 3rd party reporting tools.

Field Description Type
node_ref Event ref int
name The hostname (as defined in the setup file) varchar 255
action_table

Table to store actions fired from rules, user assignment/unassignment, summary actions fired as a result of another action being throttled or escalation actions being fired as a result of another action being escalated. If database logging is enabled this is logged by default. This functionality can be turned off however in the advanced settings of database logging.

Field Description Type
ref Action ref int
timestamp Time action was fired int
action The action name varchar 200
actionType Whether Normal/Escalate/Throttle action varchar 200
source What caused the action to fire varchar 250
parentAction The name of action whose escalation/throttling fired this action varchar 200
gateway The Gateway name varchar 200
probe The Probe on which the action fired varchar 200
managed_entity The managed entity on which the action fired varchar 200
sampler The sampler on which the action fired varchar 200
type The sampler type for which the action fired varchar 200
dataview The dataview for which the action fired varchar 250
headline The headline for which the action fired varchar 200
rowname The dataview row for which the action fired varchar 200
columnname The dataview column for which the action fired varchar 200
snooze_table

Table to store the snoozing and unsnoozing of items within Geneos. If database logging is enabled this logged by default. This functionality can be turned off however in the advanced settings of database logging.

field Description Type
ref Unique Reference for snooze int
timestamp Time the item was snoozed int
gateway Name of the gateway. varchar(200)
probe Name of the probe. varchar(200)
managedEntity Name of the managed entity. varchar(200)
type Name of the sampler type. varchar(200)
sampler Name of the sampler. varchar(200)
dataview Name of the dataview. varchar(250)
headline Name of the headline. varchar(200)
rowname Name of the row. varchar(200)
columnname Name of the variable column if applicable. Forms part of the broken down Gateway name. varchar(200)
snoozed "Y" if the item is snoozed or "N" if it is unsnoozed. char(1)
snoozedBy The user who snoozed the vaiable or blank. varchar(200)
description Description of why the item is snoozed by the user. varchar(250)
period

Type of snooze period

  • Manual - manual snooze
  • SeverityTo - snoozed until severity changes to
  • SeverityFrom - snoozed until severity changes from
  • Time - snoozed until a time of day
  • DateTime - snoozed to specific date / time
  • ValueChanges - snoozed until value changes
  • SeverityToOrTime - snoozed until specific severity or time
  • Until - Compound snooze criteria broken down below. The until option allows you effectively set a combination of the above.
 varchar(200)
untilSeverity Integer of required severity for SeverityFrom / SeverityTo / SeverityToOrTime periods. Also if the period is Until then set for snooze until options UntilSeverityFrom / UntilSeverityTo int
untilTime Datetime for when the data item will become unsnoozed. For Time / DateTime / SeverityToOrTime periods. Also if the period is Until then set for snooze until option UntilTime. int
untilValue Value of the data item for ValueChanges period. Also if the period is Until then set for snooze until option UntilValue. varchar(250)
unsnoozedBy User who unsnoozed the data item or "automatic" where unsnoozed automatically. varchar(200)
user_assignment_table

Table to store the assignment and unassignment of items within Geneos. If database logging is enabled this logged by default. This functionality can be turned off however in the advanced settings of database logging.

field Description Type
ref Unique Reference for user assignment. int
timestamp Time the item was assigned / unassigned. int
gateway Name of the gateway. varchar(200)
probe Name of the probe. varchar(200)
managedEntity Name of the managed entity. varchar(200)
type Name of the sampler type. varchar(200)
sampler Name of the sampler. varchar(200)
dataview Name of the dataview. varchar(250)
headline Name of the headline. varchar(200)
rowname Name of the row. varchar(200)
columnname Name of the variable column if applicable. Forms part of the broken down Gateway name. varchar(200)
userAssigned "Y" if the item is assigned or "N" if it is unassigned char(1)
assignedTo The user to whom the item is assigned or blank for unassignment. varchar(200)
description Description of why the item is assigned to the user. varchar(250)
period

Type of user assignment period

  • "Manual"
  • "UntilOk"
  • "Until severity changes to specified"
  • "Until severity changes from specified"
  • "Until a specific date / time"
  • "Until severity changes to specified or until a specific date / time"
  • "Until a change in value"
 varchar(200)
untilSeverity Integer of required severity for "UntilOk" /"Until severity changes from specified" / "Until severity changes from specified", "Until severity changes to specified or until a specific date / time" periods. int
untilTime Date / time used for the "Until a specific date / time" or "Until severity changes to specified or until a specific date / time" period. int
untilValue Value of the cell. Used for "Until a change in value"period varchar(250)
assignedBy User who assigned the data item varchar(200)
unassignedBy User who unassigned the data item or "automatic" where unassigned automatically. varchar(200)
alert_table

Table to store alerts raised and summary effects fired when alerts are throttled. If database logging is enabled this is logged by default. This functionality can be turned off however in the advanced settings of database logging.

Field Description Type
ref Unique Reference for alert int
timestamp Time alert was fired int
name The alert name or throttle name varchar 250
hierarchy The hierarchy alert belongs to varchar 200
type Whether Alert/Clear/Suspend/Resume/ThrottleSummary alert varchar 200
severity The severity of alert - OK/WARNING/CRITICAL/UNDEFINED varchar 200
effect The name of summary effect in case of throttled alert varchar 200
repeatCount The repeat Count for the alert int
escalationLevel The escalation level for the alert int
active_table

Table to store when an item is made active / inactive through a rule. This occurs when a rule body explicitly makes the item active or inactive and not through the "Active state affects cell" setting.

field Description Type
ref Unique Reference int
timestamp Time the item was active / inactive. int
gateway Name of the gateway. varchar(200)
probe Name of the probe. varchar(200)
managedEntity Name of the managed entity. varchar(200)
type Name of the sampler type. varchar(200)
sampler Name of the sampler. varchar(200)
dataview Name of the dataview. varchar(250)
headline Name of the headline. varchar(200)
rowname Name of the row. varchar(200)
columnname Name of the variable column if applicable. Forms part of the broken down Gateway name. varchar(200)
active "Y" if the item is active or "N" if it is inactive char(1)
source The name of the rule including any rule groups that triggered the change. e.g. OutermostRuleGroup > RuleGroup > activeStateRule varchar(200)

User Defined Tables

Any number of user defined data tables can be defined to organise and store logged variables. The type for each table must be either int, double or text string depending on the type of data to be logged. The Gateway will populate these tables.

When a value is about to be logged, the Gateway will attempt to convert it to the type defined for that table. If it cannot be converted an error will be logged and the value will not be logged.

The tables defined here must match those configured for the setting databaseLogging > tables described below.

Field Description Type
timestamp Time of event. Int
node_ref Reference into node_ref table for this variable. Int
var_ref Reference into var_ref table for this variable. Int
value The value of the variable. Int, double or varchar 250

It is recommended to add an index with the timestamp as the final value as below. This helps speed up historical graphing in the Active Console. SQL to create user tables called os_table, net_table, processes_table and text_table can be found in the gateway database schema files, which are located in the resources directory of the gateway.

How to change which user defined table a variable gets logged to

A user may decide that they would like a variable's value to be logged to a different table to the one it is currently being logged to. To ensure that this happens in a safe manner, this procedure should be carried out as follows:

  • Change the name of the table to which the variable is being logged and send the set-up changes to the gateway. The next sample which attempts to write the variable to the database will result in the gateway raising an error in its log file stating that it refuses to log any more values. The user should kill the gateway at this point.
  • The user should then copy over the records from the old table to the new table using an SQL tool to the database to which the gateway was logging. The snippets below indicate how this could be done (the Query is based on the assumption that you pre-create the 'MyNewTable').
Database Query
MySQL 
INSERT INTO MyNewTable SELECT * FROM MyOldTable
DELETE FROM MyOldTable;
Sybase 
INSERT INTO MyNewTable SELECT * FROM MyOldTable;
DELETE FROM MyOldTable;
GO
Oracle 
INSERT INTO MyNewTable SELECT * FROM MyOldTable;
DELETE FROM MyOldTable;
COMMIT;
SQLServer 
INSERT INTO MyNewTable SELECT * FROM MyOldTable;
DELETE FROM MyOldTable;
GO
  • The user should then rename the entry in the var_ref_table from the old table name to the new table name. The snippets below indicate how this could be done.
Database Query
MySQL 
UPDATE var_ref_table SET tablename='MyNewTable'
WHERE tablename='MyOldTable';
Sybase 
UPDATE var_ref_table
SET tablename='MyNewTable'
WHERE tablename='MyOldTable'
GO
Oracle 
UPDATE var_ref_table
SET tablename='MyNewTable'
WHERE tablename='MyOldTable';
COMMIT;
SQLServer 
UPDATE var_ref_table
SET tablename='MyNewTable'
WHERE tablename='MyOldTable';
GO
  • Oracle only: when moving between tables in Oracle, the var_ref_table must be rebuilt using the following steps:
Query 
CREATE TABLE var_ref_table_bak (REF NUMERIC(10,0) , varname VARCHAR(200), 
tablename VARCHAR(50) ) ;

INSERT INTO var_ref_table_bak SELECT * FROM var_ref_table;
DROP TABLE var_ref_table;
DROP SEQUENCE var_ref_seq;
DROP TRIGGER var_ref_trigger;
CREATE TABLE var_ref_table (REF NUMERIC(10,0) , varname VARCHAR(200), tablename VARCHAR(50), 
CONSTRAINT pk_var_ref_table PRIMARY KEY (REF)) ;

CREATE UNIQUE INDEX var_ref_index ON var_ref_table (varname);
CREATE SEQUENCE var_ref_seq START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER var_ref_trigger BEFORE INSERT ON var_ref_table FOR EACH ROW 
BEGIN SELECT var_ref_seq.nextval INTO :new.REF FROM dual;END;

/
INSERT INTO var_ref_table SELECT * FROM var_ref_table_bak;
DROP TABLE var_ref_table_bak;

Note: The "DROP TRIGGER var_ref_trigger" instruction may fail saying that the trigger doesn't exist - this is not a problem, just carry on with the remaining instructions.

  • Finally the user should restart the gateway. Logging should now work as expected, with values now being logged to the new table, old values moved from the old table to the new table and the var_ref_table now pointing to the new table rather than the old table.

How to reset database when the primary key of the var_ref table gets the maximum value

If the primary key of the var_ref table gets to the maximum value(2147483647), gateway will pause DBLogging reporting error in the logs. If DBLogging is paused you will see following error in gateway logs:

WARN: DatabaseLogging Maximum number of logged	variables exceeded. Database logging suspended. See manual for details of how to correct. INFO: DatabaseLogging Database connection is paused

Database logging will not resume until key values are modified. Once the values are modified the following steps should be taken:

  • Modify Key value.
  • You may have to make space in the var_ref_table by removing unwanted variables from var_ref_table. You may contact your DBA on how to remove unwanted variables.
  • Reset the key increment as following:
Database Query
MySQL 
ALTER TABLE var_ref_table AUTO_INCREMENT = <value to be reset to>;
Sybase 
sp_chgattribute var_ref_table, "identity_burn_max", 0, "<value to be reset to>";
Oracle 
DROP SEQUENCE var_ref_seq;
CREATE SEQUENCE var_ref_seq START WITH "<value to be reset to>" INCREMENT BY 1 NOMAXVALUE
SQLServer 
DBCC CHECKIDENT ('var_ref_table', RESEED, <value to be reset to>);
  • Restart Gateway.
  • Resume Database Logging by:
Database Logging -> Connection -> Resume

Optional Tables

audit_table

If logging audit information to database (see the Auditing section) is required then an additional table must be created to store this audit data. The Gateway will populate this table if auditing is enabled.

Field Description Type
audit_record_id GUID for this audit record. varchar 36
timestamp Time of event. Int
username Name of the user that performed the action varchar 50
workstation Workstation name of the user that performed the action. varchar 50
module The module which the action will effect. varchar 50
managedEntity The ManagedEntity that the action will effect. varchar 200
description Description of the action performed. varchar 250
node_ref Identifies the gateway that performs the audit logging Int
gateway The Gateway name varchar 200
probe The Probe on which the action applies varchar 200
sampler The sampler on which the action applies varchar 200
type The sampler type for which the action applies varchar 200
dataview The dataview on which the action applies varchar 250
headline The headline on for which the event applies varchar 200
rowname The dataview row on which the action applies varchar 200
columnname The dataview column on which the action applies varchar 200
auditevent The type of action audited varchar 50
failreason If the action failed, a brief reason for the failure varchar 200

The SQL to generate this table can be found in the appropriate database schema file which is located in the gateway resources directory.

audit_details_table

If the collection of extended audit details is enabled and the database is listed as an audit output (see the Auditing section) then an additional table must be created to store this audit details data.

Field Description Type
audit_record_id GUID for this audit record. varchar 36
item See the Auditing section for a description of this field. varchar 200
field See the Auditing section for a description of this field. varchar 200
vaue See the Auditing section for a description of this field. varchar 1000

The SQL to generate this table can be found in the appropriate database schema file which is located in the gateway resources directory.

Use of NULLs in User Defined Tables

The user defined tables are used to store time series. Under normal operation each time a cell that is being logged to the database changes value, the value along with the time it changed is logged to the database. This can be modified by the use of the following options that are set on the Database logging items; minInterval, forcedInterval, absoluteMargin and percentMargin.

The system also logs NULL values to the user data tables to indicate breaks in data.

If a database item goes inactive. A NULL will be logged with the time that the active time associated with the Database logging data-item went inactive. This active/inactive flag for a database logged item is controlled by the active time set on the database logging item in the gateway configuration (See activeTime).

A NULL can also be logged when a data-item is deleted. This is controlled by the logNullOnDelete setting on the Database logging items. The time logged with the NULL value will be the time that the data-item was deleted.

A NULL will also be logged to the table if the cell to be logged is empty.

Database cache dump files

If the connection to the database breaks for any reason, the Gateway will buffer the updates in an internal queue until the connection is re-established. Eventually the number of updates in the queue will exceed to maximum allowed (default 4000). At this point the Gateway will dump the updates into a file. If the connection is later re-established the Gateway will reread any dump files and attempt to insert the data into the database. This mechanism ensures that monitored data is not lost during database outages.

The dump files and auto reinsertion mechanism can be disabled in the Gateway configuration. The Gateway will discard any files that are 7 days or older by default but this period is also configurable. The size of this cache is unlimited by default, however you may configure a limit and the Gateway will discard files to keep the cache within this size limit. The oldest data is deleted first. If auto reload is disabled, the Gateway command line option -process-dump-files can be used to manually insert the data at a convenient time.

See Database cache dump file configuration.

Database Logging Suspension

Types of suspension

Discarding item values

With this type of suspension database item value updates (e.g. cell values) will be discarded completely, thereby not logging them to the database (or to the database cache dump files if the database is not available).

Everything else is still logged to the database (e.g. events, attributes, audit records, nulls), maintaining a record of what has changed in the overall system.

As the item value updates are neither logged nor cached, any item value updates during this period are permanently lost. It is therefore useful when disk space is running low on the configured database system as value updates will be the biggest source of database data.

Pausing the connection

With this type of suspension then everything that would be going to the database is instead always stored in memory until the maximum queue size is reached, and the database cache dump file mechanism writes this data to disk (unless the cache on disk has been turned off). The connection to the database will be dropped as soon as the connection is paused.

This is useful if the database is completely un-usable (e.g. going to be down for maintenance), and all data needs to be kept. Once the connection is no longer paused then the files will be read from disk and inserted into the database (unless this feature has been turned off) and logging will continue as normal.

Combining suspension types

Discarding item values and connection pausing may be used independently, or they can be used at the same time.

If discarding is used on its own then cell values will be thrown away, and everything else will be kept and logged to the database if it's available, or cached to dump files if it's not. If connection pausing is used on its own then everything will be cached to dump files on disk. If both are used at the same time then cell values will be thrown away, and everything else will be cached to dump files on disk.

Initiating suspension

Commands

Each of the types of database suspension can be started or stopped by using commands that are available by right-clicking on Gateways in an Active Console:

Database Logging -> Item Values -> Discard
-> Log
-> Connection  -> Pause
-> Resume

Running the commands manually is useful for ad-hoc maintenance of the database. When combining them they may be run in either order.

These commands are also available to be scheduled under the /DATABASELOGGING: group of commands in the internal command list, and this is useful if the database is regularly taken down for maintenance.

More details of each of the commands can be seen in the Database Logging commands.

Automatically

Sometimes, when an error is returned from the database, the best action to take is to automatically start discarding of database item values or to pause the database connection altogether. This can be configured using either of the following settings that are available in the Advanced tab of the Database logging section:

Discard item values on error
Pause connection on error

In either of these settings, it's possible to configure multiple error codes as a list. Each of these error codes is matched against the unique error code returned from the database. When any of these error codes are returned from the database it will trigger the relevant type of suspension.

Error codes may be entered into either or both lists. If a code appears in both lists then the suspension types will be combined at the same time if that code is encountered. With different codes in the two lists then item values may start to be discarded first, and then at a later point the connection may be paused too. It will not be possible to get to a combined suspension with pausing first and discarding second, since there will no longer be any error messages received from the database once it is disconnected.

To return to logging as before the equivalent Database Logging command(s) on the Gateway (ItemValues->Log or Connection->Resume or both) will need to be run.

For Sybase, MySQL and SQLServer, the error code is a number and will appear inside square brackets. For example, the database may throw back an error like:

[2003] Can't connect to MySQL server on 'ITRSPC000.ldn.itrs' (110) where 2003 is the error code and should be entered into this field in the list.

For Oracle, the numbers on their own are not unique and the message will also contain the unique code straight after the square brackets, for example:

[942] ORA-00942: table or view does not exist
[100] SQL*Loader-00100: Syntax error on command-line
[47] EXP-00047: Missing tablespace name(s)

In the first case, ORA-00942 is the unique error code (where 942 in square brackets is just the number from the error code) and ORA-00942 should be entered into this field in the list. For the other two examples SQL*Loader-00100 and EXP-00047 should be entered.

Please refer to Database logging errors for a list of possible error codes returned from different database vendors.

Database configuration

Database logging configuration consists of two parts, configuring the database to log to, and which items to log. This section details how to configure the database.

databaseLogging

Database logging configuration is performed within the databaseLogging top-level section. If the section does not exist then database logging is not enabled.

Mandatory: No
Default: No logging

databaseLogging > enabled

Boolean value controlling whether database logging is active or not.

Mandatory: No
Default: No logging

databaseLogging > events

Options to control whether database logging occurs in relation to certain events.

Mandatory: No

databaseLogging > events > fireOnComponentStartup

Variables may be logged when a gateway or netprobe is first started.

Mandatory: No
Default: false

databaseLogging > events > fireOnConfigurationChange

Variables may be logged following a change of the gateway configuration file.

Mandatory: No
Default: false

databaseLogging > events > fireOnCreateWithOkSeverity

Variable may be logged following a dataview item being created and transitioning from undefined to OK severity.

Mandatory: No
Default : False

databaseLogging > events > logSnoozeDetails

Controls logging of snoozing and unsnoozing of data items.

Mandatory: No
Default : True

databaseLogging > events > logUserAssignmentDetails

Controls logging of user assignment and unassignment of data items.

Mandatory: No
Default : True

databaseLogging > events > logActions

Controls whether action events are logged to the database.

Mandatory: No
Default : True

databaseLogging > events > logAlerts

Controls if alerts are logged to the database.

Mandatory: No
Default : True

databaseLogging > events > logActiveState

Controls if changes to a cell's active state are logged to the database. A cell's active state is a logical OR of the active state of all the rules that affects the cell. See Gateway Rules, Actions, and Alerts.

Mandatory: No
Default : True

databaseLogging > events > database > maxDescriptionLength

Specifies the maximum length of the description field in the event_table, where the events are logged to. The gateway will automatically truncate descriptions which are too long to this length, to ensure that they are logged.

Clients should only need to change this setting if they have changed the description field length above the default length in the database schema provided with gateway.

Note: If logging information to audit_table (see Auditing).

Mandatory: No
Default: 250

databaseLogging > events > database > snoozeMaxDescriptionLength

Specifies the maximum length of the description field in the snooze_table, where the snooze events are logged to. The gateway will automatically truncate descriptions which are too long to this length, to ensure that they are logged.

Clients should only need to change this setting if they have changed the description field length above the default length in the database schema provided with gateway.

Note: If logging information to audit_table (see Auditing).

Mandatory: No
Default: 250

databaseLogging > events > database > userAssignmentMaxDescriptionLength

Specifies the maximum length of the description field in the user_assignment_table, where the user assignment events are logged to. The gateway will automatically truncate descriptions which are too long to this length, to ensure that they are logged.

Clients should only need to change this setting if they have changed the description field length above the default length in the database schema provided with gateway.

Note: If logging information to audit_table (see Auditing).

Mandatory: No
Default: 250

databaseLogging > maxRequestQueueSize

The maximum number of queries to the database that gateway will buffer, before writing a dump file. Values should lie in the range 1-10,000 inclusive.

Mandatory: No
Default: 4000

databaseLogging > connection

The connection section holds the connection details for a particular database.

Mandatory: Yes

databaseLogging > connection > database

Specifies the connection parameters for the database to log to. This node can contain a choice of mySQL, SQLServer, sybase or oracle.

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication

Authentication details for the gateway to log into the database

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication > userName

The username that the Gateway will use to login to the database.

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication > password

The login password.

Note: This can be stored in the setup file as plaintext, using std encryption or AES 256 encryption.

See Secure Passwords

Mandatory: Yes

databaseLogging > connection > clientAuthentication

Optional authentication details for a client such as Active Console to log into the database. If this is not given the client authentication details default to the gateway authentication.

Mandatory: No
Default: Uses Gateway authentication

databaseLogging > connection > clientAuthentication > userName

The username that the client will login with.

Mandatory: Yes

databaseLogging > connection > clientAuthentication > password

The login password.

Note: This can be stored in the setup file as plaintext, using std encryption or AES 256 encryption.

See Secure Passwords.

Mandatory: Yes

databaseLogging > tables

Contains a list of definitions of the tables within the database that gateway can log to.

Mandatory: Yes

databaseLogging > tableGroup

Table groups are used to group sets of tables, to improve ease of setup management.

Mandatory: No

databaseLogging > tableGroup > name

Specifies the name of the table group. Although the name is not used internally by gateway, it is recommended to give the group a descriptive name so that users editing the setup file can easily determine the purpose of the group.

Mandatory: Yes

databaseLogging > tables > table

A uniquely named (among all other tables) table definition of a data table in the database.

See "How to change which user defined table a variable gets logged to" for important information for ensuring database integrity when changing which tables your data is logged to.

Mandatory: Yes (at least one table is required for data logging)

databaseLogging > tables > table > type

The type of the data which can be contained within the table. One of integer, double or string.

databaseLogging > suspendOnError

A list of error codes which, if encountered in an error reported back from the database, will cause database logging to be automatically (item values to be automatically discarded) until enabled through the DATABASELOGGING:itemsValues:log command.

Note: This setting has been deprecated. Please use the discardItemValuesOnError setting instead.

For more details on this setting please see the Suspend on Error Setting section.

Mandatory: No
Deprecated: Please use the discardItemValuesOnError setting.

databaseLogging > discardItemValuesOnError

A list of error codes which, if encountered in an error reported back from the database, will cause item values to be automatically discarded until enabled again through the /DATABASELOGGING:itemsValues:log command.

For more details, please refer to the Discarding item values and Initiating suspension automatically sections.

Mandatory: No

databaseLogging > pauseConnectionOnError

A list of error codes which, if encountered in an error reported back from the database, will cause database logging to be automatically paused until enabled through the /DATABASELOGGING:connection:resume command.

For more details, please refer to the Pausing the connection and Initiating suspension automatically sections.

Mandatory: No

databaseLogging > isolationLevel

This setting specifies the isolation level that the Gateway Database Logging feature uses when performing transactions. The three settings that can be used are:

  • Read_committed — guarantees that any data read is committed by the time it is read. Lost updates may occur.
  • Repeatable_read — higher isolation level which guarantees that any data read cannot change. Lost updates are less likely to occur.
  • Serializable — highest isolation level which requires read and write locks at the end of the transaction. Lost updates are less likely to occur.

These settings correspond to the ANSI/ISO Isolation Levels.

Mandatory: No

Default: Repeatable_read

Note: If you encounter the ORA-08177: can't serialize access for this transaction error message, change the Isolation level value to Read_committed.

databaseLogging > optimisation > maximumRequestsBeforeCommit

The maximum number of insertion requests the gateway will make to the database without committing changes.

Mandatory: No
Default: 15 insertions

databaseLogging > optimisation > maximumTimeBetweenCommits

The maximum amount of time (in seconds) that will elapse without the gateway committing changes to the database.

Mandatory: No
Default: 10 Seconds

databaseLogging > logNetprobeSampleTimeForDataItems

With this option enabled the gateway will use the sample time of the item being logged. This functionality requires a probe capable of sending the sample time to the data-items.

The default behaviour of the gateway is to log the time the data was received which for a busy gateway may differ from the time it was published by the netprobe.

If the sample time for a the item is not available then gateway performs the default action.

Note: Non-dataview items such as events, probe up / down etc. will still be logged with the Gateway time.

Mandatory: No
Default: false

MySQL configuration

databaseLogging > connection > database > mysql

Configuration for the MySQL database type.

Mandatory: No
databaseLogging > connection > database > mysql > serverName

The hostname or IP address of the machine where the MySQL database is running.

Mandatory: Yes
databaseLogging > connection > database > mysql > databaseName

The name of the database to log to, e.g. Geneos.

Mandatory: Yes
databaseLogging > connection > database > mysql > port

The server port number to connect to MySQL on.

Mandatory: No
Default: 3306

Sybase configuration

databaseLogging > connection > database > sybase

Configuration for the Sybase database type.

Mandatory: No
databaseLogging > connection > database > sybase > interfaceEntry

This should be the alias in the Sybase interfaces file that references the required database server.

Mandatory: Yes
databaseLogging > connection > database > sybase > databaseName

The name of the database to log to, e.g. Geneos.

Mandatory: Yes
databaseLogging > connection > database > sybase > serverName

The hostname or IP address of the machine where the sybase database is running (this is used for Active Console connections).

Mandatory: No
databaseLogging > connection > database > sybase > port

The server port number to connect to Sybase on (this is used for Active Console connections).

Mandatory: No
Default: Active Console DB not configured
databaseLogging > connection > database > sybase > applicationName

The application name to be set to the connection created from Gateway's databaseLogging to the Database Server.

Mandatory: No
Default: gateway2 (listen-port <Gateway's port>)

Oracle configuration

databaseLogging > connection > database > oracle

Configuration for the Oracle database type.

Mandatory: No
databaseLogging > connection > database > oracle > databaseName

The name of the database to log to, for example Geneos. This is specified in the tnsnames.ora file.

The full TNS definition in tnsnames.ora may be specified in place of the database name in any of these cases:

  • tnsnames.ora is available
  • tnsnames.ora is not available
  • LDAP is used to connect

If tnsnames.ora is available, it overrides the details of the file in the Oracle directory.

For example, if you have these configuration details:

MYALIAS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = MYDATABASE)
(SERVICE_NAME = MYDATABASE)
)
)

In the database name field, you may specify as follows:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDATABASE)(SERVICE_NAME=MYDATABASE)))

Ensure that the alias is not included and the syntax does not have spaces. All necessary environment variables must also be set in the start-up environment of the Gateway and adjusted for the operating system and start-up script in use. E.g. for UNIX/Linux components started from a bash script:

export ORACLE_HOME=/usr/local/oracle/instantclient_11_2
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}

Remember to adjust the path to the Oracle installation directory and choose the correct library subdirectory based on the architecture of the Gateway in use.

When connecting to Oracle database, you might encounter the ORA-12154: TNS:could not resolve the connect identifier specified error message. This means that the value in the Gateway Setup Editor > Database logging > Database name parameter cannot be resolved.

To resolve this, do one of the following:

  • Ensure that the database name is correctly used in the tnsnames.ora file.
  • If you are not using the tnsnames.ora file, enter the following values in the Database name parameter:
    • (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<database server host/IP>)(PORT=<database server port>))(CONNECT_DATA=(SERVICE_NAME=database name/database global name)))
Mandatory: Yes
databaseLogging > connection > database > oracle > serverName

The hostname or IP address of the machine where the oracle database is running (this is used for Active Console connections).

Mandatory: No
Default: Active Console DB not configured
Deprecated: See clientConnection setting.
databaseLogging > connection > database > oracle > port

The server port number to connect to oracle on (this is used for Active Console connections).

Mandatory: No
Default: Active Console DB not configured
Deprecated: See clientConnection setting.
databaseLogging > connection > database > oracle > sid

The SID setting is only necessary for Active Console if the SID does not match the connect identifier.

The databaseName represents the connect identifier, which is a name given to the hostname, port and SID of an oracle database stored in the TNSNAMES.ora. The connect identifier name can be the same as the SID but doesn't have to be.

If the connect identifier matches the SID (The connect identifier is shown on the first line) then the SID setting does not need to be set e.g. only databaseName will need to be set to MYDATABASE.

MYDATABASE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MYDATABASE)
(SERVER = DEDICATED)
)
)

If the connect identifier does not match the SID then the SID setting needs to be set e.g. the databaseName would be set to MYALIAS and the SID setting would be set to MYDATABASE.

MYALIAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MYDATABASE)
(SERVER = DEDICATED)
)
)
Mandatory: No
databaseLogging > connection > database > oracle > clientConnection > serverName

The hostname or IP address of the machine where the oracle database is running (this is used for Active Console connections).

Note: For clustered configurations multiple clientConnection servername / port pairs can be defined.

Mandatory: No
Default: Active Console DB not configured
databaseLogging > connection > database > oracle > clientConnection > port

The server port number to connect to oracle on (this is used for Active Console connections).

Note: For clustered configurations multiple clientConnection servername / port pairs can be defined.

Mandatory: No
Default: Default oracle port (1521)
databaseLogging > connection > database > oracle > applicationName

The application name to be set to the connection created from Gateway's databaseLogging to the Database Server.

Mandatory: No
Default: gateway2 (listen-port <Gateway's port>)

Microsoft SQL Server configuration

databaseLogging > connection > database > sqlServer

Configuration for the MS SQL Server database type.

Mandatory: No
databaseLogging > connection > database > sqlServer > serverName

The hostname or IP address of the machine where the MS SQL Server database is running.

Note: When connecting to named instances the format should be "ServerNameInstanceName". On connection Geneos will verify the named instance actually matches the one listening on the specified port.

Mandatory: Yes
databaseLogging > connection > database > sqlServer > databaseName

The name of the database to log to, e.g. Geneos.

Mandatory: Yes
databaseLogging > connection > database > sqlServer > port

The server port number to connect to MS SQL Server on.

Mandatory: No
Default: 1433

Note: If connecting from a Linux 64 gateway to a named SQL Server instance, the correct port for this instance must be specified instead of including the instance name in the serverName.

Example database configuration

This example configuration sets up logging for a MySQL database which will be managed by a 'DBUser'. The logs will be read by a 'ACUser' client account.

Open Database logging from the Gateway Setup Editor Navigation pane:

  1. In the Gateway Setup Editor navigation pane, right-click Database logging and select Create.
  2. Specify the Database type, in this example MySQL.
  3. Specify the Server name of the machine the database is running on.
  4. Specify the Database name and Port (default: 3306).
  5. Under Gateway authentication, specify the User name of the database admin, in this example 'DBUser'.
  6. Select stdAES for the Password authentication of the user.
  7. Click Set password and enter a password for the user. The database admin account 'DBUser' should have write permissions for the 'ExampleDB' database.
  8. Under Client authentication, specify the User name of the client account, in this example 'ACUser'
  9. Select stdAES for the Password authentication of the user.
  10. Click Set password and enter a password for the user. The client account 'ACUser' should have read permissions for the 'ExampleDB' database.
  11. Click Save current document to apply your changes

Success: You can now add tables and items to your database logging setup.

Items configuration

The item configuration describes which data-items to log to the database, and additionally under which conditions they should be logged. Using the configuration settings it is possible to configure how the values get logged, so that rapidly changing values do not fill up the database and overload the gateway.

When a data-item is configured for logging, the gateway will try to log the initial value of this item as soon as possible. If the data is already present in the system, then this will occur when the setup containing the configuration is applied. Otherwise logging will be performed when the data first arrives on gateway.

databaseLogging > items

A list of data-items which will be logged to the database. The items configuration consists of a list of item nodes, which can contain the settings described below.

Mandatory: Yes (At least one item definition is required)

databaseLogging > itemGroup

Item groups are used to group sets of items, to improve ease of setup management.

Mandatory: No

databaseLogging > itemGroup > name

Specifies the name of the item group. Although the name is not used internally by gateway, it is recommended to give the group a descriptive name so that users editing the setup file can easily determine the purpose of the group.

Mandatory: Yes

databaseLogging > items > item > targets

The gateway XPath of the item (or items) to log to database. The target is used to match against data-items to be logged. This target cannot include any runtime information in its filters. If it does then you will see an error like:

WARN: DatabaseLogging Ignored target as xpath contains non-identifying predicate

See the XPaths - User Guide for more information on identifying predicates and non-identifying predicates.

Mandatory: Yes

databaseLogging > items > item > minInterval

Optionally specifies the minimum logging interval (in seconds) for data-items which correspond to the name above. If a value is logged, and then changes during the interval such that it would be logged again, the changed value is not logged. The effect is that the database contains logged values separated by at least minInterval seconds each.

Mandatory: No
Default: Not defined

databaseLogging > items > item > forcedInterval

The converse of minInterval described above, this optional setting specifies the maximum interval (in seconds) that an item will go without being logged. The effect is that the database contains logged values separated by no more than forcedInterval seconds each. The forced interval configuration will take precedence over any absolute or percentage margin configurations.

Mandatory: No
Default: Not defined

databaseLogging > items > item > absoluteMargin

The absolute margin specifies how much the value must change by before it is logged. This value is computed against the last logged value. The formula used for this calculation is |(oldValue-newValue)|>=absoluteMargin. If this is specified then percentMargin (below) cannot also be specified.

Mandatory: No
Default: Not defined

databaseLogging > items > item > percentMargin

The percentage margin specifies by what percentage the value must change by before it is logged. This value is computed again the last logged value. The formula used for this calculation is |(newValue-oldValue)/oldValue|x100>=percentMargin. If this is specified then absoluteMargin (above) cannot also be specified.

Mandatory: No
Default: Not defined

databaseLogging > items > item > logNullOnDelete

Selecting this option causes the database to write a null value to the database when a cell is deleted from the system. This can be useful for tracking creation/deletion of rows or cells over time.

Mandatory: No
Default: No null will be written on delete

databaseLogging > items > item > activeTime

References the active time by name, during which items described by this configuration should be logged to the database. See Active Times.

When the active time enters inactive period, Gateway logs NULL value to the geneos database table as a way of telling that logging to database is currently suspended. When the active period resumes, it writes the current value of data-item and resumes database logging as normal. During charting through Active Console, the interval between NULL and subsequent actual value is not charted (appears as gaps in the chart) giving clear indication that database logging was off during inactive period.

Mandatory: No
Default: Will log at all times

databaseLogging > items > item > table

References the table to log the value to by name. The type of value being logged must match the table data type.

Mandatory: Yes

Example item configuration

This example configuration sets up a new item.

  1. In the Gateway Setup Editor navigation pane, right-click Database logging > Items and select New Item.
  2. Set a Name for the data-item.
  3. Specify the Xpath to the Target data.
  4. Select a Table.
  5. Click the Advanced tab.
  6. Specify the Min interval between updates.
  7. Specify the Active time during which logs will be recorded.
  8. Specify any additional Options.

Database cache dump file configuration

Go to GSE > Database logging > Advanced, and then select dumpFile in the Database cache files.

ref-guide162

databaseLogging > dumpFile

Configuration node for the Database cache dump file options.

databaseLogging > dumpFile > disable

If Disable writing new files is set to true, then database dump files will not be generated. If the database is not available for a prolonged period data will be discarded.

Mandatory: No
Default: false

databaseLogging > dumpFile > disableAutoReload

If set to true, then database will not attempt to read data from the cache and send these to the database when the database becomes available.

Mandatory: No
Default: false

databaseLogging > dumpFile > cachePeriod

The maximum number days to persist a cache file. If the database does not reconnect within this period then the data will be discarded. If the maximum age is reduced from previously configured then the oldest data is removed until the cache falls within limits.

Mandatory: No
Default: 7

databaseLogging > dumpFile > maxCacheSize

This is an optional restriction on the total size of the cache. The default is unlimited, however you can set an upper limit in megabytes in one megabyte increments.

When the maximum size is reached then the files containing the oldest data are deleted to make room for new data. If the maximum size is reduced from previously configured then the oldest data is removed until the cache falls within limits.

Mandatory: No
Default: unlimited

databaseLogging > noCacheAndDeleteExisting

You can select to not keep a cache at all. This option prevents storing failed logging requests to the cache and deletes any existing cache files.

MS SQL Server Database Setup using native client library - 64-bit Linux Gateway

The MS SQL Server native client libraries are currently supported only for 64-bit Linux.

Follow the steps below to connect to MS SQL Server database from 64-bit Linux Gateways using native client library:

  1. Install MS SQL Server from the Microsoft site on Windows or Linux. For fresh installations, select MS SQL Server as the default installation (i.e. do not give it any instance name). In case you are using MS SQL Server 2008 Express, there is a bug in it where selecting the Default Instance box in the installation does not work. By entering MSSQLSERVER as the instance name the installer will install SQL Server with the default (i.e. none) instance name.
  2. Download and install the latest version of gateway on your Linux box from https://resources.itrsgroup.com as usual.
  3. For MS SQL Server database connection from a Linux host, you need a separate database library. You need the unix ODBC driver manager and the MS SQL ODBC Driver for SQL Server. For information on supported drivers and versions, see Database support in Geneos Compatibility Matrix.

Installing the Microsoft ODBC DRIVER For SQL SERVER

With the release of Microsoft's ODBC Driver for Linux, Microsoft have made the installation and configuration of their driver much easier by supporting Linux package management tools.

  1. Follow the instructions for your platform from the Microsoft site.

  2. To verify that the ODBC Driver on Linux was registered successfully, execute the following command:

    odbcinst -q -d -n "ODBC Driver XX for SQL Server"

    3. Change the quoted string to match your driver version. It should show you output such as:

    [ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
UsageCount=1

  3. Copy or symlink /etc/odbcinst.ini (having above contents) to /opt/microsoft/msodbcsql/lib64 folder.

    sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql/lib64/odbcinst.ini

Setting up the environment before starting Gateway/Netprobe

  1. Create Gateway startup scripts from the templates provided.

    • If you are using Gateway version 5.5.x or newer and also using version 13 of the ODBC driver, then you must set the environment variable values for the MS SQL Server database in your Gateway start up script. In older versions this is not required.

      Run the following commands, where /opt/microsoft/msodbcsql/lib64 is replaced by the path which contains the MS ODBC Driver for SQL Server:

      export SQLSERVER_LIB=/opt/microsoft/msodbcsql17/lib64
export LD_LIBRARY_PATH=${SQLSERVER_LIB}
export ODBCHOME=${SQLSERVER_LIB}
export ODBCSYSINI=${SQLSERVER_LIB}
export ODBCINSTINI=odbcinst.ini
export GENEOS_MSSQL_DRIVER='ODBC Driver 17 for SQL Server'

      Note: The environment variable $GENEOS_MSSQL_DRIVER is used to determine which entry in your odbcinst.ini refers to your MS SQL driver. If this isn't set it defaults to driver 17 as above.

      When MS SQL server is configured Geneos will ensure that by default the library libodbc.so is used. This can be found in the ${SQLSERVER_LIB} directory so you should ensure that this is first in your LD_LIBRARY_PATH.

      Older installations may have the $GENEOS_ODBCLIB environment variable set. If this is the case it will be overridden and a warning written to the log file. This is to prevent iODBC being used by default where it is installed.

  2. Configure MS SQL Server database logging in the database logging section for your Gateway or FIX Analyser Plug-In and run the Gateway and Netprobe. You should be able to log to MS SQL Server database. You should see the following line in log file: Connecting to DB using ODBC Driver XX for SQL Server.

Troubleshooting

  1. If you are using a 32-bit gateway and cannot connect to the database, first check if you can connect to database using FreeTDS driver. This will at least eliminate database problems.

  2. Recheck you environment variables.

  3. If you get an error such as:

    Connection Failed There was a problem connecting => db error msg: IM002 [unixODBC][Driver Manager]
Data source name not found, and no default driver specified,
db error code: 0 (type: DBMSAPI) [0] IM002 [unixODBC][Driver Manager]
Data source name not found, and no default driver specified DBMSAPI

    Please check that you are using latest version of Gateway or Netprobe. Check the log for message "Connecting to DB using ODBC Driver XX for SQL Server", if you can't find this file, then it means that you need to upgrade Gateway/ Netprobe.

  4. If you get error such as:

    Connection Failed There was a problem connecting => db error msg: IM004 [unixODBC][Driver Manager]
Driver's SQLAllocHandle on SQL_HANDLE_HENV failed,
db error code: 0 (type: DBMSAPI) [0] IM004 [unixODBC][Driver Manager]
Driver's SQLAllocHandle on SQL_HANDLE_HENV failed DBMSAPI

    It is possible that you have not deployed all the files required for the ODBC driver. Check that you have installed all the files required for the correct operation of the ODBC driver.

  5. If you get error such as:

    Connection Failed There was a problem connecting => db error msg: 01000 [unixODBC][Driver Manager]
Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0' : file not found,
db error code: 0 (type: DBMSAPI) [0] 01000 [unixODBC][Driver Manager]
Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0' : file not found DBMSAPI

    Check that the driver /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0 is able to find all dependent libraries it is looking for. For this, issue command:

    ldd /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0

    This will list all libraries it is dependent on and if it can't find them, mark them as "not found". Install the libraries at proper location or if already installed, create symbolic links. Usually, it is the case that it cannot find libcrypto and libssl library versions under /lib64.

  6. If you get error such as:

    Connection Failed There was a problem connecting => db error msg: 42000 [unixODBC][Microsoft]
[ODBC Driver 17 for SQL Server][SQL Server]Cannot open database "GWX2341new1" requested by the login.
The login failed.
28000 [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'test'.
01S00 [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute,
db error code: 4060 (type: DBMSAPI) [4060] 42000 [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]
[SQL Server]Cannot open database "testDB" requested by the login. The login failed.

    Check that the database name and/or the credentials are correct.

  7. If you get error such as:

    Connection Failed There was a problem connecting => db error msg: HYT00 [unixODBC][Microsoft]
[ODBC Driver 17 for SQL Server]Login timeout expired
08001 [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific
error has occurred while establishing a connection to SQL Server. Server is not found or not
accessible. Check if instance name is correct and if SQL Server is configured to allow remote
connections. For more information see SQL Server Books Online.

    If you're attempting to connect to a named SQL Server instance from a Linux 64 gateway, then ensure the correct port has been specified. OBDC connections from Linux don't work using instance names to resolve the port, and instead the port must be manually configured. You should configure the SQL Server instance to use a fixed port for communication rather than the dynamically assigned port that it defaults to as connecting to a dynamic MS SQL instance is not supported by the Microsoft ODBC driver. A named instance must be referenced by a port number. See the Microsoft documentation for more information.

Appendices

Database logging errors

Please refer to the following links for possible database errors that can be encountered while trying to log to the database from the Gateway:

Fix variable name duplicates

The varname is a legacy name of the cell being logged. It is the actual cell that is configured as a database logging item. If the history charts in the Active Console and the Gateway Express Reports do not work, then you have to check if there are duplicate varname entries.

Schema 1.1 allows for variable name or varname duplicates in the database logging. To fix this, upgrade the schema to 1.2 which makes the varname unique in the var_ref_table.

To fix variable name duplicates:

  1. Locate the duplicate varname. See Schema 1.8 in Databases to view the column.
  2. Use this query to display the duplicate varname in the dataview:
    3. SELECT *
FROM var_ref_table
GROUP BY varname HAVING (COUNT(varname)>1)
ORDER BY ref DESC
  3. Assess if the tables and the associated Gateway are valid. The Schema 1.8 in Databases guide helps you locate the Gateway where the cell is configured. If the results are invalid, you may delete the records. Otherwise, proceed to the next steps.
  4. Disable the affected Gateway database logging feature.
  5. Configure the affected database logging items to make them log into a single table. In the example below, a table named os_table is created and two Gateways are configured to log into the table:

    6. Gateway 1:

    Field Values
    Name cpu
    Targets / g32811 / New Probe / New Managed entity / cpu usage(type=) / cpu usage / Average_cpu /percentUtlisation
    Table os_table
       

    Gateway 2:

    Field Values
    Name cpu
    Targets / g32812 / New Probe / New Managed entity / cpu usage(type=) / cpu usage / Average_cpu /percentUtlisation
    Table os_table

  6. Move the duplicates into a temporary table, then delete them from the var_ref_table.
  7. Upgrade the Geneos DB schema to 1.2 to add a unique constraint to the varname column. The upgrade script can be found in the Gateway directory:

  8. Restart the Gateways.
  9. Enable the database logging.

    10. The Gateways should now log the values into a single table after upgrading the schema to 1.2.

  10. Transfer the data from the temporary table into the new table you have created.

These are some integrity tests that can be used to test the scripts when fixing the duplicate variable names in the provided example.

  • Prior data migration:
    • SELECT count(*) FROM os_table WHERE node_ref=5000015
 
1> select count(*) from os_table where node_ref=5000015
2> go
 
-----------
	107
  • Actual data migration:
    • INSERT into os_table(timestamp, node_ref, var_ref, value) select dup.timestamp,dup.node_ref, 10000028, dup.value from os_32811_table dup, var_ref_temp_table vr where vr.ref = dup.var_ref and vr.ref = 10000026
  • Post data migration:
    • SELECT count(*) FROM os_table WHERE node_ref=5000015
1> select count(*) from os_table where node_ref=5000015
2> go 
  
-----------        
133
133 = 107 +26
  • Checking if data is migrated:
    • SELECT count(*) FROM os_table WHERE node_ref=5000015
 
1> select count(*) from os_table where node_ref=5000015
2> go
 
-----------
	107