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):

receivers:
  sqlquery:
    driver: snowflake
    datasource: "<username>:<password>@<account_identifier>/SNOWFLAKE/ACCOUNT_USAGE"
    collection_interval: 10s
    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
        tracking_start_value: "0"
        tracking_column: EVENT_ID
        logs:
          - body_column: LOG
service:
  pipelines:
    logs:
      receivers:
        - sqlquery

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 (e.g., every 10 seconds).

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

  • sql: The query constructing a JSON object from LOGIN_HISTORY columns.

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

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

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.

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 [email protected].

Previous SURF

Next Duo Security

Theme

Light

light

dark