# Ship Snowflake logs and audit data with OpenTelemetry Copy as Markdown[Open in ChatGPT](https://chatgpt.com/?q=Read%20https%3A%2F%2Fcoralogix.com%2Fdocs%2Fintegrations%2Fsecurity%2Fshipping-snowflake-logs-and-audit-data-to-coralogix.md%20and%20help%20me%20with%20my%20question%20about%20this%20Coralogix%20documentation%20page.)[Open in Claude](https://claude.ai/new?q=Read%20https%3A%2F%2Fcoralogix.com%2Fdocs%2Fintegrations%2Fsecurity%2Fshipping-snowflake-logs-and-audit-data-to-coralogix.md%20and%20help%20me%20with%20my%20question%20about%20this%20Coralogix%20documentation%20page.) [Snowflake](https://www.snowflake.com/en/) has no direct Coralogix export, but it records user activity in queryable account usage views. This page routes that data to Coralogix through the [OpenTelemetry Collector](https://coralogix.com/docs/opentelemetry/getting-started.md): the SQL query receiver runs a query against Snowflake on an interval and emits each row as a log. The Collector also ships a dedicated [Snowflake receiver](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/snowflakereceiver) for performance and resource metrics. This page covers the [SQL query receiver](https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/sqlqueryreceiver), which is what reaches the log and audit tables. ## What you need [​](#what-you-need- "Direct link to what-you-need-") * An [OpenTelemetry Collector](https://coralogix.com/docs/opentelemetry/getting-started.md) installed and configured. * An active Snowflake account with sufficient access to the account usage views, `ACCOUNTADMIN`, for example. * Your Coralogix domain and a Send-Your-Data API key, supplied to the Collector as `CORALOGIX_DOMAIN` and `PRIVATE_KEY`. ## Build the connection string [​](#build-the-connection-string- "Direct link to build-the-connection-string-") The SQL query receiver connects with a datasource string built from your Snowflake account identifier. 1 . Copy your account URL In the Snowflake console, open the account menu in the bottom-left corner, hover over your account, and select **Copy account URL**. [![Snowflake console account menu expanded, with the account entry and the Copy account URL control marked](/docs/assets/images/Untitled-2024-07-10T175135.297-45f0f8a6e5d393cf0d8d373c76ef4812.webp)](https://coralogix.com/docs/assets/images/Untitled-2024-07-10T175135.297-45f0f8a6e5d393cf0d8d373c76ef4812.webp) Shows the path from the user menu to the account URL that carries the identifier. 2 . Extract the account identifier The URL follows this shape, and everything before `snowflakecomputing.com` is the identifier, for `xy12345.us-east-2.aws.snowflakecomputing.com`, that is `xy12345.us-east-2.aws`. ``` ...snowflakecomputing.com ``` Snowflake covers the variants in its [account identifiers guide](https://docs.snowflake.com/en/user-guide/admin-account-identifier). 3 . Assemble the datasource string Combine your credentials and identifier: ``` :@/SNOWFLAKE/ACCOUNT_USAGE ``` ## Configure the SQL query receiver [​](#configure-the-sql-query-receiver- "Direct link to configure-the-sql-query-receiver-") Add the receiver to your Collector configuration file (`otel-collector-config.yaml`, typically) and attach it to a logs pipeline. This example collects login history. ``` 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: collection_interval: 21600s driver: snowflake datasource: ":@.snowflakecomputing.com/SNOWFLAKE/ACCOUNT_USAGE" 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, 'EVENT_ID', EVENT_ID ) log, EXTRACT(EPOCH FROM EVENT_TIMESTAMP) AS EPOCH_TIMESTAMP FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY WHERE EXTRACT(EPOCH FROM EVENT_TIMESTAMP) > ? ORDER BY EPOCH_TIMESTAMP ASC tracking_start_value: "0" tracking_column: EPOCH_TIMESTAMP logs: - body_column: LOG service: pipelines: logs: receivers: - sqlquery exporters: - coralogix ``` ### What the settings do[​](#what-the-settings-do "Direct link to What the settings do") | Setting | Purpose | | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `driver` | The database driver. `snowflake`. | | `datasource` | The connection string you assembled above. | | `collection_interval` | How often the query runs. Lower it for fresher data. | | `sql` | The query. It builds a JSON object from `LOGIN_HISTORY` columns and takes the tracking value through the `?` placeholder. | | `tracking_column` and `tracking_start_value` | Where the receiver resumes from between runs. | | `logs.body_column` | The column whose contents become the log body. `LOG` here. | Note Derive a numeric `EPOCH_TIMESTAMP` column from `EXTRACT(EPOCH FROM EVENT_TIMESTAMP)` and track on that. `tracking_column` only works on numeric types, and the table's `EVENT_ID` is not a continuous sequence. With `ORDER BY EPOCH_TIMESTAMP ASC`, the Collector carries the last row's value into the next query. Restarting the Collector resets tracking to `tracking_start_value` and re-queries everything. Bound the query to avoid that, for example, `EVENT_TIMESTAMP >= DATEADD(hour, -6, CURRENT_TIMESTAMP())`. ## Deploy and validate [​](#deploy-and-validate- "Direct link to deploy-and-validate-") 1 . Add the receiver to your deployment Fold the `sqlquery` receiver into the Collector configuration you deploy, Kubernetes, ECS, or EC2. 2 . Deploy the Collector Apply the configuration change and roll out the Collector. 3 . Check for duplicates Confirm exactly one `sqlquery` receiver instance is running. More than one produces duplicate records. 4 . View the logs In Coralogix, select **Explore**, then **Logs**. [![Coralogix Explore Logs view showing a Snowflake login history event with user, client type, and authentication factor fields](/docs/assets/images/Untitled-2024-07-10T175151.145-1-fda0fb38cfcee0ac38119a8ee7ced1e7.webp)](https://coralogix.com/docs/assets/images/Untitled-2024-07-10T175151.145-1-fda0fb38cfcee0ac38119a8ee7ced1e7.webp) Shows a login history row arriving as a structured log, with the fields the query constructed. ## Collect more tables[​](#collect-more-tables "Direct link to Collect more tables") Repeat the `sqlquery.queries` block for each account usage view you want. Common candidates: `WAREHOUSE_EVENTS_HISTORY`, `WAREHOUSE_LOAD_HISTORY`, `WAREHOUSE_METERING_HISTORY`, `DATABASE_STORAGE_USAGE_HISTORY`, `DATA_TRANSFER_HISTORY`, `GRANTS_TO_ROLES`, `GRANTS_TO_USERS`, `METERING_DAILY_HISTORY`, `PIPE_USAGE_HISTORY`, `REPLICATION_USAGE_HISTORY`, `STAGE_STORAGE_USAGE_HISTORY`, `STORAGE_USAGE`, `TASK_HISTORY`, and `COPY_HISTORY`. ## ECS task definition[​](#ecs-task-definition "Direct link to ECS task definition") A Fargate task definition that runs the Collector with its configuration and API key pulled from Secrets Manager: ``` { "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": "" }, { "name": "PRIVATE_KEY", "valueFrom": "" } ], "user": "0", "logConfiguration": { "logDriver": "awslogs", "options": { "awslogs-group": "-coralogix-open-telemetry", "awslogs-create-group": "true", "awslogs-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": "" } ] } ``` ## Related resources[​](#related-resources "Direct link to Related resources") [OpenTelemetry Collector](https://coralogix.com/docs/opentelemetry/getting-started.md)[Send-Your-Data API key](https://coralogix.com/docs/user-guides/account-management/api-keys/send-your-data-api-key.md)[Explore screen](https://coralogix.com/docs/user-guides/data_exploration.md)