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 versions 10 and 11
- 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:
- Upgrade from a previous schema version.
- 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.
Field | Description | Type |
---|---|---|
major | Major schema version number | int |
minor | Minor schema version number | int |
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.
Field | Description | Type |
---|---|---|
ref | Node ref | int |
gateway | Name of gateway | varchar 50 |
Node | Name of node/Managed Entity | varchar 50 |
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.
Field | Description | Type |
---|---|---|
ref | Variable ref | int |
varname | Name of variable | varchar 200 |
tablename | Name of table to which the variable will be logged | varchar 50 |
event_table 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”.
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 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.
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 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.
Field | Description | Type |
---|---|---|
node_ref | Event ref | int |
name | The hostname (as defined in the setup file) | varchar 255 |
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.
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 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.
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
|
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 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.
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
|
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 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.
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 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.
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 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.
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 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.
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) ) ;
|
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.
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 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.
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 |
|
PostgreSQL |
|
Sybase |
|
Oracle |
|
SQLServer |
|
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 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 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 theORA-08177: can't serialize access for this transaction
error message, change the Isolation level value toRead_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 availabletnsnames.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 thekinit
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:
- In the Gateway Setup Editor navigation pane, right-click Database logging and select Create.
- Specify the Database type, in this example
MySQL
. - Specify the Server name of the machine the database is running on.
- Specify the Database name and Port (default:
3306
). - Under Gateway authentication, specify the User name of the database admin, in this example ‘DBUser’.
- Select
stdAES
for the Password authentication of the user. - Click Set password and enter a password for the user. The database admin account ‘DBUser’ should have write permissions for the ‘ExampleDB’ database.
- Under Client authentication, specify the User name of the client account, in this example ‘ACUser’
- Select
stdAES
for the Password authentication of the user. - Click Set password and enter a password for the user. The client account ‘ACUser’ should have read permissions for the ‘ExampleDB’ database.
- 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.
- In the Gateway Setup Editor navigation pane, right-click Database logging > Items and select New Item.
- Set a Name for the data-item.
- Specify the Xpath to the Target data.
- Select a Table.
- Click the Advanced tab.
- Specify the Min interval between updates.
- Specify the Active time during which logs will be recorded.
- 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.
Event | Logged to table |
---|---|
A Ticker event is generated default ticker events | event_table |
Attributes assigned/changed for a managed entity | attribute_table |
Hostname assigned/changed for a managed entity | hostname_table |
Action fired on a user assignment (if such action configured) | action_table |
Action fired on a user unassignment (if such action configured) | action_table |
Action fired from a Rule | action_table |
Summary Action fired as a result of another Action being throttled | action_table |
Escalation Action being fired as a result of another Action being escalated | action_table |
Alert fired | alert_table |
Summary effect fired as a result of Alert being throttled | alert_table |
Directory item is snoozed | snooze_table |
Directory item is unsnoozed | snooze_table |
Directory item is assigned to a user | user_assignment_table |
Directory item is unassigned to a user | user_assignment_table |
Directory item is made active by a rule | active_table |
Directory item is made inactive by a rule | active_table |
MS SQL Server Database Setup using native client library - 64-bit Linux Gateway Copied
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:
- 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.
- Download and install the latest version of gateway on your Linux box from https://resources.itrsgroup.com as usual.
- 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.
- Follow the instructions for your platform from the Microsoft site.
- 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
- 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
- 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 yourodbcinst.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.
- 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
- 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.
- 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
- 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
- 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
-
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 belibcrypto
andlibssl
.
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.
- 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.
- 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
- 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:
- 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.
- 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.
- Make sure that the SPN is set to the correct realm. For example:
setspn -A <oracle_service_name>/<key_distribution_center>@<domain> <user>
- 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
- Copy the file in the database server and store it in the
/etc/v5srvtab
directory.
Setting up the Oracle Server Copied
- 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>
- 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
- Additional configurations can be included within the libdefaults section, such as:
- ticket_lifetime = 24h
- renew_lifetime = 7d
Setting up the Oracle Client Copied
- 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
- 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
- Once a ticket-granting ticket has been issues, which can be verified using the
klist
command, copy the credential cache found in thetmp
folder to the current Gateway working directory asgeneos.krb5
.
Note
By default, the Gateway assumes that ageneos_krb5
file is present in its current working directory and contains a valid credential cache.
- 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/10/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:
- Locate the duplicate
varname
. See Schema 1.8 in Databases to view the column. - 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
- 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.
- Disable the affected Gateway database logging feature.
- 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 |
- Move the duplicates into a temporary table, then delete them from the
var_ref_table
. - 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:
-
Restart the Gateways.
-
Enable the database logging. The Gateways should now log the values into a single table after upgrading the schema to 1.2.
-
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