Configuring Oracle DB Exporter to Run Custom SQL Queries

In addition to collecting a standard set of default metrics, Oracle DB Exporter also allows you to define and collect custom metrics by running your own SQL queries. This helps you monitor application-specific performance indicators or business data that are not captured by the default database metrics.

You can use custom SQL queries to tailor your observability solution by monitoring and collecting business critical metrics, such as Siebel version, transaction counts, or any custom key performance indicators, from Oracle Database. These metrics can then be visualized through Prometheus and Grafana dashboards.

To execute custom SQL metrics using Oracle DB Exporter:

Go to the oracle-db-monitoring Helm chart directory:

cd /home/opc/siebel/<ENV_ID>/<env_namespace>-helmcharts/oracle-db-monitoring

Go to the templates directory:
```
cd templates
```
Edit the custom-metric-config.yaml file to define a new metric. Under the custom-metric.toml section, add the new metric block as shown below:
```
[[metric]]
context = <context>
labels = <labels>
metricsdesc = { <metricdescription> }
request = <SQLQuery>
databases = <databases>
```
The variables in the example have the following values:
- [[ metric ]] is used to define a new metric block. You can have multiple [[ metric ]] blocks in the YAML file for different queries.
- <context> is used to group related metrics together. Oracle DB Exporter will prefix metrics with this context. For example:
```
context = "siebel_app_version"
```
- <labels> is used to define extra Prometheus labels to attach to the metric. For example:
```
labels = [ "version" ]
```
- <metricdescription> is used to map the metric name to a user-friendly description. For example:
```
metricsdesc = { siebel_app_version="Siebel application version" }
```
- <SQLQuery> is used to specify the SQL query that Oracle DB Exporter will run against the Oracle Database. For example:
```
request = ''' select 1 as siebel_app_version, APP_VER as version from {{.Values.oracleDBMonitoring.table_owner_user }}.S_APP_VER where ROWNUM = 1  '''
```
  In the SQL query above, {{ .Values.oracleDBMonitoring.table_owner_user }} is a Helm template variable that is used to dynamically insert the table owner user during deployment, based on the value set for oracleDBMonitoring.table_owner_user in the Oracle Database monitoring Helm chart's values.yaml file. You must not change the value of this parameter; Oracle DB Exporter automatically will replace this placeholder with the correct schema value during deployment.
- <databases> is used to define the target of the SQL query. You can assign one of the following values to this parameter:
  - ForDataAsMetricsExport: Use this value when your custom query targets Siebel application tables.
  - ForDBMetricsExport: Use this value when your custom query targets database objects other than Siebel tables.
  Note: If you set the database value incorrectly, it may cause connection or privilege issues during metric collection.
  Example of [[metric]] block:
```
[[metric]]
    context = "siebel_app_version"
    labels = [ "version" ]
    metricsdesc = { siebel_app_version="Siebel application version" }
    request = '''
    select 1 as siebel_app_version, APP_VER as version
    from {{ .Values.oracleDBMonitoring.table_owner_user }}.S_APP_VER
    where ROWNUM = 1
    '''
 databases = [ "ForDataAsMetricsExport" ]
```
  Note: Prometheus is designed to ingest and store numeric time-series data. It does not natively support string values as the output for metrics. When you want to capture string information, such as a Siebel CRM application version, you must encode this information in a way that Prometheus can handle. In the above custom metric definition, version is specified as a label, Prometheus stores a metric like oracledb_siebel_app_version_siebel_app_version{version="25.9"} with the value set to 1. The string value ("25.9") is attached as a label to the metric.
Save the custom-metric-config.yaml file.
Increment the chart version as follows:
1. Go to the oracle-db-monitoring Helm chart directory.
2. Open the Chart.yaml file and increment the version number. For example, update the version 0.1.0 to 0.1.1.

Commit the changes to the Git repository:

git add .
git commit -m "Oracle DB monitoring helmchart changes." 
git push

Verify that the latest commit listed by Flux matches the commit you just pushed. This confirms that Flux has detected and synchronized your changes:
```
flux get source git -n <env_namespace>
```
After the reconciliation is completed, exec into the Oracle DB Exporter pod and verify that ConfigMap includes the new custom metric as follows:
```
kubectl exec -it <oracle-db-monitoring pod> -n <namespace> -- cat /oracle/observability/custom-metric.toml
```
Verify the output in Prometheus as follows:
1. Open the Prometheus user interface.
2. Run the query for your new metric to view the SQL query output as reported by Oracle DB Exporter.
```
oracledb_<context>_<key of metricsdesc>
```

Troubleshooting Custom Metrics Issues

If you don't see your custom metrics in Prometheus, or encounter errors during configuration, follow these steps to resolve the issue:

Ensure that the syntax in your custom-metric.toml file is correct.
Ensure your SQL query returns at least one numeric column per metric (required for Prometheus ingestion). If you need to report a string value, use a constant numeric value (like 1) as the metric and return the string field as a label.
Ensure that the metrics users have the required privileges assigned, in case of BYOD. For more information, see Prerequisites for Enabling Oracle Database Monitoring for BYOD.
Ensure that the custom-metric-config.yaml file changes are saved and the chart version has been incremented. After committing the changes, ensure that the GitOps or Helm reconciliation completes without errors.
Check the container logs for any ERROR messages related to scraping custom metrics:
```
kubectl logs <oracle-db-monitoring pod> -n <namespace>
```
Log in to the Oracle DB Exporter pod and view the custom-metric.toml file to confirm your changes:
```
kubectl exec -it <oracle-db-monitoring pod> -n <namespace> -- cat /oracle/observability/custom-metric.toml
```