Overview
This extension is deprecated and will be removed in 2024. Please migrate to the extension framework 2.0 method, described here.
This Activegate custom extension allows you to run queries against Oracle, MSSQL, MySQL, DB2, PostgreSQL, Informix or SAP HANA databases and send the results back to Dynatrace as metrics.
Use cases
You can use this extension to bring in business data, job processing metrics, database and table statistics, anything that is stored in your Databases.
Example queries
SELECT COUNT(*) FROM DAILY_PROCESSED_JOBSSELECT DEPARTMENT, EMPLOYEE_COUNT FROM DEPARTMENTS
Compatibility information
This extension utilizes JDBC to connect to the different databases, here are the versions of the JDBC drivers we utilize, please refer to the JDBC driver documentation regarding the database version they support.
- mssql-jdbc-9.4.0.jre8.jar
- mysql-connector-java-8.0.30.jar
- ojdbc11.jar
- postgresql-42.3.3.jar
- jdbc-4.50.7.1.jar
- db2jcc4.jar
- jt400-20.0.6-java11.jar
- ngdbc-2.14.7.jar NOTE: This must be downloaded and deployed manually. See Installation.
Installation
Requirements
- Linux or Windows Environment Activegate
- Network access from the Activegate machine to the target database
- A username and password that can access the database and run the queries you configure
- Note: Windows Authentication is not supported at the moment
- For SAP HANA, the jar file must be downloaded from here : ngdbc-2.14.7.jar
Installation
There are two steps to install an Activegate extension:
-
Environment Activegate server - Extract the extension zip file to the plugin_deployment folder of the remote plugin module
- In a default installation this is done with
unzip -o -d /opt/dynatrace/remotepluginmodule/plugin_deployment custom.remote.python.dbquery.zip
- Adjust the path if the Activegate was installed somewhere other than
/opt/dynatrace
-
(Optional) For SAP HANA, place the ngdbc-2.14.7.jar file in the ../plugin_deployment/custom.remote.python.dbquery/jars/ directory.
-
Browser - Upload custom.remote.python.dbquery.zip file to your tenant
Settings > Monitored technologies > Custom extensions > Upload extension
Configuration
Configuration
Create an Endpoint for every database you would like to query.
This is done in Settings > Monitored technologies > Custom extensions > Generic DB Query Plugin
You can run up to 10 queries at a time, to run more than 10 queries, create more endpoints for the same database.
The parameters are:
-
Dynatrace API Token
- The API Token, it needs
Api V2 - Metric Ingest permissions
-
Group Name
- A friendly name to group these queries
-
Custom device name
- A friendly Custom Device name to attach the metrics to, used for alerting and dashboarding purposes
-
Log level
-
Send statistics
- Send statistics to a Custom device, such as query state, duration, rows (leave this enabled during testing)
-
Database Type
- Oracle, MSSQL, MySQL, DB2, PostgreSQL and Informix are supported
-
Hostname
-
Port
-
Username
-
Password
-
Oracle listener type
- Service Name or SID, only used if the database type is Oracle
-
Database
- The database name. In case of Oracle, this is the SID or Service Name
-
(Optional) JDBC - Connection String
- Sometimes you need extra options when connecting to a database, examples:
- Setting the intent for a MSSQL connection:
jdbc:sqlserver://10.1.2.3:1433;databaseName=master;applicationIntent=ReadOnly
- Enabling SSL for a DB2 connection:
jdbc:db2://myserver:5055/my_db:sslConnection=true;
- Do NOT add the username and password to the connection string
- When you use the connection string, the hostname, port and database parameters are ignored
-
(Optional) Java path
- A path to a Java binary (must be java 11 or superior) to be used, if left empty it will use the ActiveGate JVM
-
(Optional) Java extra arguments
- Extra JVM arguments, example (enter one argument per line): -Xmx60m
-
Queries Timeout
-
Query (1-10) - Name
- The name of this metric, if this is empty this query will be ignored
-
Query (1-10) - Query string
- The query text
- This can be a simple query like
SELECT COUNT(*) FROM JOBS, where a single number is returned
- Or a more complex query, like
SELECT JOB_ID, DURATION, STATUS FROM JOBS. In this case, fill in the columns and values parameters below
-
(Optional) Query (1-10) - Schedule
- The schedule, it uses cron format, you can use the website https://crontab.guru to help you create the schedule
- If empty, the query will run every minute
-
(Optional) Query (1-10) - Value columns
- Which columns you would like to send back to dynatrace as metrics, comma separated
- Example: For the query
SELECT JOB_ID, DURATION, STATUS FROM JOBS
- You can choose to send
DURATION,STATUS as a value. These columns need to be numbers
- If empty, the value of the first column of the first row is returned
-
(Optional) Query (1-10) - Dimension columns
- Which columns you would like to use as dimensions for a metric, comma separated
- Example: For the query
SELECT JOB_ID, DURATION, STATUS FROM JOBS
- You can choose to send
JOB_ID as a dimension, you would then have a metric for every job_id
- WARNING - Never ingest columns that have a very high number of unique values, like timestamps, UUIDs, row number, customer input, etc. This will cause a dimension explosion for your metric.
-
(Optional) Query (1-10) - Extra dimensions
key=Value pairs to be added as extra hardcoded dimensions- Can be used for things like
environment=PROD,database=USERS
Example Oracle endpoint
Example Informix endpoint
Visualization
The data is sent using our Metric Ingest API, so you need to use the Data Explorer to create charts.
The metric name is always custom.db.query, the different queries are accessed via the Query Name dimension
Monitoring
If Send Statistics was enabled, you can also monitor your queries with the metrics:
- custom.db.query.statistics.total_duration
- custom.db.query.statistics.result_state
- custom.db.query.statistics.query.rows
- custom.db.query.statistics.query.duration
- custom.db.query.statistics.query.state
Any errors running the queries will also be sent to this Custom Device as CUSTOM_INFO_EVENTS
