Skip to content

Shipping Snowflake Logs and Audit Data to Coralogix with OpenTelemetry

This tutorial demonstrates how to centralize logging for Snowflake by sending logs to Coralogix via OpenTelemetry.

Overview

Snowflake is a cloud-based data warehousing platform known for efficiently storing, processing, and analyzing vast amounts of data. It provides scalable data warehousing services that enable organizations to manage and retrieve data seamlessly in a cloud environment.

Snowflake does not directly integrate with Coralogix for exporting logs and audit-related data. However, you can follow this detailed guide to leverage the OpenTelemetry Collector. Using the OpenTelemetry Collector, you can extract Snowflake logs and audit information and transmit them to Coralogix. Coralogix processes this data for streamlined visualization and analysis within its platform.

Prerequisites

  • OpenTelemetry Collector installed and configured

  • Active Snowflake account with appropriate access (e.g., ACCOUNTADMIN)

Configuring the OpenTelemetry Collector

The OpenTelemetry Collector includes observability components that support Snowflake, such as the Snowflake Receiver for monitoring key metrics and the SQL Query Receiver for executing custom queries via a built-in Snowflake database driver. These tools provide insights into Snowflake's performance and resource utilization.

Snowflake’s built-in capabilities track and log user activities. Logs and audit data can be obtained using queries, integrated with the OpenTelemetry Collector’s SQL Query Receiver to enhance observability and auditing.

STEP 1: Obtaining your Snowflake account identifier

The account identifier uniquely identifies your Snowflake account within your organization. It is required for the connection string and is used to declare cloud platforms and regions.

To obtain your account identifier:

  1. Go to your Snowflake console.

  2. In the bottom left corner, copy the account URL.

Extract the account identifier from the account URL (e.g., xy12345.us-east-2.aws.snowflakecomputing.com), following this format:

<account_locator>.<cloud_region_id>.<cloud>.snowflakecomputing.com

Refer to the Snowflake Account Identifiers Guide for more details.

STEP 2: Creating a connection string

The connection string is used to establish a connection to your Snowflake database. Format it as follows:

<username>:<password>@<account_identifier>/SNOWFLAKE/ACCOUNT_USAGE

  • <username>: Your Snowflake username.

  • <password>: Your Snowflake password.

  • <account_identifier>: Your Snowflake account identifier.

STEP 3: Writing the SQL query receiver configuration

Create the configuration for the SQL Query Receiver in your OpenTelemetry Collector configuration file (otel-collector-config.yaml):

exporters:
  coralogix:
    application_name: 'otel'
    application_name_attributes:
    - aws.ecs.task.family
    - service.namespace
    domain: ${CORALOGIX_DOMAIN}
    logs:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
    metrics:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
    private_key: ${PRIVATE_KEY}
    subsystem_name: 'integration'
    subsystem_name_attributes:
    - service.name
    - aws.ecs.docker.name
    - container_name
    timeout: 30s
    traces:
      headers:
        X-Coralogix-Distribution: ecs-fargate-integration/0.0.1
receivers:
  sqlquery:
    driver: snowflake
    datasource: "<USERNAME>:<PASSWORD>@<ACCOUNT_IDENTIFIER>.snowflakecomputing.com/SNOWFLAKE/ACCOUNT_USAGE"
    collection_interval: 21600s
    queries:
      - sql: |
          SELECT 
            OBJECT_CONSTRUCT(
              'application', 'snowflake',
              'environment', 'debug', 
              'log_type', 'login_history',
              'EVENT_TIMESTAMP', EVENT_TIMESTAMP,
              'EVENT_TYPE', EVENT_TYPE,
              'USER_NAME', USER_NAME,
              'CLIENT_IP', CLIENT_IP,
              'REPORTED_CLIENT_TYPE', REPORTED_CLIENT_TYPE,
              'FIRST_AUTHENTICATION_FACTOR', FIRST_AUTHENTICATION_FACTOR,
              'IS_SUCCESS', IS_SUCCESS,
              'ERROR_CODE', ERROR_CODE,
              'ERROR_MESSAGE', ERROR_MESSAGE
            ) log, 
            EVENT_ID 
          FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
          WHERE EVENT_TIMESTAMP >= DATEADD(hour, -6, CURRENT_TIMESTAMP())
        tracking_start_value: "0"
        tracking_column: EVENT_ID
        logs:
          - body_column: LOG
service:
  pipelines:
    logs:
      receivers:
        - sqlquery
      exporters:
        - coralogix

Configuration breakdown

The sqlquery receiver configuration executes custom SQL queries against a specified database and collects the results. Key configurations include:

  • driver: Specifies the database driver (snowflake).

  • datasource: Connection string including username, password, and account identifier.

  • collection_interval: Frequency of query execution, change it to your preference (e.g., every 60 seconds).

  • queries: List of SQL queries to execute, defining:

    • sql: The query constructing a JSON object from LOGIN_HISTORY columns. The tracking value is passed as a parameter using the ? placeholder.

    • tracking_start_value and tracking_column: Settings for tracking query progress using EVENT_TIMESTAMP.

  • logs: Defines how query results are processed as logs, utilizing LOG as the body column.

Note

We recommend using EVENT_TIMESTAMP instead of EVENT_ID as the tracking column to prevent duplicate records. When using EVENT_TIMESTAMP, ensure results are sorted in ascending order (ORDER BY EVENT_TIMESTAMP ASC), as the collector uses the last result's row tracking_column value as the parameterized value for the next query.

Example SQL query:

The OBJECT_CONSTRUCT function creates a JSON object from selected LOGIN_HISTORY columns, enhancing log structure for OpenTelemetry Collector processing.

SELECT OBJECT_CONSTRUCT('application', 'snowflake', 'environment', 'debug', 'log_type', 'login_history', 'EVENT_TIMESTAMP', EVENT_TIMESTAMP, 'EVENT_TYPE', EVENT_TYPE, 'USER_NAME', USER_NAME, 'CLIENT_IP', CLIENT_IP, 'REPORTED_CLIENT_TYPE', REPORTED_CLIENT_TYPE, 'FIRST_AUTHENTICATION_FACTOR', FIRST_AUTHENTICATION_FACTOR, 'IS_SUCCESS', IS_SUCCESS, 'ERROR_CODE', ERROR_CODE, 'ERROR_MESSAGE', ERROR_MESSAGE) log, EVENT_ID FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

STEP 4: Deployment and validation

  • Add sqlquery receiver to OpenTelemetry Collector Config: Integrate sqlquery receiver into deployment configurations (e.g., Kubernetes, ECS).

  • Deploy OpenTelemetry Collector: Apply configuration changes and deploy OpenTelemetry Collector.

  • Validate Configuration: Ensure correct log collection, confirming single sqlquery receiver instance to prevent duplicate records.

STEP 5: [Optional] Expanding SQL configurations

Repeat SQL configurations (sqlquery.queries) for all Snowflake tables storing logs and audit data.

ECS task definition

{
  "containerDefinitions": [
      {
          "name": "otel-collector",
          "image": "otel/opentelemetry-collector-contrib",
          "cpu": 0,
          "portMappings": [
              {
                  "name": "otel-collector-4317-tcp",
                  "containerPort": 4317,
                  "hostPort": 4317,
                  "protocol": "tcp",
                  "appProtocol": "grpc"
              },
              {
                  "name": "otel-collector-4318-tcp",
                  "containerPort": 4318,
                  "hostPort": 4318,
                  "protocol": "tcp",
                  "appProtocol": "grpc"
              }
          ],
          "essential": true,
          "command": [
              "--config",
              "env:SSM_CONFIG"
          ],
          "environment": [
              {
                  "name": "CORALOGIX_DOMAIN",
                  "value": "us2.coralogix.com"
              }
          ],
          "mountPoints": [],
          "volumesFrom": [],
          "secrets": [
              {
                  "name": "SSM_CONFIG",
                  "valueFrom": "<ARN_TO_SECRETS_MANAGER>"
              },
              {
                  "name": "PRIVATE_KEY",
                  "valueFrom": "<ARN_TO_SECRETS_MANAGER>"
              }
          ],
          "user": "0",
          "logConfiguration": {
              "logDriver": "awslogs",
              "options": {
                  "awslogs-group": "<ENV>-coralogix-open-telemetry",
                  "awslogs-create-group": "true",
                  "awslogs-region": "<REGION>",
                  "awslogs-stream-prefix": "snowflake"
              }
          },
          "systemControls": []
      }
  ],
  "family": "staging-coralogix-open-telemetry",
  "networkMode": "awsvpc",
  "revision": 22,
  "volumes": [],
  "status": "ACTIVE",
  "requiresAttributes": [
      {
          "name": "com.amazonaws.ecs.capability.logging-driver.awslogs"
      },
      {
          "name": "ecs.capability.execution-role-awslogs"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.19"
      },
      {
          "name": "ecs.capability.secrets.asm.environment-variables"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.17"
      },
      {
          "name": "com.amazonaws.ecs.capability.task-iam-role"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.18"
      },
      {
          "name": "ecs.capability.task-eni"
      },
      {
          "name": "com.amazonaws.ecs.capability.docker-remote-api.1.29"
      }
  ],
  "placementConstraints": [],
  "compatibilities": [
      "EC2",
      "FARGATE"
  ],
  "requiresCompatibilities": [
      "FARGATE"
  ],
  "cpu": "1024",
  "memory": "3072",
  "runtimePlatform": {
      "cpuArchitecture": "X86_64",
      "operatingSystemFamily": "LINUX"
  },
  "tags": [
      {
          "key": "project",
          "value": "<PROJECT_NAME>"
      }
  ]
}

Validation

Navigate to Explore > Logs in Coralogix to view your centralized Snowflake logs.

Support

Need help?
Our customer success team is available 24/7 via in-app chat or email at support@coralogix.com.

Previous SentinelOne (Syslog)
Next Snyk Vulnerability Monitoring with Coralogix
Home
User Guides
DataPrime
Integrations
OpenTelemetry
Developer Portal