Database Logging

Database Logging Copied

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
• PostgreSQL
• 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:

Database Schema Copied

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
• postgresql
• 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 Copied

version_table Copied

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.

node_ref_table Copied

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.

var_ref_table Copied

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.

event_table Copied

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

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 Copied

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.

hostname_table Copied

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.

action_table Copied

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.

snooze_table Copied

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.

user_assignment_table Copied

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.

alert_table Copied

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.

active_table Copied

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.

User Defined Tables Copied

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.

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 Copied

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’).
• 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.
• Oracle only: when moving between tables in Oracle, the var_ref_table must be rebuilt using the following steps:

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.

INSERT INTO MyNewTable SELECT * FROM MyOldTable DELETE FROM MyOldTable;

INSERT INTO 'MyNewTable' SELECT * FROM 'MyOldTable'; DELETE FROM 'MyOldTable';	

INSERT INTO MyNewTable SELECT * FROM MyOldTable; DELETE FROM MyOldTable; GO

INSERT INTO MyNewTable SELECT * FROM MyOldTable; DELETE FROM MyOldTable; COMMIT;

INSERT INTO MyNewTable SELECT * FROM MyOldTable; DELETE FROM MyOldTable;
GO	

UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable';

UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable';

UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable' GO

UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable'; COMMIT;

UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable'; GO

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;								

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

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:

• Restart Gateway.

• Resume Database Logging by:

Database Logging -> Connection -> Resume

Optional Tables Copied

audit_table Copied

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.

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 Copied

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.

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

ALTER TABLE var_ref_table AUTO_INCREMENT = <value to be reset to>;                   

ALTER TABLE var_ref_table ALTER ref SET GENERATED BY DEFAULT RESTART WITH 1;                   

sp_chgattribute var_ref_table, "identity_burn_max", 0, "<value to be reset to>";                        

DROP SEQUENCE var_ref_seq;
CREATE SEQUENCE var_ref_seq START WITH "<value to be reset to>" INCREMENT BY 1 NOMAXVALUE                       

DBCC CHECKIDENT ('var_ref_table', RESEED, <value to be reset to>);                        

Use of NULLs in User Defined Tables Copied

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 Copied

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 Copied

Types of suspension Copied

Discarding item values Copied

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 Copied

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 Copied

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 Copied

Commands Copied

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 Copied

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.

For PostgreSQL, the error number in square brackets (usually [7]) is a generic failure status. The message also contains the unique code straight after the square brackets, for example [7] 42P01 ERROR: relation "missing_table" does not exist. In this case 42P01is the unique error code which should be entered into the list of codes if automatic suspension should be enabled for this case.

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

Database configuration Copied

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 Copied

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 Copied

Boolean value controlling whether database logging is active or not.

Mandatory: No

Default: No logging

databaseLogging > events Copied

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

Mandatory: No

databaseLogging > events > fireOnComponentStartup Copied

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

Mandatory: No

Default: false

databaseLogging > events > fireOnConfigurationChange Copied

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

Mandatory: No

Default: false

databaseLogging > events > fireOnCreateWithOkSeverity Copied

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

Mandatory: No

Default : False

databaseLogging > events > logSnoozeDetails Copied

Controls logging of snoozing and unsnoozing of data items.

Mandatory: No

Default : True

databaseLogging > events > logUserAssignmentDetails Copied

Controls logging of user assignment and unassignment of data items.

Mandatory: No

Default: True

databaseLogging > events > logActions Copied

Controls whether action events are logged to the database.

Mandatory: No

Default: True

databaseLogging > events > logAlerts Copied

Controls if alerts are logged to the database.

Mandatory: No

Default: True

databaseLogging > events > logActiveState Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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

Mandatory: Yes

databaseLogging > connection > database Copied

Specifies the connection parameters for the database to log to. This node can contain a choice of MySQL, Oracle, PostgreSQL, SQL Server, or Sybase.

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication Copied

Authentication details for the gateway to log into the database

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication > userName Copied

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

Mandatory: Yes

databaseLogging > connection > gatewayAuthentication > password Copied

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 Copied

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 Copied

The username that the client will login with.

Mandatory: Yes

databaseLogging > connection > clientAuthentication > password Copied

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 Copied

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

If you do not specify any items to log, then you do not need to define any tables.

Mandatory: No

databaseLogging > tableGroup Copied

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

Mandatory: No

databaseLogging > tableGroup > name Copied

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 Copied

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.

If the tables section is present, then at least one table must be defined.

Mandatory: Yes

databaseLogging > tables > table > type Copied

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

databaseLogging > suspendOnError Copied

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.

Mandatory: No

Deprecated: Please use the discardItemValuesOnError setting.

databaseLogging > discardItemValuesOnError Copied

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 Copied

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 Copied

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 Copied

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

Mandatory: No

Default: 15 insertions

databaseLogging > optimisation > maximumTimeBetweenCommits Copied

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 Copied

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: true

MySQL configuration Copied

databaseLogging > connection > database > mysql Copied

Configuration for the MySQL database type.

Mandatory: No

databaseLogging > connection > database > mysql > serverName Copied

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

Mandatory: Yes

databaseLogging > connection > database > mysql > databaseName Copied

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

Mandatory: Yes

databaseLogging > connection > database > mysql > port Copied

The server port number to connect to MySQL on.

Mandatory: No

Default: 3306

databaseLogging > connection > database > mysql > sslConfiguration > sslMode Copied

Specifies the mode of SSL connection. The following are the possible values:

• Disabled — allows insecure connections only.

• Preferred — establishes a secure connection first. If that fails, then it will establish an insecure connection.

• Required — allows secure connections only.

• Verify_ca — allows secure connections only and additionally verifies the server TLS certificate against the configured Certificate Authority (CA).

• Verify_identity — allows secure connections only and additionally verifies if the server certificate matches the host, where the connection is being established.

Mandatory: No

databaseLogging > connection > database > mysql > sslConfiguration > caCert Copied

Path to the Certificate Authority (CA) certificate file used by the server.

Mandatory: No

databaseLogging > connection > database > mysql > sslConfiguration > clientCert Copied

Path to the client’s public key certificate file.

Mandatory: No

databaseLogging > connection > database > mysql > sslConfiguration > clientKey Copied

Path to the client’s private key file.

Mandatory: No

databaseLogging > connection > database > mysql > sslConfiguration > cipherSuite Copied

Configuration for the MySQL database type.

Mandatory: No

PostgreSQL configuration Copied

databaseLogging > connection > database > postgresql Copied

Configuration for the PostgreSQL database type.

Mandatory: No

databaseLogging > connection > database > postgresql > serverName Copied

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

Mandatory: Yes

databaseLogging > connection > database > postgresql > databaseName Copied

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

Mandatory: Yes

databaseLogging > connection > database > postgresql > port Copied

The server port number to connect to PostgreSQL on.

Mandatory: No

Default: 5432

databaseLogging > connection > database > postgresql > applicationName Copied

The application name for the connection created from Gateway databaseLogging to the Database Server.

Mandatory: No

Default: gateway2 (listen-port <Gateway’s port>)

databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > sslMode Copied

Mode of SSL connection. The following are the possible values:

• Allow — establishes insecure connection first. If that fails, then it will establish a secure connection.

• Disable — allows insecure connections only.

• Prefer — establishes secure connection first. If that fails, then it will establish an insecure connection.

• Require — allows secure connections only.

• Verify-ca — allows secure connections only and additionally verifies the server TLS certificate against the configured Certificate Authority (CA).

• Verify-full — allows secure connections only and additionally verifies if the server certificate matches the host, where the connection is being established.

Mandatory: No

databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > caCert Copied

Path to the Certificate Authority (CA) certificate file used by the server.

Mandatory: No

databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > clientCert Copied

Path to the client’s public key certificate file.

Mandatory: No

databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > clientKey Copied

Path to the client’s private key file.

Mandatory: No

Sybase configuration Copied

databaseLogging > connection > database > sybase Copied

Configuration for the Sybase database type.

Mandatory: No

databaseLogging > connection > database > sybase > interfaceEntry Copied

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

Mandatory: Yes

databaseLogging > connection > database > sybase > databaseName Copied

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

Mandatory: Yes

databaseLogging > connection > database > sybase > serverName Copied

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 Copied

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 Copied

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 Copied

databaseLogging > connection > database > oracle Copied

Configuration for the Oracle database type.

Mandatory: No

databaseLogging > connection > database > oracle > databaseName Copied

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.

Mandatory: Yes

databaseLogging > connection > database > oracle > serverName Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

databaseLogging > connection > database > sqlServer Copied

Configuration for the MS SQL Server database type.

Mandatory: No

databaseLogging > connection > database > sqlServer > serverName Copied

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 “ServerName\InstanceName”. On connection, Geneos will verify that the named instance actually matches the one listening on the specified port.

It is also recommended to avoid using the InstanceName when the SQL Server Browser service is not running or not supported. For more information on SQL Server Browser, see SQL Server Browser service.

Mandatory: Yes

databaseLogging > connection > database > sqlServer > databaseName Copied

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

Mandatory: Yes

databaseLogging > connection > database > sqlServer > useKerberos Copied

Use Kerberos (also known as Windows Integrated Authentication) when connecting.

If you enable this option, do not specify any database credentials for Gateway authentication.

Mandatory: No

Note

You must ensure that you have configured Gateway to use a Kerberos keytab that allows passwordless access to your database.

To configure Gateway to use Kerberos authentication for database logging:

• Use the ktutil tool to create a keytab and the kinit tool to verify a keytab is accepted by Kerberos.
• Check that the keytab allows for passwordless access to your database using sqlcmd with the -E option.
• Start the Gateway with the -kerberos-principal and -kerberos-keytab options.

For more information about starting Gateway with command line options, see Command line options in Gateway Installation Guide.

databaseLogging > connection > database > sqlServer > port Copied

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 Copied

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 Copied

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 Copied

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 Copied

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

Mandatory: No

databaseLogging > itemGroup > name Copied

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 Copied

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 - Predicates for more information on identifying predicates and non-identifying predicates.

Mandatory: Yes

Note:  Beginning Geneos 5.5.x, the Managed Entity display name is used in the user readable paths throughout the Gateway Setup Editor, except when the GSE is opened as a standalone application. This only applies if you open the GSE within the Active Console.

databaseLogging > items > item > minInterval Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

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

databaseLogging > dumpFile Copied

Configuration node for the Database cache dump file options.

databaseLogging > dumpFile > disable Copied

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 Copied

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 Copied

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 Copied

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 Copied

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 Copied

Note

MS SQL is not supported in an ARM64 environment. Instead, MS SQL is designed to run on AMD64 or x64 processors.

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 Copied

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"

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

6. 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 Copied

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 Copied

Error 1 Copied

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.

Error 2 Copied

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

1. Verify you are using the latest version of Gateway or Netprobe. If the log doesn’t contain the message Connecting to DB using ODBC Driver XX for SQL Server, then you must upgrade Gateway and Netprobe.

Error 3 Copied

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

1. 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.

Error 4 Copied

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

1. To verify that the driver /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0 can find all its dependent libraries, run the following command:

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

   This script lists all libraries the program depends on. If it can’t find a library, it marks it as “not found.” You’ll then need to install the missing libraries in the proper location. For libraries already installed, but not found in /lib64, create symbolic links. In most cases, the missing libraries will likely be libcrypto and libssl.

Error 5 Copied

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.

1. Check that the database name and the credentials are correct.

Error 6 Copied

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.

1. Connecting to a named SQL Server instance from a Linux 64 gateway requires specifying the correct port. ODBC drivers on Linux rely on port numbers, not instance names, to establish connections. Configure the SQL Server instance to use a fixed port for communication, as the Microsoft ODBC driver doesn’t support connecting to dynamically assigned ports. Simply reference the named instance by its port number. For more information, Microsoft documentation.

Error 7 Copied

Connection Failed There was a problem connecting => db error msg: 28000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'gensql'., 
db error code: 18456 (type: DBMSAPI) [18456] 28000 [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'gensql'. DBMSAPI

1. Check that you have completed the necessary steps to use Kerberos when connecting to the database.

Setting up Oracle connection with Kerberos authentication Copied

This section provides a configuration guide for setting up the Oracle connection with Kerberos authentication, enabling connectivity between SQL Toolkit and Gateway Database Logging.

Preparing the Active Directory Server Copied

To prepare the Active Directory server, do the following:

1. Create a service account in the Active Directory for the database server to validate the Kerberos tickets. This user does not need any specific rights but enable password never expires.
2. Ensure that you deselect the account option Use Kerberos DES Encryption types for this account and select the option Do not require Kerberos preauthentication for this user.
3. Make sure that the SPN is set to the correct realm. For example: setspn -A <oracle_service_name>/<key_distribution_center>@<domain> <user>
4. Extract the keytab file for this designated user, so that we can create tickets without inputting a password. To do this, use the following command:
   ktpass -princ <oracle_service_name>/<key_distribution_center>@<domain> -crypto all -pass <password> -mapuser <user>@<domain> -out v5srvtab
5. Copy the file in the database server and store it in the /etc/v5srvtab directory.

Setting up the Oracle Server Copied

1. Generate a Kerberos ticket, which will be used to connect to the Kerberos server for ticket validation. To do this: $ORACLE_HOME/bin/okinit -k -t /etc/v5srvtab <oracle_service_name>/<key_distribution_center>
2. Update the following files:

• sqlnet.ora

# sqlnet.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
# Debug/Tace logs can be removed

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)
TRACE_LEVEL_SERVER = ADMIN
SQLNET.KERBEROS5_CLOCKSKEW = 6000
SQLNET.KERBEROS5_CONF = /etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true
LOG_DIRECTORY_SERVER = /opt/oracle/logging
DIAG_ADR_ENABLED = OFF
SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

• tnsnames.ora

# tnsnames.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

LISTENER_ORCLPDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))

• /etc/krb5.conf

# Configuration snippets may be placed in this directory as well
includedir /etc/krb5.conf.d/
includedir /var/lib/sss/pubconf/krb5.include.d/

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = QAMNL.COM

[realms]
 QAMNL.COM = {
        kdc = ksad2.qamnl.com
        admin_server = ksad2.qamnl.com
        default_domain = QAMNL.COM
 }

[domain_realm]
 qamnl.com = QAMNL.COM
 .qamnl.com = QAMNL.COM

3. Additional configurations can be included within the libdefaults section, such as:

• ticket_lifetime = 24h
• renew_lifetime = 7d

Setting up the Oracle Client Copied

1. Update the following files:

• sqlnet.ora

SQLNET.KERBEROS5_CONF = /etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true
ADR_BASE = /opt/oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, KERBEROS5)
TRACE_LEVEL_CLIENT = ADMIN
SQLNET.KERBEROS5_CLOCKSKEW = 6000
LOG_DIRECTORY_SERVER = /opt/oracle/logging
DIAG_ADR_ENABLED = OFF
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

• tnsnames.ora

ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

LISTENER_ORCLPDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))

• /etc/krb5.conf

includedir /etc/krb5.conf.d/
includedir /var/lib/sss/pubconf/krb5.include.d/

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = QAMNL.COM

[realms]
 QAMNL.COM = {
        kdc = ksad2.qamnl.com
        admin_server = ksad2.qamnl.com
        default_domain = QAMNL.COM
 }

[domain_realm]
 qamnl.com = QAMNL.COM
 .qamnl.com = QAMNL.COM

3. Additional configurations can be included within the libdefaults section, such as:

• ticket_lifetime = 24h
• renew_lifetime = 7d

Setting up the Gateway Database Logging plugin Copied

1. Once a ticket-granting ticket has been issues, which can be verified using the klist command, copy the credential cache found in the tmp folder to the current Gateway working directory as geneos.krb5.

Note

By default, the Gateway assumes that a geneos_krb5 file is present in its current working directory and contains a valid credential cache.

2. Enable the Database logging for Oracle, but do not put any credentials in the Gateway and Client authentication sections.

Appendices Copied

Database logging errors Copied

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

• MySQL - http://dev.mysql.com/doc/refman/5.0/en/error-handling.html
• PostgreSQL - https://www.postgresql.org/docs/current/errcodes-appendix.html
• Sybase - http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00462.1510/concepts/con_syberror.html
• Oracle - http://docs.oracle.com/cd/B28359_01/server.111/b28278/toc.htm
• MS SQL Server - http://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx

Fix variable name duplicates Copied

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 does not work, then you must 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:

SELECT *
FROM var_ref_table
GROUP BY varname HAVING (COUNT(varname)>1)
ORDER BY ref DESC

4. 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.
5. Disable the affected Gateway database logging feature.
6. 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: Gateway 1:

Gateway 2:

13. Move the duplicates into a temporary table, then delete them from the var_ref_table.
14. 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:

16. Restart the Gateways.

17. Enable the database logging. The Gateways should now log the values into a single table after upgrading the schema to 1.2.

18. 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