Databases

Introduction Copied

This document provides details of how to set-up database in MySQL, PostgreSQL, Oracle, Sybase and SQL Server servers for use with Geneos.

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

The information included covers:

sizing
setting up user accounts with appropriate access
useful commands for each type of database
simple housekeeping scripts

It is assumed that the database server itself has already been installed. Please see Database Schema in Gateway Database Logging for information on the structure of the Geneos database.

The Geneos Database Copied

In Sybase, MySQL, and SQLServer a particular server may contain a number of different databases. Tables are created within each database. Effectively each application uses a different database. Once connected to the server and using the database, tables can be accessed directly by table name.

In PostgreSQL each database may have several schemas but, by default, tables are created in a schema called public. Additionally, users and groups of users are shared across the entire cluster, but no other data is shared across databases. Any given client connection to the server can access only the data in a single database, the one specified in the connection request.

In Oracle there is generally a single database instance per Oracle instance. Within each Oracle instance schemas are created which contain the tables for an application. Tables are referenced by schema_name.tablename. Each user is assigned a default schema upon creation; this schema name is the same as the user name.

For Oracle tables to be referenced without specifying the schema you must either be connected to Oracle as the user or use a synonym. Details on creating synonyms can be found in the Oracle section.

Database schemas - the creation scripts Copied

The database schemas or creation scripts are packaged with the Gateway2 software and appear in the appropriate directory (mysql, oracle, Sybase or sqlServer) under resources/databases as schema-n.n.sql

Sizing Copied

It is difficult to provide an accurate figure for the size of the database required as database sizing is affected by so many variables:

The number of metrics stored and the interval at which these are stored .
This itself is a function of the number of managed entities monitored, plug-ins used, etc.
The volatility of the metrics.
Housekeeping / archiving strategy.

A minimal size for a database in megabytes can be calculated using the following formula:

Size = Number of Managed Entities * Number of months data to be stored * 10

In practice the size of the database required can be minimised by:

Keeping only a limited number of days of data
Carefully considering what data is logged and only logging data which has a known use
Specifying the interval at which items are logged and using margins

Archiving Strategies Copied

There is no automated archiving or housekeeping of data from the database.

All data logged to the database is written with a Unix timestamp making it a relatively simple process to remove all data prior to a certain date, i.e. it is possible to develop a set of routines which enable a rolling 3 or 6 months worth of data to be kept.

Example of housekeeping scripts can be found in the relevant database sections below. These scripts just purge old data from the database. They can be used as a basis for an archiving script in as much as the queries provided can be used for archiving as well as purging of data.

Required Access Levels Copied

The Geneos user used by the gateway needs to have select, insert, update access to the tables

The logins used by the ActiveConsole only need select access to the various tables.

MySQL Copied

Required client libraries Copied

Linux supported libraries Copied

Geneos supports the following libraries and loads the first available library, as listed:

libmysqlclient.so.21
libmysqlclient_r.so.18
libmysqlclient_r.so.16
libmysqlclient_r.so.15
libmysqlclient_r.so
libmysqlclient.so.18
libmysqlclient.so.16
libmysqlclient.so.15
libmysqlclient.so
libmariadb.so.3
libmariadb.so

Geneos attempts to load libmysqlclient.so.21 first. If it does not find it, then it tries the next shared object, and so on.

You can specify a specific library to load by setting the GENEOS_MYSQL_CLIENT_LIBRARY environment variable. For example, to load libmysqlclient.so.18 set the value of GENEOS_MYSQL_CLIENT_LIBRARY to libmysqlclient.so.18.

Note
In case of errors related to SSL, the SQL API will find and use a more suitable version of the MySQL client library.

Windows supported libraries Copied

Geneos supports the following libraries and loads the first available library, as listed:

libmysql.dll
libmariadb.dll

Geneos attempts to load libmySQL.dll first. If it does not find it, then it tries libmariadb.dll.

Using MySQL Copied

mysql is a command line client utility which allows you to connect to and administer a mysql database server.

Entering mysql with no paramters will attempt to connect to the mysql server on the localhost as the user root.

To connect as user <user_name> to a database <database_name> on a remote host <host_name> use

mysql -u<user_name> -h<host_name> <database_name>

Note
There must be no space between -u and the user_name.

Setting up the Geneos database schema Copied

Create a user

mysql> create user geneos_user;
Query OK, 0 rows affected (0.08 sec)
mysql>

Create a database

mysql> create database geneos_db;
Query OK, 1 row affected (0.01 sec)

mysql>

Give the user appropriate permissions

mysql> grant all on geneos_db.* to 'geneos_user'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on geneos_db.* to 'geneos_user'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

Note
Both grants (@’localhost’ and ‘%’) are needed if the user is going to be able to access the database from the localhost and remote hosts.

Adding identified by ‘password’, after the user on each grant entry, will give the user the password.

Giving the user full privileges (all) means that the user is a superuser and has full access to do anything within the geneos_db database except to grant privileges. To give the user the ability to grant privileges use " grant all on ‘db_name’ to ‘user’@‘host’ with grant option; "

An example setting a password and giving grant privileges:

mysql> grant all on geneos_db.* to 'geneos_user'@'localhost' identified by 'geneos_passwd' with grant option;
Query OK, 0 rows affected (0.03 sec)

After all the grants have been completed issue a flush privileges command to ensure the privileges are reloaded from the grant tables

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

Load the schema

Assuming the schema is in the file schema_1.n.sql, check the first line of the schema sets the database to the required database in this case geneos_db.

$ more schema_1.n.sql

use geneos_db;

CREATE TABLE … …

Run in the schema

mysql -ugeneos_user < schema_1.n.sql

For more information on how to set up MySQL database in Linux, see SQL Toolkit configuration.

Alternative Storage Engines Copied

Both MySQL and MariaDB support multiple storage engines including MyISAM (the default engine before MySQL 5.5.5) and InnoDB, the default engine in MySQL releases since 2010. InnoDB supports a number of reliability and scalability features such as transactions (commit and rollback), row-level locking and crash recovery. A single MySQL database can contain both MyISAM tables and InnoDB tables: the storage engine is determined when the table is created it can also be changed later by using the ALTER TABLE statement.

Before Geneos release 5.0, the database creation scripts supplied with the Gatewayspecified the MyISAM engine. From Geneos release 5.0, these scripts do not explicitly specify the storage engine: the current default engine will be used. Since this does not change the SQL statements required to query the database the Geneos schema number has not been updated. Both Gateways from Geneos release 5.0 and Gatewaysfrom older Geneos releases work correctly with either InnoDB or MyISAM tables. However, from Geneos release 5.0, Gateways are generally tested only against InnoDB tables.

Please refer to the MySQL documentation, in particular storage engine setting and converting tables to innodb, for more information.

Useful Commands Copied

Who am I? Copied

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.02 sec)

mysql>

What databases exist? Copied

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| geneos_cjt         |
| gwy2               |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>

What is the current database? Copied

mysql> select database();
+------------+
| database() |
+------------+
| geneos_db  |
+------------+
1 row in set (0.00 sec)

mysql>

What users exist? Copied

mysql> select user,password from mysql.user;
+-----------+-------------------------------------------+
| user      | password                                  |
+-----------+-------------------------------------------+
| root      | *15ECE87A73876CA51D7444400EA7CD03BB8FA7B3 |
| root      | *15ECE87A73876CA51D7444400EA7CD03BB8FA7B3 |
| mysql     |                                           |
| sybase    |                                           |
| dmiller   |                                           |
| cjt       |                                           |
| *         |                                           |
| cjt2      |                                           |
| cjt2      |                                           |
| cjt_db    |                                           |
| cjt_db    |                                           |
| someuser  |                                           |
| dmiller   |                                           |
+-----------+-------------------------------------------+
13 rows in set (0.01 sec)

Switch to another database Copied

mysql> use geneos_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

List the tables in the current database Copied

mysql> show tables;
+----------------------+
| Tables_in_geneos_db  |
+----------------------+
| event_table          |
| net_table            |
| node_ref_table       |
| os_table             |
| processes_table      |
| text_table           |
| var_ref_table        |
| version_table        |
+----------------------+
8 rows in set (0.00 sec)

mysql>

Get the details of a table Copied

Get details of the columns in a table

mysql> describe os_table;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| timestamp | int(11) | NO   |     | NULL    |       |
| node_ref  | int(11) | NO   | MUL | NULL    |       |
| var_ref   | int(11) | NO   |     | NULL    |       |
| value     | double  | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.29 sec)

mysql>

Null - if yes the column can contain nulls

describe <table_name> is a short cut for “show columns from table <table_name>

Show details of an index on a table

mysql> show index from var_ref_table;
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table         | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| var_ref_table |          0 | PRIMARY       |            1 | ref         | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| var_ref_table |          0 | var_ref_index |            1 | varname     | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| var_ref_table |          0 | var_ref_index |            2 | tablename   | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+---------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)            mysql>

Non_unique - 0 if the index cannot contain duplicates, 1 if it can

On the var_ref_table in the example above, var_ref_index is a unique index consisting of varname and tablename columns.

Count the number of rows in a table

mysql> select count(*) from event_table;
+----------+
| count(*) |
+----------+
|  6319742 |
+----------+
1 row in set (0.12 sec)

mysql>

Get the exact Data Definition Language (DDL) statement used to create the table, including the storage engine used:

mysql> show create table os_table;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                               |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| os_table | CREATE TABLE `os_table` (  `timestamp` int(11) NOT NULL,  `node_ref` int(11) NOT NULL,  `var_ref` int(11) NOT NULL,  `value` double DEFAULT NULL,  KEY `os_index` (`node_ref`,`var_ref`,`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Limit the number of rows returned Copied

mysql> select * from os_table limit 5;
+------------+----------+---------+-------+
| timestamp  | node_ref | var_ref | value |
+------------+----------+---------+-------+
| 1246981633 |        2 |       1 |    48 |
| 1246981633 |        3 |       1 |    48 |
| 1246981633 |        4 |       1 |  30.9 |
| 1246981633 |        5 |       1 |    48 |
| 1246981693 |        4 |       1 |  29.5 |
+------------+----------+---------+-------+
5 rows in set (0.00 sec)

mysql>

What access permissions does a user have? Copied

Logged in as the user, use the show grants command:

mysql> show grants;
+-------------------------------------------------------------------------------------+
| Grants for geneos_user@localhost                                                    |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'geneos_user'@'localhost'                                     |
| GRANT ALL PRIVILEGES ON `geneos_db`.* TO 'geneos_user@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

To list all the permissions a user has been given select form the mysql.user table

mysql> select * from mysql.user where user = 'cjt';
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| Host      | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
| %         | cjt  |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
| localhost | cjt  |          | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            |          |            |             |              |             0 |           0 |               0 |                    0 |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+
2 rows in set (0.00 sec)
mysql>

To list all the permissions a user has been given within a database, select from the mysql.db table

mysql> select * from mysql.db where user = 'cjt' and db = 'geneos_cjt';
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host      | Db         | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %         | geneos_cjt | cjt  | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| localhost | geneos_cjt | cjt  | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+-----------+------------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
2 rows in set (0.07 sec)

mysql>

Handling Timestamps Copied

To convert from a date and time to a Unix timestamp use UNIX_TIMETSTAMP(date)

mysql> select unix_timestamp('2009-12-24 09:24:10');
+---------------------------------------+
| unix_timestamp('2009-12-24 09:24:10') |
+---------------------------------------+
|                            1261646650 |
+---------------------------------------+
1 row in set (0.02 sec)

mysql>

To convert from a Unix timestamp to a normal time use FROM_UNIXTIME(timestamp)

mysql> select from_unixtime(1261581335);
+---------------------------+
| from_unixtime(1261581335) |
+---------------------------+
| 2009-12-23 15:15:35       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

mysql> select from_unixtime(timestamp),value from os_table where var_ref = 151 and node_ref = 7;
+--------------------------+-------+
| from_unixtime(timestamp) | value |
+--------------------------+-------+
| 2009-12-23 15:15:35      | 42332 |
| 2009-12-23 15:15:44      | 42336 |
+--------------------------+-------+

Creating additional Geneos tables Copied

To create additional tables for Geneos data use the following statements substituting appropriately for in TABLE and KEY sections

For numeric data -

CREATE TABLE `<name>_table` (
`timestamp` int(11) NOT NULL,
`node_ref` int(11) NOT NULL,
`var_ref` int(11) NOT NULL,
`value` double default NULL,
KEY `<name>_index` (`node_ref`,`var_ref`,`timestamp`)
)  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

For text data -

CREATE TABLE `<name>_table` (
`timestamp` int(11) NOT NULL,
`node_ref` int(11) NOT NULL,
`var_ref` int(11) NOT NULL,
`value` varchar(250) collate latin1_general_ci default NULL,
KEY `<name>_index` (`node_ref`,`var_ref`,`timestamp`)
) DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Creating additional read-only users Copied

The ActiveConsole connects directly to the database to extract information, for imports into the Event Ticker and to plot historic graphs. The ActiveConsole can use the same user as the gateway or another user but as it is only reading data the ActiveConsole should ideally use a read-only user. In addition, if you wish to extract information from the database using another reporting tool or run ad-hoc queries on the database you should probably use a read-only user.

Creating a read-only user Copied

To create a read-only user, create a user

mysql> create user geneos_ro;
Query OK, 0 rows affected (0.08 sec)

mysql>

Give the user appropriate permissions

mysql> grant select on geneos_db.* to 'geneos_ro'@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> grant select on geneos_db.* to 'geneos_ro'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

Note
Both grants (@’localhost’ and ‘%’) are needed if the user is going to be able to access the database from the localhost and remote hosts.

After all the grants have been completed issue a flush privileges command to ensure the privileges are reloaded from the grant tables

Housekeeping Copied

A simple housekeeping script for MySql. Replace all occurrences of with the appropriate table.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than @num_days                                           */
/*                                                                            */
/******************************************************************************/

DROP procedure purge_<name>_table;

DELIMITER $$

CREATE procedure purge_<name>_table
(
in num_days numeric
)
BEGIN

delete
from <name>_table
where timestamp < unix_timestamp(date_sub(curdate(), interval num_days day));

END $$

DELIMITER ;

To load this stored procedure into the database use

mysql -h hostname -u username -D database_name < purge_script_name.sql

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines:

#!/bin/sh

HOST=redhatws
USER=geneos_user
PASSWORD=geneos_password
DBASE=geneos_db
NUM_DAYS=90

mysql -h ${HOST} -u ${USER} -D ${DBASE} -P ${PASSWORD} << EOF
call purge_<name>_table($NUM_DAYS);
EOF

PostgreSQL Copied

Required Client Libraries Copied

Ensure PostgreSQL client libraries (libpq.so) are installed on the host running the Gateway and set up the POSTGRESQL_HOME and LD_LIBRARY_PATH in the start_gateway script appropriately. Below is an example of the shell commands to set up the correct environment variables, these may vary depending on the details of your installation:

POSTGRESQL_LIB=/opt/PostgreSQL/10/lib ; export POSTGRESQL_LIB
LD_LIBRARY_PATH=${POSTGRESQL_LIB}:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

Using psql Copied

You can use the psql command line client utility to connect to and administer a PostgreSQL database server.

Once you are connected to the database server you will enter the psql command prompt. The psql tool has a number of unique features, in particular you can use backslash commands to interact with the database using psql. For example to quit from psql type \q.

The psql prompt has other important unique features. It shows which database you are connected to and it ends with a # character if you are connected as an administrator. This is important since you cannot switch to a different database without starting a new session.

To create new databases, users or user roles you should connect as an administrator:


$ psql -h pgsrv.example.com -d postgres -U postgres
Password for user postgres:

Cannot read termcap database;
using dumb terminal settings.
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "pgsrv.example.com" at port "5432"
postgres=# create role geneos_user with inherit password 'geneos_passwd';
CREATE ROLE
postgres=# \q

Note
If you see the message “Cannot read termcap database”, this should be ignored.

To perform most functions, including creating tables within a database, you should connect as a non-privileged user:

$ psql -h pgsrv.example.com -d geneos_db -U geneos_user
Password for user geneos_user:

Cannot read termcap database;
geneos_db=> \conninfo
You are connected to database "geneos_db" as user "geneos_user" on host "pgsrv.example.com" at port "5432".
geneos_db=> SELECT COUNT(1) From int_table; count
-------     0
(1 row)
geneos_db=> \q

Setting up the Geneos database schema Copied

Create a Geneos user and database Copied

To create a Geneos user and database:

Connect as an administrator.
Create a role to own the Geneos database and a user with the owner role.
Create the database itself.

postgres=# create role geneos_dbo with nologin noinherit;
CREATE ROLE
postgres=# create user geneos_user with inherit password 'geneos_passwd';
CREATE ROLE
postgres=# grant geneos_dbo to geneos_user;
GRANT ROLE
postgres=# create database geneos_db with owner = geneos_dbo;
CREATE DATABASE
postgres=# \q

Create the Geneos schema Copied

To create the schema:

You will need to load the Geneos schema from the file schema_1.n.sql, check that it begins by invoking the database owner role created above:

$ more schema_1.n.sql
/****************************************************************************************/
-- geneos_dbo should be
-- either      * the PostgreSQL user which Gateway connects as
-- or (better) * a role which has been granted to the Gateway user
set role geneos_dbo;

Connect as the user created above and populate the schema by executing the schema file

geneos_db=> \i schema_1.n.sql
SET
CREATE FUNCTION
CREATE FUNCTION
DO
DROP FUNCTION
DROP FUNCTION
CREATE TABLE
...
CREATE TABLE
geneos_db=> \q

Useful commands Copied

Where possible the following examples list both an internal psql command and an equivalent SQL query for each task.

Who am I? Copied

geneos_db=> \conninfo
You are connected to database "geneos_db" as user "geneos_user" on host "pgsrv.example.com" at port "5432"".
geneos_db=> SELECT current_user; current_user
-------------- geneos_user
(1 row)

geneos_db=>

What users exist? Copied

geneos_db=> \du                                     List of roles Role name   |                         Attributes                         |  Member of
-------------+------------------------------------------------------------+-------------- geneos_dbo  | No inheritance, Cannot login                               | {} postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {} geneos_user |                                                            | {geneos_dbo}

geneos_db=> SELECT u.usename AS "User Name", CASE WHEN u.usesuper THEN 'yes' ELSE 'no' END AS "Superuser"
geneos_db-> FROM pg_catalog.pg_user u; User Name | Superuser
-----------+----------- postgres  | yes test      | no
(2 rows)

geneos_db=>

What databases exist? Copied

geneos_db=> \l                                   List of databases     Name     |   Owner    | Encoding |  Collate   |   Ctype    |   Access privileges
--------------+------------+----------+------------+------------+----------------------- geneos_db    | geneos_dbo | UTF8     | en_US.utf8 | en_US.utf8 | postgres     | postgres   | UTF8     | en_US.utf8 | en_US.utf8 | template0    | postgres   | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +              |            |          |            |            | postgres=CTc/postgres template1    | postgres   | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +              |            |          |            |            | postgres=CTc/postgres
(4 rows)

geneos_db=> SELECT datname AS "Database name" FROM pg_database WHERE not datistemplate; Database name
--------------- postgres geneos_db
(2 rows)

geneos_db=>

What is the current database? Copied

geneos_db=> SELECT current_database(); current_database
------------------ geneos_db
(1 row)

geneos_db=>

Switch to another database Copied

You cannot switch to another database in PostgreSQL without making a new connection as follows

geneos_db=> \c postgres postgres
Password for user postgres:

You are now connected to database "postgres" as user "postgres".
postgres=#

List tables in current database Copied

geneos_db=> \dt                  List of relations Schema |         Name          | Type  |   Owner
--------+-----------------------+-------+------------ public | action_table          | table | geneos_dbo public | active_table          | table | geneos_dbo public | alert_table           | table | geneos_dbo public | attribute_table       | table | geneos_dbo public | audit_details_table   | table | geneos_dbo public | audit_table           | table | geneos_dbo public | event_table           | table | geneos_dbo public | hostname_table        | table | geneos_dbo public | net_table             | table | geneos_dbo public | node_ref_table        | table | geneos_dbo public | os_table              | table | geneos_dbo public | processes_table       | table | geneos_dbo public | snooze_table          | table | geneos_dbo public | text_table            | table | geneos_dbo public | user_assignment_table | table | geneos_dbo public | var_ref_table         | table | geneos_dbo public | version_table         | table | geneos_dbo
(17 rows)

geneos_db=>

Get the details of a table Copied

geneos_db=> \d+ os_table                                       Table "public.os_table"  Column   |       Type       | Collation | Nullable | Default | Storage | Stats target | Description
-----------+------------------+-----------+----------+---------+---------+--------------+------------- timestamp | integer          |           | not null |         | plain   |              | node_ref  | integer          |           | not null |         | plain   |              | var_ref   | integer          |           | not null |         | plain   |              | value     | double precision |           |          |         | plain   |              |
Indexes:    "os_index" btree (node_ref, var_ref, "timestamp")
Foreign-key constraints:    "os_table_node_ref_fkey" FOREIGN KEY (node_ref) REFERENCES node_ref_table(ref)

geneos_db=> SELECT column_name, data_type, character_maximum_length, is_nullable
geneos_db-> FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'os_table'; column_name |    data_type     | character_maximum_length | is_nullable
-------------+------------------+--------------------------+------------- timestamp   | integer          |                          | NO node_ref    | integer          |                          | NO var_ref     | integer          |                          | NO value       | double precision |                          | YES
(4 rows)

geneos_db=> SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'os_table'; indexname |                                       indexdef
-----------+--------------------------------------------------------------------------------------- os_index  | CREATE INDEX os_index ON public.os_table USING btree (node_ref, var_ref, "timestamp")
(1 row)

geneos_db=>

Limit the number of rows returned Copied

geneos_db=> SELECT * FROM os_table LIMIT 5; timestamp  | node_ref | var_ref | value
------------+----------+---------+------- 1566920683 |        1 |       1 | 27.12 1566920703 |        1 |       1 | 17.53 1566920724 |        1 |       1 | 17.39 1566920744 |        1 |       1 | 10.12 1566920764 |        1 |       1 |  7.24
(5 rows)

What access permissions does a user have? Copied

geneos_db=> \du                                     List of roles Role name   |                         Attributes                         |  Member of
-------------+------------------------------------------------------------+-------------- geneos_dbo  | No inheritance, Cannot login                               | {} postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {} geneos_user |                                                            | {geneos_dbo}

geneos_db=>

Handling timestamps Copied

To convert from a date and time to a Unix timestamp use SELECT EXTRACT(EPOCH FROM <time expression>):

geneos_db=> select extract(epoch from timestamp '2019-08-09 09:48:06'); date_part
------------ 1565344086
(1 row)

geneos_db=> select extract(epoch from timezone('UTC', timestamp '2019-08-09 08:48:06')); date_part
------------ 1565340486
(1 row)

geneos_db=>

To convert from a Unix timestamp to a date and time use SELECT to_timestamp(<Unix timestamp>):

geneos_db=> SELECT to_timestamp(1565340486) as date_time_with_offset,
geneos_db->   timezone('UTC', to_timestamp(1565340486)) as utc_date_time,
geneos_db->   timezone('Europe/London', to_timestamp(1565340486)) as local_date_time; date_time_with_offset  |    utc_date_time    |   local_date_time
------------------------+---------------------+--------------------- 2019-08-09 08:48:06+00 | 2019-08-09 08:48:06 | 2019-08-09 09:48:06
(1 row)

geneos_db=>

Housekeeping Copied

This psql function removes all rows from the specified target_table whose timestamp is older than num_days:

CREATE OR REPLACE FUNCTION purge_table(target_table character varying, num_days integer)  RETURNS void  LANGUAGE plpgsql
AS $function$
DECLARE  cutoff integer;
BEGIN  cutoff := EXTRACT(EPOCH FROM (current_timestamp - make_interval(days => num_days)));  EXECUTE FORMAT('DELETE FROM %I WHERE timestamp < $1', target_table) using cutoff;
END $function$

Once a function has been created in the Geneos database it can be executed from a shell script triggered by cron or by a Geneos scheduled command:

#!/bin/sh

HOST=pgsrv.example.com
USER=geneos_user
PASSWORD=geneos_password
DBASE=geneos_db
NUM_DAYS=90
TABLE=event_table

echo "select purge_table('$TABLE', $NUM_DAYS)" | PGPASSWORD=$PASSWORD pqsl -h $HOST --username=$USER --dbname=$DBASE

Sybase Copied

Required Client Libraries Copied

Ensure the Sybase Server 15 client libraries are installed on the host running the gateway and set-up the SYBASE and LD_LIBRARY_PATH in the start_gateway script appropriately. Please see Sybase Software development kit for more details. Software Developer’s Kit 15.7 > Open Client and Open Server Programmers Supplement for UNIX > Open Client Client-Library/C. This is available at http://infocenter.sybase.com/help/index.jsp.

setenv SYBASE /export/share/dev/SYBASE
setenv LD_LIBRARY_PATH $SYBASE/OCS-12_5/lib:$SYBASE/lib:${LD_LIBRARY_PATH}

Interfaces file Copied

Sybase clients rely on the interfaces file to determine where Sybase servers are on the network. Find the interfaces file on the server on which the gateway is running and ensure it contains an entry for the Sybase server you are attempting to connect to.

% pwd
/export/share/dev/SYBASE
% more interfaces
LINUX165
master tli tcp /dev/tcp \x00021388c0a80aa50000000000000000
query tli tcp /dev/tcp \x00021388c0a80aa50000000000000000

ITRSSOL5
master tli tcp /dev/tcp \x00021004c0a80a290000000000000000
query tli tcp /dev/tcp \x00021004c0a80a290000000000000000
:
:

Using isql Copied

isql (Interactive SQL parser to Adaptive Server) is a command line client utility

isql -S<server_name> -U<user_name> -P<password>

The server_name should match an entry in the interfaces file.

Setting up the Geneos database schema Copied

Create a user

1> sp_addlogin geneos_user, geneospasswd
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1>

Create a database

1> create database geneos_db
2> go
CREATE DATABASE: allocating 25600 logical pages (50.0 megabytes) on disk
'bigger_data'.
1>

Switch to the database

1> use geneos_db
2> go

Change the database owner

1> sp_changedbowner geneos_user
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Database owner changed.
(return status = 0)
1>

Load the schema

Assuming the schema is in the file schema_1.n.sql, check the first line of the schema sets the database to the required database in this case geneos_db.

$ more schema_1.n.sql

use geneos_db
go

CREATE TABLE … …

Run in the schema

isql -S LINUX165 -U geneos_user -P geneospasswd < schema_1.n.sql

Useful Commands Copied

Who am I? Copied

1> select user, user_id()
2> go

------------------------------ -----------
geneos_user                              3

(1 row affected)
1>

What databases exist? Copied

1> sp_helpdb
2> go
name                     db_size       owner                    dbid           created         status
------------------------ ------------- ------------------------ ------         --------------  ------------------------------------------------------------------------------------------------------
RJB                            50.0 MB  sa                            6         Nov 29, 2005    mixed log and data
geneos_db                      50.0 MB  sa                           16         Sep 14, 2009    mixed log and data
master                        126.0 MB  sa                            1         Jan 25, 2005    mixed log and data
model                           2.0 MB  sa                            3         Jan 25, 2005    mixed log and data
:
:

What is the current database? Copied

1> select db_name()
2> go

------------------------------
geneos_db

(1 row affected)
1>

What users exist? Copied

To find what users exist in a given database

1> use geneos_db
2> go
1> sp_helpuser
2> go
Users_name        ID_in_db    Group_name        Login_name
----------------- ----------- ----------------- -----------------
dbo                         1 public            geneos_user
(return status = 0)
1>

Switch to another database Copied

1> use geneos_db
2> go
1>

List the tables in the current database Copied

1> select name from sysobjects where type = "U"
2> go
name
------------------------------
version_table
node_ref_table
var_ref_table
event_table
os_table
net_table
processes_table
text_table
audit_table

(9 rows affected)
1>

Get the details of a table Copied

sp_help <table_name> will return full details about a table in the current database, including the columns and indexes.

1> sp_help os_table
2> go
Name                           Owner                           Object_type
------------------------------ ------------------------------  --------------------------------
os_table                       dbo                             user table

(1 row affected)

Data_located_on_segment        When_created
------------------------------ --------------------------
default                               Sep 14 2009 12:24PM

Column_name     Type            Length      Prec Scale Nulls Default_name      Rule_name       Access_Rule_name               Identity
--------------- --------------- ----------- ---- ----- ----- ---------------   --------------- ------------------------------ --------
timestamp       int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
node_ref        int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
var_ref         int                       4 NULL  NULL     0 NULL              NULL            NULL                                  0
value           real                      4 NULL  NULL     0 NULL              NULL            NULL                                  0

index_name           index_description                                         index_keys                                     index_max_rows_per_page index_fillfactor index_reservepagegap    index_created
-------------------- -------------------------------------------------------   ---------------------------------------------- ----------------------- ---------------- --------------------    -------------------
os_index             nonclustered located on default                           node_ref, var_ref, timestamp                                         0                0                    0    Sep 14 2009 12:24PM

(1 row affected)

No defined keys for this object.
Object is not partitioned.
Lock scheme Allpages
The attribute 'exp_row_size' is not applicable to tables with allpages lock scheme.
The attribute 'concurrency_opt_threshold' is not applicable to tables with allpages lock scheme.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
1              0          0                 0            0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
0                     0                   0
(return status = 0)
1>

Nulls - if 1 the column can contain nulls, if 0 then nulls are not allowed.

The default for a database when creating a table is not null, can be changed with sp_dboption.

To just see the details of the indexes on a table use sp_helpindex

1> sp_helpindex var_ref_table
2> go
index_name           index_description                                         index_keys                                      index_max_rows_per_page index_fillfactor index_reservepagegap   index_created
-------------------- --------------------------------------------------------  ----------------------------------------------  ----------------------- ---------------- --------------------   -------------------
var_ref_index        clustered, unique located on default                      varname, tablename                                                    0                0                    0   Sep 14 2009  3:38PM

(1 row affected)
(return status = 0)
1>

On the var_ref_table in the example above, var_ref_index is a unique index consisting of varname and tablename columns.

Count the number of rows in a table

1> select count(*) from event_table
2> go

-----------
10920

(1 row affected)
1>

Limit the number of rows returned Copied

Set rowcount to the number of rows required. This affects all subsequent statements.

1> set rowcount 5
2> go

1> select * from os_table
2> go
timestamp   node_ref    var_ref     value
----------- ----------- ----------- --------------------
1239963328           1           1             0.000000
1239964471           1           1             0.000000
1239964527           1           1             0.000000
1239964585           1           2             5.130000
1239964590           1           2             4.620000

(5 rows affected)
1>

To remove the limit and have all rows returned, set rowcount to zero.

1> set rowcount 0
2> go

More user details Copied

1> sp_displaylogin geneos_user
2> go
Suid: 11
Loginame: geneos_user
Fullname:
Default Database: master
Default Language:
Auto Login Script:
Configured Authorization:
Locked: NO
Date of Last Password Change: Sep 14 2009 11:19AM
Password expiration interval: 0
Password expired: NO
Minimum password length: 6
Maximum failed logins: 0
Current failed login attempts:
Authenticate with: ANY
(return status = 0)
1>

What access permissions does a user have? Copied

To list all the permissions a user has been given within a database, use sp_helprotect specifying the user

1> use geneos_db
2> go
1> sp_helprotect geneos_ro
2> go
grantor         grantee         type     action                        object          column     grantable
--------------- --------------- -------- ----------------------------  --------------- ---------- ---------
dbo             geneos_ro       Grant    Select                        audit_table     All        FALSE
dbo             geneos_ro       Grant    Select                        event_table     All        FALSE
dbo             geneos_ro       Grant    Select                        net_table       All        FALSE
dbo             geneos_ro       Grant    Select                        node_ref_table  All        FALSE
dbo             geneos_ro       Grant    Select                        os_table        All        FALSE
dbo             geneos_ro       Grant    Select                        processes_table All        FALSE
dbo             geneos_ro       Grant    Select                        text_table      All        FALSE
dbo             geneos_ro       Grant    Select                        var_ref_table   All        FALSE
dbo             geneos_ro       Grant    Select                        version_table   All        FALSE
dbo             public          Grant    Select                        sysalternates   All        FALSE
dbo             public          Grant    Select                        sysattributes   All        FALSE
dbo             public          Grant    Select                        sysobjects      ckfirst    FALSE
:
:

To list the permissions which a particular user has been given to a particular table use sp_helprotect specifying the table and user

1> sp_helprotect version_table, geneos_ro
2> go
grantor         grantee         type     action                        object          column     grantable
--------------- --------------- -------- ----------------------------  --------------- ---------- ---------
dbo             geneos_ro       Grant    Select                        version_table   All        FALSE

(1 row affected)
(return status = 0)
1>

Handling Timestamps Copied

To convert from a date and time to a Unix timestamp use

1> select datediff(second,"January 1, 1970 00:00","24 Dec 2009 09:24:10")
2> go

-----------
1261646650

(1 row affected)
1>

To convert from a Unix timestamp to a normal time use

1> select convert(char(26),dateadd (second,1261646650,"January 1 1970 00:00"),109)
2> go

--------------------------
Dec 24 2009  9:24:10:000AM

(1 row affected)
1>

Creating additional Geneos tables Copied

To create additional tables for Geneos data use the following statements substituting appropriately for in table and index sections

For numeric data

CREATE TABLE <name>_table (timestamp int not null,node_ref int not null,var_ref int not null,value real);
go
CREATE INDEX <name>_index on <name>_table (node_ref,var_ref,timestamp)
go

For text data

CREATE TABLE <name>_table(timestamp int not null,node_ref int not null,var_ref int not null,value varchar(250));
go
CREATE INDEX <name>_index on <name>_table (node_ref,var_ref,timestamp)
go

Creating additional read-only users Copied

Creating a read-only user Copied

To create a read-only user, create a user

1> sp_addlogin geneos_ro, geneospasswd
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)
1>

Add the user to the database.

Note
You need to switch to the database first before issuing the sp_adduser command.

1> select user
2> go

------------------------------
dbo

(1 row affected)
1> use geneos_db
2> go
1> sp_adduser geneos_ro
2> go
New user added.
(return status = 0)
1>

As the dbo, give the read-only user access to the tables

1> grant select on version_table to geneos_ro
2> go

To give a user access to all the tables in a database the following approach can be used.

Note
This gives all permissions but all can be replaced by select.

Generate a list of the tables surrounded by appropriate text to return a command:-

1> select "grant all on " + name + " to geneos_user" from sysobjects
2> where type = "U"
3> go

----------------------------------------------------------
grant all on version_table to geneos_user
grant all on node_ref_table to geneos_user
grant all on var_ref_table to geneos_user
grant all on event_table to geneos_user
grant all on os_table to geneos_user
grant all on net_table to geneos_user
grant all on processes_table to geneos_user
grant all on text_table to geneos_user
grant all on audit_table to geneos_user

(9 rows affected)
1>

Then cut and paste the resulting output

1>  grant all on version_table to geneos_user
grant all on node_ref_table to geneos_user
grant all on var_ref_table to geneos_user
grant all on event_table to geneos_user
grant all on os_table to geneos_user
grant all on net_table to geneos_user
grant all on processes_table to geneos_user
grant all on text_table to geneos_user
grant all on audit_table to geneos_user

2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
12> go
1>

Housekeeping Copied

A simple housekeeping script for Sybase. Replace all occurrences of with the appropriate table. The loop and rowcount of 10000 avoids filling the transaction log.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than @num_days                                           */
/*                                                                            */
/******************************************************************************/
drop proc purge_<name>_table
go

create proc purge_<name>_table
(
@num_days int
)
as

declare @archive_tm      int,
@finished        char(1)

select @num_days = @num_days * -1
select @archive_tm = datediff(second,"January 1, 1970 00:00",
convert(char(15),
dateadd(dd, @num_days, getdate()),106))

set rowcount 10000

select @finished = 'N'

while @finished = 'N'
begin

delete
from <name>_table
where timestamp < @archive_tm

if @@rowcount = 0 select @finished = 'Y'

end

go

To load this stored procedure into the database use

isql -S hostname -U username -D database_name -P password < purge_script_name.sql

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines:

#!/bin/sh

DB_SERVER=SYBASE_SERVER
DB_DATABASE=geneos_db
DB_USERNAME=geneos_user
DB_PASSWORD=geneos_passwd

NUM_DAYS=90

isql -S${DB_SERVER} -U${DB_USERNAME} -D${DB_DATABASE} -P${DB_PASSWORD} << EOF
exec  purge_<name>_table  "$NUM_DAYS"
go
EOF

Oracle Copied

Required Client Libraries Copied

Ensure 64-bit Oracle client libraries (libclntsh.so) are installed on the host running the gateway and set-up the ORACLE_HOME and LD_LIBRARY_PATH in the start_gateway script appropriately. Below is an example of the shell commands to set-up the correct environment variables, these may vary depending on the details of your Oracle installation.

ORACLE_HOME=/usr/oracle/oracle/product/11.2.0/client; export ORACLE_HOME
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

tnsnames.ora file Copied

Oracle clients use the tnsnames.ora file to determine where Oracle servers are on the network.

Find the tnsnames.ora file on the server on which the gateway is running and ensure it contains an entry for the Oracle database you are attempting to connect to.

EUMON01TCP, EUMON01,EUMON01.itrspc106.ldn.itrs =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP) (HOST = itrspc106.ldn.itrs) (PORT = 1521)
)
(CONNECT_DATA =
(SID = EUMON01)
)
)

Using sqlplus Copied

sqlplus is a command line client utility.

Before trying to use sqlplus you need to set-up ORACLE_HOME, e.g.

setenv ORACLE_HOME /usr/oracle/oracle/product/11.2.0/client

sqlplus <user_name>/<password>@<database_name>sqlplus geneos_user/geneos_passwd@db_name

To set the line length and page length within sqlplus to a sensible values -

SQL> set linesize 132
SQL> set pagesize 60

Setting up the Geneos database schema Copied

Create a user

SQL> create user geneos_user identified by geneos_passwd default tablespace USERS temporary tablespace TEMP;

User created.

SQL>

Give the user appropriate access, connect and dba

SQL> grant connect, dba to geneos_user;

Grant succeeded.

SQL>

Load the schema, assuming the schema is in the file schema_1.n.sql

sqlplus geneos_user/geneos_passwd@TESTDB @schema_1.n.sql

Useful Commands Copied

Who am I? Copied

SQL> select user, uid from dual;

USER                                  UID
------------------------------ ----------
geneos_user                            58

SQL>

SQL> show user;
USER is "geneos_user"
SQL>

What users exist? Copied

SQL> select username, default_tablespace, temporary_tablespace from dba_users order by username;

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
ANONYMOUS                      SYSAUX                         TEMP
CJT                            USERS                          TEMP
CTHOUMINE                      USERS                          TEMP
CTXSYS                         SYSAUX                         TEMP
DBSNMP                         SYSAUX                         TEMP

List the tables in the current database Copied

SQL> select table_name from tabs;

TABLE_NAME
------------------------------
NODE_REF_TABLE
VERSION_TABLE
VAR_REF_TABLE
EVENT_TABLE
OS_TABLE
NET_TABLE
PROCESSES_TABLE
TEXT_TABLE

8 rows selected.

SQL>

select * from user_objects where object_type = 'TABLE';

Get the details of a table Copied

A list of the columns

SQL> desc OS_TABLE
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TIMESTAMP                                 NOT NULL NUMBER(38)
NODE_REF                                  NOT NULL NUMBER(38)
VAR_REF                                   NOT NULL NUMBER(38)
VALUE                                              FLOAT(63)

SQL>

A list of the indexes on the table

SQL> select distinct(INDEX_NAME) from all_ind_columns where table_name = 'OS_TABLE' and index_owner = 'geneos_user';

INDEX_NAME
------------------------------
OS_INDEX

SQL>

Full details of an index

SQL> select * from all_ind_columns where table_name = 'OS_TABLE' and index_name='OS_INDEX' and index_owner='CTHOUMINE';

INDEX_OWNER                    INDEX_NAME                TABLE_OWNER                    TABLE_NAME                      COLUMN_NAME                     COLUMN_POSITION COLUMN_LENGTH   CHAR_LENGTH     DESC
------------------------------ ------------------------- ------------------------------ ------------------------------  ------------------------------  --------------- -------------   -----------     ----
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      TIMESTAMP                       1               22              0               ASC
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      NODE_REF                        2               22              0               ASC
CTHOUMINE                      OS_INDEX                    CTHOUMINE                      OS_TABLE                      VAR_REF                         3               22              0               ASC

SQL>

Count the number of rows in a table

SQL> select count(*) from event_table;

COUNT(*)
----------
8977

SQL>

Limiting the number of rows returned Copied

Add rownum <= <num_rows_required> to the where clause

SQL> select * from os_table where rownum <= 4;

TIMESTAMP   NODE_REF    VAR_REF      VALUE
---------- ---------- ---------- ----------
1251806387          1          1         11
1251806450          1          1         10
1251807151          1          1         11
1251807181          1          1         10

SQL>

What access permissions does a user have? Copied

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
ALTER SESSION
CREATE TABLESPACE
:
:

Handling Timestamps Copied

To convert from a date and time to a Unix timestamp use

SQL> SELECT (to_date('24-dec-2009 09:47:08','dd-mon-yyyy hh24:mi:ss') - to_date('01-JAN-1970')) * (86400) AS dt FROM dual;

DT
----------
1261648028

SQL>

To convert from a Unix timestamp to a normal time use

SQL> select to_char(to_date('01-jan-1970') + 1261648028 / 86400,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(TO_DATE('01-
-------------------
24-dec-2009 09:47:08

SQL>

Creating additional Geneos tables Copied

To create additional tables for Geneos data use the following statements substituting appropriately for .

For numeric data

CREATE TABLE <name>_table (timestamp int not null, node_ref int not null, var_ref int not null, value real null) ;
CREATE INDEX <name>_index on <name>_table (node_ref, var_ref, timestamp) ;
ALTER TABLE <name>_table add constraint fk_<name>_table_node_ref  FOREIGN KEY (node_ref) REFERENCES node_ref_table (ref);
ALTER TABLE <name>_table add constraint fk_<name>_table_var_ref  FOREIGN KEY (var_ref) REFERENCES var_ref_table (ref);

For text data

CREATE TABLE <name>_table (timestamp int not null, node_ref int not null, var_ref int not null, value varchar(250)) ;
CREATE INDEX <name>_index on <name>_table (node_ref, var_ref, timestamp) ;
ALTER TABLE <name>_table add constraint fk_<name>_table_node_ref  FOREIGN KEY (node_ref) REFERENCES node_ref_table (ref);
ALTER TABLE <name>_table add constraint fk_<name>_table_var_ref  FOREIGN KEY (var_ref) REFERENCES var_ref_table (ref);

Creating additional read-only users Copied

Creating a read-only user Copied

To create a read-only user, create a user

SQL> create user geneos_ro identified by geneos_ro_passwd default tablespace USERS temporary tablespace TEMP;

User created.

SQL>

and give the user connect access,

SQL> grant connect to geneos_ro;

Grant succeeded.

SQL>

For user2 to access the tables of user1 you must have dba privileges and give user2 select access to the table of user1 and then create a synonym

grant select on "user1."table_name" to "user2";
create synonym "user2"."table_name" for "user1"."table_name"

e.g.

SQL> grant select on geneos_user.event_table to geneos_ro;

Grant succeeded.

SQL> create synonym geneos_ro.event_table for geneos_user.event_table;

Synonym created.

Listing synonyms Copied

To list synonyms

SQL> select OWNER,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from all_synonyms where owner = 'GENEOS_RO';

OWNER               SYNONYM_NAME                 TABLE_OWNER                    TABLE_NAME
------------------- ---------------------------- ---------------------------- --------------------
GENEOS_RO           EVENT_TABLE                  GENEOS_USER                  EVENT_TABLE
GENEOS_RO           NET_TABLE                    GENEOS_USER                  NET_TABLE
GENEOS_RO           NODE_REF_TABLE               GENEOS_USER                  NODE_REF_TABLE
GENEOS_RO           OS_TABLE                     GENEOS_USER                  OS_TABLE
GENEOS_RO           SOURCE_TABLE                 GENEOS_USER                  SOURCE_TABLE
GENEOS_RO           TEXT_TABLE                   GENEOS_USER                  TEXT_TABLE
GENEOS_RO           VAR_REF_TABLE                GENEOS_USER                  VAR_REF_TABLE
GENEOS_RO           VERSION_TABLE                GENEOS_USER                  VERSION_TABLE

Housekeeping Copied

A simple housekeeping script for Oracle. Replace all occurrences of with the appropriate table.

/******************************************************************************/
/*                                                                            */
/* Purging the <name>_table, nothing subtle, just remove all records with a   */
/* timestamp earlier than num_days                                            */
/*                                                                            */
/******************************************************************************/

CREATE OR REPLACE PROCEDURE purge_<name>_table(num_days IN NUMBER)
IS
BEGIN
delete
from <name>_table
where timestamp < (trunc(sysdate) - num_days - to_date('01-JAN-1970','DD-MON-YYYY')) * 86400;
END;

To load this stored procedure into the database use

sqlplus user@schema@TESTDB @purge_script_name.sql

and once this has loaded into sqlplus enter /

for example

sqlplus cthoumine/cthoumine@TESTDB @ purge_<name>_table.sql
SQL*Plus: Release 11.2.0.1.0 - Production on Tue Dec 22 15:18:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

8  /

Procedure created.

SQL>

Once the stored procedure has been loaded into the database it can easily be called from a simple shell script triggered by cron and containing the following lines

#!/bin/sh

ORACLE_HOME=/usr/oracle/oracle/product/11.2.0/client
export ORACLE_HOME

USER=geneos_user
SCHEMA=geneos_user
DBASE=TESTDB

NUM_DAYS=90

/usr/oracle/oracle/product/11.2.0/client/bin/sqlplus ${USER}/${SCHEMA}@${DBASE} << EOF
exec  purge_<name>_table($NUM_DAYS);
EOF

SQL Server Copied

Required Client Libraries Copied

See MS SQL Server Database Setup using native client library - used by 64-bit Linux Gateway in the Database Logging Guide.

Setting up the database Copied

Create a user (Windows authenticated) Copied

Within Microsoft SQL Server Management Studio, open up the Security folder, and the Logins folder within it. Right click the Logins folder and choose New Login

databases1

Use a valid Windows NT application / user account for user credentials and enter the appropriate permissions.

databases2

databases3

databases4

Create a user (SQL Server authentication) Copied

Within Microsoft SQL Server Management Studio, open up the Security folder, and the Logins folder within it. Right click the Logins folder and choose New Login

In the General tab enter a login name and assign a password to it.

databases5

Accept the defaults on other tabs as seen in previous slides.

Create a database Copied

Right click the Database folder and choose New Database. Give the database a name and assign the database an owner.

databases6

Assign Collation to be Latin1_General_CI_AS. Keep other settings as default.

databases7

Create tables / schema Copied

Choose New Query option

databases8

Within the Gateway’s installation folder navigate to the directory resources/databases/sqlServer. Load the contents of the schema-<version_no>.sql file into the query window.

The script assumes geneos_db is the database. You will need to change this to name it otherwise.

Click on Execute

databases10

Right click the GENEOS database and choose Refresh and tables are now listed

databases11

Housekeeping Copied

A simple housekeeping script for SQL Server. Replace all occurrences of with the appropriate table.

/******************************************************************************/
/*
/* Purging the <name>_table, nothing subtle, just remove all records with a
/* timestamp earlier than num_days
/*
/******************************************************************************/

use geneos_db
GO
CREATE PROCEDURE dbo.purge_<name>_table
@num_days int
AS
Delete
from <name>_table
where @num_days < DATEDIFF(day,DATEADD(S,timestamp,'1970-01-01'),GETDATE())
GO

Below is a batch script to trigger the procedure:

set HOST=nysupsvr08
set USER=username
set PASSWORD=password
set DBASE=geneos_db
set NUM_DAYS=15

sqlcmd -S %HOST% -d %DBASE% -U %USER% -P %PASSWORD% -Q "Execute  purge_<name>_table %NUM_DAYS%"

Other Copied

Renaming Gateways and the database Copied

If the name of a gateway is changed, when the gateway is restarted it will fail to find the existing entries in the node_ref_table as these are linked to the old gateway name and as a result will create an entirely new set of node_refs. As a result whilst the historical information held in the database still exists it will not be available via the ActiveConsole and is effectively lost.

In order to avoid this, if changing the gateway name, the following procedure should be followed:-

Stop the gateway
Edit the gateway.setup.xml and change the gateway name

<gatewayName>old_gateway_name</gatewayName>

<gatewayName>new_gateway_name</gatewayName>

Change the gateway name in the node_ref_table

update node_ref_table
set gateway = "new_gateway_name"
where gateway = "old_gateway_name"

Restart the gateway

Renaming Managed Entities and the database Copied

The name of a Managed Entity is stored in the node_ref_table as the node, a unique node_ref (number) is then assigned to this node and gateway combination. In other database tables the Managed Entity or node is referred to by this node_ref.

If the Managed Entity name is changed the gateway will not find the name of the managed entity in the node_ref_table and will automatically create a new row for the node / gateway combination and allocate it a new node_ref and will then store new information relating to the Managed Entity under the new node_ref. As a result the historic information held with-in the database for that Managed Entity will not be available via the ActiveConsole and is effectively lost.

In order to avoid this, if changing the name of a managed entity, it is necessary to perform the following procedure:

Stop the gateway
Rename the netprobe in the gateway.setup.xml
Change the node name in the node_ref_table

update node_ref_table
set node = "new_node_name"
where node = "old_node_name" and
gateway = "gateway_name"

Restart the gateway

Gateway Database Schema Copied

The EER diagrams below are created using tools on MySQL. There might be slight differences on different databases in terms of variable types and sizes.

The links between tables shown are logical links. Foreign key constraints are not enforced by any of the Geneos database schema.

Schema 1.0 Copied

databases12

Schema 1.1 Copied

databases13

Schema 1.2 Copied

databases14

Schema 1.3 Copied

databases15

Schema 1.4 Copied

databases16

Schema 1.5 Copied

databases17

Schema 1.6 Copied

schema1_6

Schema 1.7 Copied

schema1_7

Schema 1.8 Copied

There is also an optional schema provided for those using time series. This needs to be added in addition to the normal Geneos schema in the same database.

Time series optional tables Copied

databases18