SQL Toolkit configuration
Introduction Copied
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 Copied
Basic tab Copied
| 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, 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: 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: |
| 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 Copied
Sample dataview Copied
Connect to a MySQL database Copied
Sample dataview Copied
Connect to an Oracle database Copied
Connect to an Oracle database with Kerberos authentication Copied
- Login via SSH as a user that is a part of the Active Directory.
- Run the
kinitcommand. - Configure an Oracle SQL Toolkit sampler without setting the username and the password.
Sample dataview Copied
Connect to a PostgreSQL database Copied
Note
- PostgreSQL is not supported in IBM AIX platforms.
- PostgreSQL connections configured with SSL or TLS are not supported in SUSE 12 platforms.
Sample dataview Copied
Connect to a SQL Server database Copied
| Field | Description |
|---|---|
| Server Name |
Name of the SQL Server. When connecting to named instances
the format should be 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: |
| MultiSubnetFailover |
Allows you to enable or disable the Mandatory: No Default: |
| 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 The credentials used for authentication are the
username and password provided in the fields. On , if the
username and password fields are left blank, then the
currently logged-on user executing the probe will be
used. On , if the username and password fields are left blank and useWindowsAuthentication is set to You can specify a domain by entering the username using the UPN format or the down-level logon format. If invalid credentials are supplied, then the sampler will fall back to SQL authentication. Mandatory: No Default: |
| SSL Configuration > Encryption option |
Specifies whether the data should be encrypted before sending it over the network. The following are the possible values:
Default: |
| SSL Configuration > Encryption option > Optional/Mandatory > Trust server certificate |
Enabling or disabling the Trust Server Certificate option affects how the Netprobe handles the server's SSL certificate during the connection process:
|
| SSL Configuration > Encryption option > Optional/Mandatory > Hostname in certificate |
Specifies the expected hostname in the server's certificate during encryption negotiation if it's different from the default value derived from This setting is ignored when TrustServerCertificate is |
| 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 |
pecifies the expected hostname in the server's certificate during encryption negotiation if it's different from the default value derived from This setting is Ignored when ServerCertificate is specified. |
Sample dataview Copied
Connect to a Sybase database Copied
Sample dataview Copied
Advanced tab Copied
| 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:
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:
Mandatory: No Default: |
| 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 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 Copied
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:
- Open the Services app. To find it, press
⊞ Winand typeservices. - In the Services app, look for
NetprobeNT_64, or the service name you assigned to the Netprobe. - Right-click the Netprobe service and select Properties.
- In the Properties window, select the Log On tab.
- Under This account: enter the Kerberos credentials, then click OK:
- Restart the Netprobe service by right-clicking the service and selecting Restart.
Enabling diagnostics Copied
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 Copied
Oracle Copied
| 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;
|
| 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 Copied
| 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
|
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 Copied
| 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;
|
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 Copied
| 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 Copied
| 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 Copied
| 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)
|