exasol / cloudwatch-adapter

Makes Exasol database monitoring data available in AWS CloudWatch

Exasol – AWS Cloudwatch Adapter

This adapter transfers the statistics from the Exasol database to AWS CloudWatch metrics. This adapter runs independently of the Exasol database in an AWS Lambda function.

Setup

Create an Exasol User

The CloudWatch adapter accesses your Exasol database via its SQL interface. For that it needs credentials. We recommend creating a dedicated user for that purpose:

CREATE USER CLOUDWATCH_ADAPTER IDENTIFIED BY "<PASSWORD>";

GRANT CREATE SESSION TO CLOUDWATCH_ADAPTER;

Don't forget to use a strong, randomly generated password instead of <PASSWORD>.

Store Credentials in AWS Secrets Manager

Create a new secret in the AWS Secrets Manager with the following values:

• host: VPC internal ip address of your Exasol database
• port: Exasol JDBC port (default: 8563)
• username: Name of an Exasol user account with CREATE SESSION privileges
• password: Password for the account
• certificateFingerprint: Fingerprint of the database's TLS certificate. This is only required if Exasol uses a self-signed certificate.

VPC Setup

The CloudWatch adapter must run in the same AWS VPC that the Exasol database runs. By that it can access the Exasol database using a internal IP address.

From within the VPC it can however not access the default Endpoints for AWS CloudWatch and SecretsManager.

To add them, go to the AWS VPC Console / Endpoints. There create endpoints for the following AWS services:

• com.amazonaws.<REGION>.monitoring
• com.amazonaws.<REGION>.secretsmanager

Make sure that you select the VPC, Subnet and Security group of your Exasol database.

It is important to enable DNS for the endpoint. If it's not possible, you might have to enable DNS in your VPC.

Setup CloudWatch Adapter

• Open the AWS Lambda Console
• Click "Create Function"
• Select "Browse serverless application repository"
• Search for "ExasolCloudWatchAdapter"
• Fill out the application settings

  • ExasolDeploymentName: A name describing the Exasol installation you want to monitor. The adapter adds this name as a dimension to the metrics in Cloudwatch. This will help you to distinguish the data if you monitor more than one Exasol deployment.

  • ExasolConnectionSecretArn: ARN of Secrets Manager secret you created in a previous step.

  • Metrics: A comma-separated list of metrics. If empty, the adapter reports all metrics. List of supported metrics.

  • SubnetId: ID of the VPC subnet of the Exasol database.

  • SecurityGroup: ID of the security group of the Exasol database.

• Click on "Deploy"

Create a Dashboard

Now the adapter should transmit the metrics to CloudWatch. To visualize them you have to create a CloudWatch dashboard. You could start from scratch and build your own dashboard. We, however, recommend you to start with our example dashboard. This comes with lots of preconfigured widgets, designed by the best practices of our monitoring experts.

Troubleshooting

If the adapter does not work properly, first check its log output. For that go to the AWS Management Console / Lambda, select the Lambda function of the adapter, and click on Monitoring. There click on View logs in CloudWatch and scan the log files for error messages.

Known Bugs

• In case your Exasol database uses a timezone with time-shift as DBTIMEZONE, this adapter will not report the hour when the time is shifted back, since Exasol stores the statistics entries in the DBTIMEZONE and by that, the log entries are ambiguous in that hour (see #2).
• Due to a bug in SAM we can not publish the app with a retry count set to 0. This can lead to duplicate reported data if the Lambda fails after reporting some points and EventBridge triggers it again (see #21).

Additional Information

The design of this adapter ensures that points are never written twice, which would lead to wrong statistics. It does, however not assure that all points are written. In case of temporary errors with the Exasol database or the CloudWatch API, it can occur that data points are missing.

• Changelog
• Developers Guide
• Dependencies

cloudwatch-adapter's Issues

Finish release

A new version of this project was released.
Now we need to deploy it to the AWS severless application store.

For that, please pick the packed SAM template from the latest run of Prepare SAM release workflow and send it to the person responsible for releasing on the Exasol AG AWS account.

Log version number for debugging

For debugging it would be helpful if the Lambda would write it's version to the log.

Set retry count for scheduled event to 0

The events must not retry, since this could lead to duplicate reporting

Add a convenient deployment method

Add a convenient method for quickly setting up this function.

Report metrics from EXA_SYSTEM_EVENTS more often

The metrics from EXA_SYSTEM_EVENTS are only written from time to time. For example one per day. For using them in Cloudwatch alarms it's however required that they are reported at least every 5 minutes.

Solution:

report most recent value every 5 mins.

Fix SAM template

Currently, the SAM deployment fails with a ClassNotFoundException.

Finish release

A new version of this project was released.
Now we need to deploy it to the AWS severless application store.

For that, please pick the packed SAM template from the latest run of Prepare SAM release workflow and send it to the person responsible for releasing on the Exasol AG AWS account.

Refactor exasol metric class structure

Refactor the class structure to support different metric providers as plugins.

Fix unstable Integration tests

Integration tests are unstable

Add support for TLS connections

When connecting to Exasol via TLS using self signed certificates it's necessary to specify the certificate fingerprint.

Finish release

A new version of this project was released.
Now we need to deploy it to the AWS severless application store.

For that, please pick the packed SAM template from the latest run of Prepare SAM release workflow and send it to the person responsible for releasing on the Exasol AG AWS account.

Add script / template for a nicely configured cloudwatch dashboard

Possible approach:
https://medium.com/poka-techblog/cloudwatch-dashboards-as-code-the-right-way-using-aws-cdk-1453309c5481

Update log4j to fix CVE-2021-44228

Cloudwatch adapter uses log4j versions affected by CVE-2021-44228:

[INFO]    org.apache.logging.log4j:log4j-core:jar:2.14.1:compile
[INFO]    org.apache.logging.log4j:log4j-slf4j18-impl:jar:2.14.1:compile
[INFO]    com.amazonaws:aws-lambda-java-log4j2:jar:1.2.0:compile
[INFO]    org.apache.logging.log4j:log4j-api:jar:2.14.1:compile

Dependency tree

[INFO] +- com.amazonaws:aws-lambda-java-log4j2:jar:1.2.0:compile
[INFO] +- org.apache.logging.log4j:log4j-api:jar:2.14.1:compile
[INFO] +- org.apache.logging.log4j:log4j-core:jar:2.14.1:compile
[INFO] +- org.apache.logging.log4j:log4j-slf4j18-impl:jar:2.14.1:compile

Remove RetryPolicy workaround from developers guide

After https://github.com/aws/serverless-application-model/issues/1931 is fixed, remove RetryPolicy workaround from developers guide.

Remove exclude for E-PK-CORE-53 dependencies.md file has outdated content

After setting required maven version to 3.8.7. or higher with PK ticket #444 (project-keeper release 2.9.6) users can remove the suppressed warning from file .project-keeper.yml:

  - regex: "(?s)E-PK-CORE-53: The dependencies.md file has outdated content.*"

Affected repositories:

• spark-connector
• ci-isolation-aws
• error-catalog
• cloudwatch-adapter
• tableau-connector
• parquet-io-java
• virtual-schema-common-document
• parquet-edml-generator
• cloudwatch-dashboard-examples
• glue-connector
• exasol-virtual-schema
• ✅ project-keeper
• s3-document-files-virtual-schema
• cloud-storage-extension
• extension-manager
• java-class-list-extractor
• edml-java

Add wrapper for most common SDK exception

Add a wrapper with mitigations around typical AWS SDK exceptions.

Migrate to AWS CDK v2

Information doesn't match with ExaOperation

hello, I'm comparing the information collected by metrics vs information showed in exa operation and it doesn't match, could you guide me for this

Handle different Exasol versions

Currently, only Exasol 7.1.x is supported

Add guide for manual deployment

Add a guide that explains how to directly deploy this APP without using the serverless application repository.

Report metrics from EXA_SYSTEM_EVENTS only every 4 mins

PR #36 implements reporting of metrics from EXA_SYSTEM_EVENTS every minute.
This is too often and should only be every 4 mins.

Fix sonatype-2020-0026

This project is currently affected by the transitive vulnerability sonatype-2020-0026.

This comes from the AWS CloudWatch SDK (see aws/aws-sdk-java-v2#3263). As soon as they fixed it there, we should also update the dependency here.

Project uses outdated repo maven.exasol.com

Error:  Failed to execute goal on project cloudwatch-adapter: Could not resolve dependencies for project com.exasol:cloudwatch-adapter:jar:1.0.5: Failed to collect dependencies at com.exasol:exasol-jdbc:jar:7.1.11: Failed to read artifact descriptor for com.exasol:exasol-jdbc:jar:7.1.11: Could not transfer artifact com.exasol:exasol-jdbc:pom:7.1.11 from/to maven.exasol.com (https://maven.exasol.com/artifactory/exasol-releases): transfer failed for https://maven.exasol.com/artifactory/exasol-releases/com/exasol/exasol-jdbc/7.1.11/exasol-jdbc-7.1.11.pom: Unknown host maven.exasol.com -> [Help 1]

Finish release

A new version of this project was released.
Now we need to deploy it to the AWS severless application store.

For that, please pick the packed SAM template from the latest run of Prepare SAM release workflow and send it to the person responsible for releasing on the Exasol AG AWS account.

Add support for reporting backup events

For administrators it would be helpful to create CloudWatch alerts that indicate if an Exasol backup succeeded or failed.

For that we can add a Metric based on events in the EXA_SYSTEM_EVENTS table. We need to report the following events:

• BACKUP_START
• BACKUP_END
• BACKUP_ABORTED

The user must be able to configure which events should be used.

Document installation without VPC Endpoints

VPC endpoints are not required if the Exasol database runs in a public subnet. For details see: https://docs.aws.amazon.com/lambda/latest/dg/configuration-vpc.html#vpc-internet

Todo:

• test this approach
• update documentation
• update video

Support Exasol in VPC

Add support for Exasol databases that have no public IP address.

Fix release

Release is broken due to invalid artifact name.

Use AWS Secrets Manager for storing the Exasol credentials

Currently, the credentials of the Exasol database are stored in environment variables of the Lambda function. For security reasons we should use the AWS Secrets Manager here instead.

Use localstack for integration tests

use localstack instead of the online AWS SDK for integration tests.

Report not possible during time-shift

In case your Exasol database uses a timezone with a time-shift as DBTIMEZONE, this adapter will not report the hour when the time is shifted back, since Exasol stores the statistics entries in the DBTIMEZONE and by that, the log entries are ambiguous in that hour.

Steps for Fix:

• Change column EXA_STATISTICS table to use TIMESTAMP WITH LOCAL TIMEZONE instead of TIMESTAMP or store entries in UTC.
• Change this adapter to use the new API

Finish release

A new version of this project was released.
Now we need to deploy it to the AWS severless application store.

For that, please pick the packed SAM template from the latest run of Prepare SAM release workflow and send it to the person responsible for releasing on the Exasol AG AWS account.

Report temporary db-ram more often

For monitoring the database it would be great to report the temporary db ram size more often then every 30 minutes.
That could be possible by reading it as an aggregation of all rows of SYS.EXA_ALL_SESSIONS.

(This is a follow-up of #61)

Fix vulnerabilities reported by dependencies_check.yml

com.amazonaws:aws-java-sdk-s3:jar:1.12.245:test
* [CVE-2022-31159], severity CWE-22: Improper Limitation of a Pathname to a Restricted Directory ('Path Traversal') (6.5)

Fix SAM CI

Right now, CI fails since sam is not installed correctly

Add video guide for installation

Add a video guide that explains the installation

Basic adapter

Acceptance criteria

1. Adapter makes a single performance metric available in CloudWatch

No backup metrics collected

Previously of the new version released I saw 32 metrics collected, but after the upgrade I still see the same 32 metrics, do I need to perform anything else to see backup metrics or any new metric added?, below the link for metrics that I refer to.

https://github.com/exasol/cloudwatch-adapter/blob/main/doc/changes/changes_1.0.0.md#cloudwatch-adapter-100-released-2022-02-23

As your team suggested in other ticket I have validated information in table EXA_SYSTEM_EVENTS

Change localstack version from latest to 0.12.6

Right now the required features from localstack/localstack#3508 (comment) are only available in latest. After 0.12.6 is released, we should use the fixed version.

Add configuration for VPC

Currently, users have to reconfigure the VPC of the lambda after its creation.

It would be more convenient to directly have a property for the VPC.

Dependency upgrade

See log messages from build job Dependency Check:

• io.netty:netty-handler:jar:4.1.86.Final in runtime
  • CVE-2023-34462, severity CWE-770: Allocation of Resources Without Limits or Throttling (6.5)

I don't see metrics collected

Hello,

I have installed cloudwatch-adapter for exasol (DB version 7.1.6), currently I just see these messages in cloudwatch:

• sun.reflect.Reflection.getCallerClass is not supported. _This will impact performance.

• Transforming org/apache/logging/log4j/core/lookup/JndiLookup (lambdainternal.CustomerClassLoader@433c675d)

• START RequestId: bbf47e5c-7f52-4e5a-b58a-25dd37c8027b Version: $LATEST

• 2022-03-20 18:40:14 bbf47e5c-7f52-4e5a-b58a-25dd37c8027b INFO CloudWatchAdapter - Writing 108 points.

• END RequestId: bbf47e5c-7f52-4e5a-b58a-25dd37c8027b

• REPORT RequestId: bbf47e5c-7f52-4e5a-b58a-25dd37c8027b Duration: 4497.37 ms Billed Duration: 4498 ms Memory Size: 512 MB Max Memory Used: 184 MB Init Duration: 4733.76 ms

**Checking inside of DB I just see these commands performed by user CLOUDWATCH_ADAPTER **

• SELECT NOW()

• SELECT CONVERT_TZ(MEASURE_TIME, DBTIMEZONE, 'UTC', 'INVALID REJECT AMBIGUOUS REJECT') as UTC_MEASURE_TIME, t.* FROM "EXA_STATISTICS"."EXA_DB_SIZE_LAST_DAY" t WHERE MEASURE_TIME >= CONVERT_TZ(?, 'UTC', DBTIMEZONE, 'INVALID REJECT AMBIGUOUS REJECT') AND MEASURE_TIME < CONVERT_TZ(?, 'UTC', DBTIMEZONE, 'INVALID REJECT AMBIGUOUS REJECT');

• COMMIT /* AUTO */

-ALTER SESSION SET TIME_ZONE = 'UTC'

Dependency check build fails

Error:  Failed to execute goal org.sonatype.ossindex.maven:ossindex-maven-plugin:3.2.0:audit (default-cli) on project cloudwatch-adapter: Detected 1 vulnerable components:
Error:    com.fasterxml.jackson.core:jackson-databind:jar:2.12.6.1:test; https://ossindex.sonatype.org/component/pkg:maven/com.fasterxml.jackson.core/[email protected]?utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2022-42003] CWE-502: Deserialization of Untrusted Data (7.5); https://ossindex.sonatype.org/vulnerability/CVE-2022-42003?component-type=maven&component-name=com.fasterxml.jackson.core%2Fjackson-databind&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1
Error:      * [CVE-2022-42004] CWE-502: Deserialization of Untrusted Data (7.5); https://ossindex.sonatype.org/vulnerability/CVE-2022-42004?component-type=maven&component-name=com.fasterxml.jackson.core%2Fjackson-databind&utm_source=ossindex-client&utm_medium=integration&utm_content=1.8.1

Metrics have wrong unit

While Exasol stores the values in MiB, GiB and MiB/s Cloudwatch uses MB, GB, and MB/s.

Currently, the CloudtWach adapter reports the unchanged value under the wrong unit.

Fix:

Convert data during transfer

Update readme with dependency list

Acceptance Criteria

• All the project dependencies (runtime, test) are listed in the readme

How to update to the last version

Hello, do you have the steps to implement below metrics or last version of cloudwatch adapter?

https://github.com/exasol/cloudwatch-adapter/blob/main/doc/changes/changes_1.0.0.md#cloudwatch-adapter-100-released-2022-02-23

Add additional Metrics

• NET
• SWAP
• EXA_SYSTEM_EVENTS/NODES
• EXA_SYSTEM_EVENTS/DB_RAM_SIZE

Add metrics for query count

Add metrics that count the queries, grouped by their type.
The information can be aggregated from EXA_SQL_LAST_DAY

Add documentation on VPC API endpoints

The adapter needs AWS API enpoints for cloudwatch and monitoring inside of the VPC.

Update log4j library to 2.17.1 version