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
![endpoint](https://dt-cdn.net/hub/oracle.png)
Example Informix endpoint
![endpoint](https://dt-cdn.net/hub/informix_endpoint.png)
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
![chart](https://dt-cdn.net/hub/chart_b4EixwB.png)
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