--- title: "Database Logging" description: "" url: "https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/data-access-and-publishing/geneos_dblogging_tr/index.html" --- 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](#ms-sql-server-database-setup-using-native-client-library---64-bit-linux-gateway) on how to configure the set-up. For information on supported database versions, see [Database support in Geneos Compatibility Matrix](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html#database-support). **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 the **Database logging** > **Advanced** section: | Event | Logged to table | |-------|-------| | A Ticker event is generated [default ticker events](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/geneos_advancedfeatures_tr/index.html#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 - 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/databases/index.html). 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 **Caution** Upgrades must be performed between consecutive schema versions. For example, if you have schema v1.0, you first need to upgrade to schema v1.1, then upgrade to schema v1.2. You should **never** upgrade directly from 1.0 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.9.

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 **Note** A wildcard `%` is added by default at the beginning of each entry in the **varname** column. This is the default behavior for database logging in `var_ref_table` and was introduced for compatibility with Gateway 1. | 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. **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".

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

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

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](#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.

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

#### 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'). - 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.

Database	Query
MySQL	INSERT INTO MyNewTable SELECT * FROM MyOldTable DELETE FROM MyOldTable;
PostgreSQL	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

Database	Query
MySQL	UPDATE var_ref_table SET tablename='MyNewTable' WHERE tablename='MyOldTable';
PostgreSQL	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

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;

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;

### 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: - Restart Gateway. - Resume Database Logging by: ``` Database Logging -> Connection -> Resume ``` #### Optional Tables ##### audit_table If logging audit information to database (see the [Auditing](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/geneos_advancedfeatures_tr/index.html#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.

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

##### 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. The SQL to generate this table can be found in the appropriate database schema file which is located in the Gateway resources directory.

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

Database	Query
MySQL	`ALTER TABLE var_ref_table AUTO_INCREMENT = <value to be reset to>;`
PostgreSQL	`ALTER TABLE var_ref_table ALTER ref SET GENERATED BY DEFAULT RESTART WITH 1;`
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>);`

### 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](#databaselogging--items--item--mininterval), [forcedInterval](#databaselogging--items--item--forcedinterval), [absoluteMargin](#databaselogging--items--item--absolutemargin) and [percentMargin](#databaselogging--items--item--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](#databaselogging--items--item--activetime)). A NULL can also be logged when a data-item is deleted. This is controlled by the [logNullOnDelete](#databaselogging--items--item--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-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](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/monitoring-and-alerts/geneos_commands_tr/index.html#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. 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 `42P01`is 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](#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](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/monitoring-and-alerts/geneos_rulesactionsalerts_tr/index.html). 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/geneos_advancedfeatures_tr/index.html#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](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/geneos_advancedfeatures_tr/index.html#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](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/geneos_advancedfeatures_tr/index.html#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, Oracle, PostgreSQL, SQL Server, or Sybase. 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/security/access-controls/gateway_secure_passwords/index.html). 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/security/access-controls/gateway_secure_passwords/index.html). Mandatory: Yes #### databaseLogging > tables 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 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](#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 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](#databaselogging--discarditemvaluesonerror) setting instead. Mandatory: No Deprecated: Please use the [discardItemValuesOnError](#databaselogging--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](#discarding-item-values) and [Initiating suspension automatically](#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](#pausing-the-connection) and [Initiating suspension automatically](#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: true #### 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 ##### databaseLogging > connection > database > mysql > sslConfiguration > sslMode 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 Path to the Certificate Authority (CA) certificate file used by the server. Mandatory: No ##### databaseLogging > connection > database > mysql > sslConfiguration > clientCert Path to the client's public key certificate file. Mandatory: No ##### databaseLogging > connection > database > mysql > sslConfiguration > clientKey Path to the client's private key file. Mandatory: No ##### databaseLogging > connection > database > mysql > sslConfiguration > cipherSuite Configuration for the MySQL database type. Mandatory: No #### PostgreSQL configuration ##### databaseLogging > connection > database > postgresql Configuration for the PostgreSQL database type. Mandatory: No ##### databaseLogging > connection > database > postgresql > serverName The hostname or IP address of the machine where the PostgreSQL database is running. Mandatory: Yes ##### databaseLogging > connection > database > postgresql > databaseName The name of the database to log to, e.g. Geneos. Mandatory: Yes ##### databaseLogging > connection > database > postgresql > port The server port number to connect to PostgreSQL on. Mandatory: No Default: 5432 ##### databaseLogging > connection > database > postgresql > applicationName The application name for the connection created from Gateway databaseLogging to the Database Server. Mandatory: No Default: gateway2 (listen-port ) ##### databaseLogging > connection > database > postgresql > useKerberos 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 Default: False **Note** You must ensure that Gateway can obtain a Kerberos identity that allows passwordless access to your database. Use either a Kerberos keytab or an existing Kerberos credential cache. To configure Gateway to use Kerberos authentication for database logging, choose one of the following: - Keytab — 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 `psql -h -p -d postgres -U `. Start the Gateway with the `-kerberos-principal` and `-kerberos-keytab` options. - Credential cache — obtain a ticket into a credential cache (for example with `kinit`) and start the Gateway with the `-kerberos-credential-cache` option. In this case, the `-kerberos-principal` option can be used to specify the database user name. If the `-kerberos-principal` option is not specified, the database user name defaults to the operating system user. For more information about starting Gateway with command line options, see [Command line options in Gateway Installation Guide](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/gateway_installation_guide/index.html#command-line-options). #### databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > sslMode 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 Path to the Certificate Authority (CA) certificate file used by the server. Mandatory: No #### databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > clientCert Path to the client's public key certificate file. Mandatory: No #### databaseLogging > connection > database > postgresql > applicationName > sslConfiguration > clientKey Path to the client's private key file. Mandatory: No #### 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 ) #### 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. 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 ) #### 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 "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](https://learn.microsoft.com/en-us/sql/tools/configuration-manager/sql-server-browser-service?view=sql-server-ver16). Mandatory: Yes ##### databaseLogging > connection > database > sqlServer > databaseName The name of the database to log to, e.g. Geneos. Mandatory: Yes ##### databaseLogging > connection > database > sqlServer > useKerberos 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 Gateway can obtain a Kerberos identity that allows passwordless access to your database. Use either a Kerberos keytab or an existing Kerberos credential cache. To configure Gateway to use Kerberos authentication for database logging, choose one of the following: - Keytab — 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. - Credential cache — obtain a ticket into a credential cache (for example with `kinit`) and start the Gateway with the `-kerberos-credential-cache` option. For more information about starting Gateway with command line options, see [Command line options in Gateway Installation Guide](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/gateway_installation_guide/index.html#command-line-options). ##### 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** ![](https://docscdn.itrsgroup.com/geneos/images/Icons/save16x16.png "icon") 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 - Predicates](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/gateway-administration/geneos_xpath_ug/index.html#xpath-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 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](https://docs.itrsgroup.com/docs/geneos/7.11.0/processing/monitoring-and-alerts/gateway_active_times/index.html). 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. ![](https://docscdn.itrsgroup.com/geneos/images/gateway/gw-dblogging-dump-file.png) #### 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 **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](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](https://docs.itrsgroup.com/docs/all/geneos/7x-geneos-compat-matrix/index.html#database-support). ### 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](https://docs.microsoft.com/en-us/sql/connect/odbc/linux/installing-the-microsoft-odbc-driver-for-sql-server-on-linux/). 2. To verify that the ODBC Driver on Linux was registered successfully, execute the following command: ```bash 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: ```plaintext [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. ```bash 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 ### Error 1 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 ``` 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 ``` 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 ``` 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. ### Error 4 ``` 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 ``` 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 ``` 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 the credentials are correct. ### Error 6 ``` 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. ``` 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](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns). ### Error 7 ``` 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 ``` Check that you have completed the necessary [steps to use Kerberos when connecting to the database](#databaselogging--connection--database--sqlserver--usekerberos). ## Setting up Oracle connection with Kerberos authentication 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 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 /@ ` 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 /@ -crypto all -pass -mapuser @ -out v5srvtab``` 5. Copy the file in the database server and store it in the `/etc/v5srvtab` directory. ### Setting up the Oracle Server 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 /` 2. Update the following files: - `sqlnet.ora` ```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` ```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` ```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 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 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. ![](https://docscdn.itrsgroup.com/geneos/images/gateway/databases-images/oracle-kerberos-gateway-dblogging-6-7.png) ## 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: - MySQL - - PostgreSQL - - Sybase - - Oracle - - MS SQL Server - ### 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 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 the [current version](#version_table) to make the `varname` unique in the `var_ref_table`. **Caution** Upgrades must be performed between consecutive schema versions. For example, if you have schema v1.0, you first need to upgrade to schema v1.1, then upgrade to schema v1.2. You should **never** upgrade directly from 1.0 to 1.2. To fix variable name duplicates: 1. Locate the duplicate `varname`. See [Schema 1.9 in Databases](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/databases/index.html#schema-19) 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 ``` 3. Assess if the tables and the associated Gateway are valid. The [Schema 1.9 in Databases](https://docs.itrsgroup.com/docs/geneos/7.11.0/administration/databases/index.html#schema-19) 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: **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 the current version to add a unique constraint to the `varname` column. The upgrade script can be found in the Gateway directory: ![](https://docscdn.itrsgroup.com/geneos/images/gateway-db-logging-fix-duplicate.png) 8. Restart the Gateways. 9. Enable the database logging. The Gateways should now log the values into a single table after upgrading the schema to the current version. 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 ```