Geneos ["Geneos"]
["Geneos > Netprobe"]["Technical Reference"]

SQL Toolkit configuration

Introduction

The SQL Toolkit plugin executes database queries that can run and display the output of the query in the dataview.

This plugin can connect to and extract data from the following supported databases:

  • MySQL or MariaDB
  • Microsoft SQL Server
  • Oracle
  • SAP ASE, formerly known as Sybase ASE.
  • PostgreSQL
  • IBM Db2

For more information, see SQL Toolkit.

This is a sample query that is used in SQL Toolkit plugin:

select distinct
        from_unixtime(timestamp) as 'Date',
        varname as 'Managed Variable',
        description
        from event_table
        where unix_timestamp(now()) - timestamp <= 3600
        and severity >= 2
        order by Date asc

Note: There is no need for a semicolon at the end of the query.

The data returned by the query is converted into a set of regular Geneos Managed Variables. All the standard Geneos Enterprise Management Framework functionality (e.g. setting rules, logging to a database) applies.

Note: The order of the rows published in the dataview may not always adhere to the order returned by the query (for example, if "ORDER BY" is used in the query). This is due to performance considerations.

Plugin Configuration

Basic tab

Basic Configuration Description
Queries

Lists a set of queries that will be used by the plugin to the database.

Each query should have a name and an SQL query code.

To view the list of data types that SQL Toolkit plugin does not support, see Manage unsupported data types in SQL Toolkit.

Mandatory: Yes

Queries > Name

Name of the query.

For example,

Query1

Mandatory: Yes

Queries > Sql

Holds the SQL used to run the query.

Caution: You should only enter SQL SELECT statements that return output in table format. Otherwise, if you try to use other types of SQL statements, this plugin may fail and display the error message: Fail - Failed to get rows as Query was probably INSERT, DELETE, UPDATE or procedure call(0).

For example:

select * from my_table

Mandatory: Yes

Queries > Row limit

Limits the number of rows to be returned by a query.

Note: Netprobe is capable of handling multiple rows. However, setting a larger value for this option may overload the Netprobe and the database server.

Mandatory: No

Default: 2000 rows

Connection

Defines all of the settings that need to be passed to the database.

The following are supported databases that you can connect to:

Note: The Netprobe has a default of 10 maximum database connections. This number is configurable in the GSE in Probes > Advanced > Max database connections.

Connection > User Name

Name of the user with which to connect to the database.

Mandatory: No

Connection > Password

The password with which to connect to the database. To set an encrypted password, click on the "Set Password" button, then enter and confirm the password to be used.

Mandatory: No

Connection > Close connection after query

If set to true the sampler will close the connection to the database after each query. This frees resources for other processes that may connect to the database but makes the sampler a little less efficient. If false the connection remains open but is returned to a pool of connections for reuse within the netprobe.

When using a large sample interval (e.g. running queries every hour), setting this parameter to true will reduce database resources without significantly impacting Netprobe performance.

Mandatory: No

Default: false

   

Connect to a Db2 database

Field Description
Database Name

Alias of the database to use when running the query.

Mandatory: Yes

   
Sample dataview

Connect to a MySQL database

Field Description
Server Name

Name of the MySQL server.

Mandatory: Yes

Database Name

Name of the database to use when running the query.

Mandatory: Yes

Port

Port number that MySQL is listening on.

Mandatory: No

Default: 3306

SSL Configuration > SSL Mode

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 unsecured 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

SSL Configuration > CA Certificate

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

Mandatory: No

SSL Configuration > Client Certificate

Path to the client's public key certificate file.

Mandatory: No

SSL Configuration > Client Key

Path to the client's private key file.

Mandatory: No

SSL Configuration > Cipher suites

Specifies the list of allowed ciphers for secure connections.

Mandatory: No

   
Sample dataview

Connect to an Oracle database

Field Description
Database Name

Name of the database to use when running the query. This is specified in the tnsnames.ora file.

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

  • tnsnames.ora is available
  • tnsnames.ora is available
  • tnsnames.ora is available

If tnsnames.ora is available, doing so will override the details of the file in your 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 Netprobe 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 Netprobe in use.

To configure an Oracle SQL Toolkit using connection string, copy and enter the sample below in the Database Name field:

Sample connection string: (DESCRIPTION =   (
ADDRESS = (PROTOCOL = TCP)
HOST = 192.168.100.87)(PORT = 1521))
(CONNECT_DATA =   (SERVER = DEDICATED)       
(SERVICE_NAME = geneosdb.mnl.itrsgroup.com)     )   )				

You can use the connection string instead of using the actual database name. This means that when the database name changes, you can still connect provided that the connection string remains intact.

Note: If you encounter the SQL-TOOLKIT Fail - OCI_INVALID_HANDLE(0) error while configuring the Oracle database, ensure to define and export the ORACLE_HOME and LD_LIBRARY_PATH varia

Mandatory: Yes

Application Name

Application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

   
Sample dataview

Connect to a PostgreSQL database

Notes: 
  • PostgreSQL is not supported in IBM AIX platforms.
  • PostgreSQL connections configured with SSL or TLS are not supported in SUSE 12 platforms.
Field Description
Server Name

Name of the PostgreSQL server.

Mandatory: Yes

Database Name

Name of the PostgreSQL database to use when running the query.

Mandatory: Yes

Port

Port number of the PostgreSQL server.

Mandatory: Yes

Application Name

Application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

SSL Configuration > SSL Mode

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

SSL Configuration > Client Certificate

Path to the client's public key certificate file.

Mandatory: No

SSL Configuration > Client Key

Path to the client's private key file.

Mandatory: No

   
Sample dataview

Connect to an SQL Server database

Field Description
Server Name

Name of the SQL Server.

When connecting to named instances the format should be ServerName\InstanceName. The format ServerName can be accepted if there is only once instance running on the database's host. On connection, Geneos will verify if the named instance actually matches the one listening on the specified port.

Note: Dynamic MS SQL instances are not supported.

Mandatory: Yes

Database Name

Name of the database to use when running the query if the database name is not specified in the query. If the query contains a valid database name, this value will be ignored.

Mandatory: No

Port

Port number that MySQL is listening on.

Mandatory: No

Default: 1433

MultiSubnetFailover

Allows you to enable or disable the MultiSubnetFailover connection option of your SQL Server.

Mandatory: No

Default: false

Application Name

Application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

Note: This option is only valid for Windows platforms.

Use Windows authentication

Note: Beginning Geneos 5.6.x, this plugin supports Windows authentication for Linux platforms. For more information, see Active Directory: Using Kerberos Keytabs to integrate non-Windows systems.

Login using Windows authentication instead of ASE authentication. User accounts used in Windows authentication should have a SeInteractiveLogonRight right.

The credentials used for Windows authentication are the username and password provided in the fields. On Windows, if the username and password fields are left blank, then the currently logged-on user executing the probe will be used. On Linux, if the username and password fields are left blank and useWindowsAuthentication is set to true, then the Kerberos Principal and Keytab defined in the Advanced tab will be used.

You can specify a domain by entering the username using the UPN format or the down-level logon format. If invalid Windows credentials are supplied, then the sampler will fall back to SQL authentication.

Mandatory: No
Default: false
SSL Configuration > Encryption option

Specifies whether the data should be encrypted before sending it over the network. The following are the possible values:

  • Optional — new term which previously means no to SSL encryption in ODBC 17 and below, and OLE DB 18 and below.

  • Mandatory — new term which previously means yes to SSL encryption in ODBC 17 and below, and OLE DB 18 and below.

  • Strict — Server certificate is checked instead of the Trust server certificate. The data sent between the client and the server is encrypted.

Default: Optional

SSL Configuration > Encryption option > Optional > Trust server certificate Enables encryption using a self-signed server certificate.
SSL Configuration > Encryption option > Optional > Hostname in certificate

Specifies the hostname to be expected in the server's certificate when encryption is negotiated, if the hostname is different from the default value derived from Addr/Address/Server.

This setting is Ignored when TrustServerCertificate is true.

SSL Configuration > Encryption option > Mandatory > Trust server certificate Enables encryption using a self-signed server certificate.
SSL Configuration > Encryption option > Mandatory > Hostname in certificate

Specifies the hostname to be expected in the server's certificate when encryption is negotiated, if the hostname is different from the default value derived from Addr/Address/Server.

This setting is Ignored when TrustServerCertificate is true.

SSL Configuration > Encryption option > Strict > Server certificate Specifies the path to a certificate file to check if an exact match against the SQL Server TLS/SSL certificate.
SSL Configuration > Encryption option > Strict > Hostname in certificate

Specifies the hostname to be expected in the server's certificate when encryption is negotiated, if the hostname is different from the default value derived from Addr/Address/Server.

This setting is Ignored when ServerCertificate is true.

   
Sample dataview

Connect to a Sybase database

Field Description
Interface Entry

Name of the Sybase interface. This is specified in the sql.ini or interfaces file.

Note: The Sybase database connection details are specified in the interfaces file.
This is a text file included in SAP ASE installation which contains the connection details of SAP ASE Database, including its host name and port.

Mandatory: Yes

Database Name

Name of the database to use when running the query.

Mandatory: Yes

Application Name

Application name to be set to the connection created from the sampler to the Database Server. The value should be the same in the ocs.cfg file.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

Use Windows authentication

Login using Windows authentication instead of ASE authentication. User accounts used in Windows authentication should have a SeInteractiveLogonRight right.

The credentials used for Windows authentication are the username and password provided in the fields. On Windows, if the username and password fields are left blank, then the currently logged-on user executing the probe will be used. On Linux, if the username and password fields are left blank and useWindowsAuthentication is set to true, then the Kerberos Principal and Keytab defined in the Advanced tab will be used.

You can specify a domain by entering the username using the UPN format or the down-level logon format. If invalid Windows credentials are supplied, then the sampler will fall back to SQL authentication.

Mandatory: No
Default: false
   
Sample dataview

Advanced tab

Advanced Configuration Description
Show headlines

Headlines showing the rows returned, query status and query time taken should be displayed. The following are the possible settings:

  • true — Headlines should be displayed.

  • false — Headlines should not be displayed.

Mandatory: No

It is generally not useful to set the plugin sample interval to a very small value, as most queries take some time to evaluate (if you are interested in real-time plots of data, the Active Chart feature of ActiveConsole may be more suitable). If the system attempts to sample before the current query has returned, the plugin will ignore the sample request.

Show row line

Each row should be numbered to indicate its place relative to the other rows. The following are the possible settings:

  • true — Row numbers should be displayed in the dataview.

  • false — Row numbers should not be displayed in the dataview.

Mandatory: No

Default: false

Query timeout

Number of seconds that a query will run before it is cancelled.

If the Query timeout is set and the timeout is reached before the query is finished, then the query will be cancelled and a Fail status will be displayed in the dataview.

Mandatory: No

Default: disabled

Kerberos

Note: Beginning Geneos 5.6.x, this plugin supports Windows authentication for Linux platforms. For more information, see Active Directory: Using Kerberos Keytabs to integrate non-Windows systems.

If you are running the Netprobe as a service on Windows, then you need to perform some additional configuration on the system for Kerberos authentication to work, please see Additional steps for Windows Netprobe service.

Mandatory: No

Kerberos > Principal

Unique identity to which Kerberos can assign tickets. This is the user requesting access to the SQL Server database.

If you are running the Netprobe as a service on Windows, then you need to perform some additional configuration on the system for Kerberos authentication to work. For more information, see Additional steps for Windows Netprobe service.

Mandatory: No

Kerberos > Key Tab

File path to the keytab for the specified principal.

If you are running the Netprobe as a service on Windows, then you need to perform some additional configuration on the system for Kerberos authentication to work. For more information, see Additional steps for Windows Netprobe service.

Mandatory: No

   
Additional steps for Windows Netprobe service

If you are running the Netprobe as a service on Windows, then you need to perform some additional configuration on the system for Kerberos authentication to work:

  1. Open the Services app. To find it, press ⊞ Win and type services.

  2. In the Services app, look for NetprobeNT_64, or the service name you assigned to the Netprobe.

  3. Right-click the Netprobe service and select Properties.

  4. In the Properties window, select the Log On tab.

  5. Under This account: enter the Kerberos credentials, then click OK:

    Windows Netprobe service log on properties

  6. Restart the Netprobe service by right-clicking the service and selecting Restart.

Enabling diagnostics

To help track down problems, the printing of debug statements can be enabled by adding a debug setting to the sampler set-up called SQL-TOOLKIT. This results in the plugin printing statements each time it attempts something of significance e.g. attempting to open a connection to a database, being unable to connect to a database, etc.

Sample Queries

Oracle

QUERY SQL*PLUS Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name FROM dual;
SELECT func_name FROM dual
Function call with input parameters
SELECT func_name('str', 1)
FROM dual;
SELECT func_name('str', 1) FROM dual
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
exec proc_name;
proc_name
BEGIN proc_name; END;
Procedure call with input parameters
exec proc_name('str', 1);

BEGIN proc_name ('str', 1); END;
BEGIN proc_name ('str', 1); END;
Procedure call with output parameter
DECLARE num NUMBER;
BEGIN
proc1(num);  -- as output
proc2(num);  -- as input
END;
DECLARE num NUMBER;
BEGIN
proc1(num);
proc2(num);
END;
     

Note: Cursor return type is only supported in Oracle.

Sybase

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name
GO
SELECT * FROM tbl_name
Function call without parameters
SELECT dbo.func_name()
GO
SELECT dbo.func_name()
Function call with input parameters
SELECT dbo.func_name('str',1)
GO
SELECT dbo.func_name('str',1)
SELECT dbo.func_name(col1, col2)
FROM tbl_name
GO
SELECT dbo.func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
EXECUTE proc_name
GO
EXECUTE proc_name

Note

If a procudure is returning multiple resultsets, only the first resultset will be displayed in the dataview.

Procedure call with input parameters
EXECUTE proc_name 'str',1
GO
EXECUTE proc_name 'str',1
BEGIN proc_name 'str', 1 END
GO
BEGIN proc_name 'str', 1 END
Procedure call with output parameter
DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN
DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN
     

MySQL

QUERY MYSQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name FROM dual;
SELECT func_name FROM dual
SELECT func_name;
SELECT func_name

Note

Function calls without table reference can be called with or without 'FROM dual'

Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
CALL proc_name;
CALL proc_name

Note

If a procudure is returning multiple resultsets, only the first resultset will be displayed in the dataview.

Procedure call with input parameters
CALL proc_name('str',1);
CALL proc_name('str',1)
Procedure call with output parameter
EXECUTE proc1(@num);  --as output
EXECUTE proc2(@num);  --as input
EXECUTE proc1(@num);
EXECUTE proc2(@num)
     

DB2

QUERY DB2 Netprobe
Simple SELECT query
SELECT * FROM tbl_name
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT *
FROM TABLE(func_name)
SELECT *
FROM TABLE(func_name)
Function call with input parameters
SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1
SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1
SELECT func_name(col1, col2)
FROM tbl_name
SELECT func_name(col1, col2)
FROM tbl_name
SELECT *
FROM TABLE(func_name('str', 1))
SELECT *
FROM TABLE(func_name('str', 1))
Procedure call without parameters
CALL proc_name
CALL proc_name
Procedure call with input parameters
CALL proc_name('str',1)
CALL proc_name('str',1)
Procedure call with output parameter
BEGIN
DECLARE num SMALLINT;
CALL proc1(num); -- as output
CALL proc2(num); -- as input
END
BEGIN
DECLARE num SMALLINT;
CALL proc1(num);
CALL proc2(num);
END
     

MS SQL Server

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name;
SELECT func_name
SELECT *
FROM func_name;
SELECT *
FROM func_name
Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Procedure call without parameters
EXEC proc_name;
EXEC proc_name
Procedure call with input parameters
EXEC proc_name 'str',1;
EXEC proc_name 'str',1
Procedure call with output parameter
DECLARE @lastRw TINYINT;
EXEC get_max_ @lastRw OUTPUT;
EXEC insertNextRow @lastRw;
DECLARE @lastRw TINYINT
EXEC get_max_ @lastRw OUTPUT
EXEC insertNextRow @lastRw
     

Note: When calling functions/procedures in MS SQL Server, [EXEC ] can be omitted or replaced with [EXECECUTE ].

PostgreSQL

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name();
SELECT func_name()
SELECT *
FROM func_name();
SELECT *
FROM func_name()
Function call with input parameters
SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)
SELECT func_name(col1, col2)
FROM tbl_name;
SELECT func_name(col1, col2)
FROM tbl_name
Function call with output parameter

Note: Output parameters are not included in the function call.

SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)