Snowflake

Self Service Ingestion

Connect this data source on your own, using the Hunters platform.

TL;DR

Supported data types	IOC search	Search	Table name	Log format	Collection method
Login History	✅	✅	snowflake_login_history	NDJSON	API
Reader Account Login History	✅	✅	snowflake_reader_account_login_history	NDJSON	API
Query History		✅	snowflake_query_history	NDJSON	API
Reader Account Query History		✅	snowflake_reader_account_query_history	NDJSON	API

Overview

Snowflake Audit Logs provide a record of activities within the Snowflake data warehouse, offering insights into user actions, system events, and resource utilization.
In the process of integrating the logs into hunters, the data is fetched using API, normalized into schemas and streamed to Hunters' Data Lake. The Ingestion allows the exploration of this source for overseeing users' usages in the Snowflake warehouse.

Supported data types

📘Note

Login History and Query History data types are currently supported for Snowflake instances that are self hosted only.

Login History

Table name: snowflake_login_history

Snowflake's Login History logs are an essential feature for security and auditing within the Snowflake data warehousing platform. These logs provide a comprehensive record of authentication activities, detailing when and how users log into the Snowflake environment. Key information captured includes the user's identity, login time, the IP address from which the login was attempted, and the success or failure of each login attempt.

Learn more here.

Reader Account Login History

Table name: snowflake_reader_account_login_history

Snowflake's Reader Account Login History logs specifically track login activities associated with Reader Accounts, which are designed for sharing secure and governed access to specific data with external users. These logs are a vital part of Snowflake's auditing capabilities, providing detailed visibility into when and how users access the Snowflake environment through Reader Accounts schema within the last 365 days.

Learn more here.

Query History

Table name: snowflake_query_history

These logs provide detailed insights into the queries run by users, including the query text, execution time, user identity, the warehouse on which the query was executed, and the performance outcomes of these queries within the last 365 days.

Learn more here.

Reader Account Query History

Table name: snowflake_reader_account_query_history

Snowflake's Reader Account Query History logs specifically track the SQL queries executed within the last 365 days through Reader Accounts, which are specialized accounts designed for sharing access to data in a secure and governed manner with external users. These logs are crucial for auditing and monitoring the data access and query activities performed by users of Reader Accounts.

Learn more here.

Send data to Hunters

Hunters supports the collection of Snowflake logs using API.

💡Before you start

If your Snowflake instance is hosted by Hunters, skip to Step 2.

Step 1: Set up credentials on Snowflake

Use the ACCOUNTADMIN role to set up a new role for the Hunters integration:

-- How to enable Hunters access the QUERY_HISTORY & LOGIN_HISTORY tables for the sake of collecting them as audit logs:
USE ROLE ACCOUNTADMIN;
-- Create a dedicated role for Hunters to access SNOWFLAKE.ACCOUNT_USAGE
CREATE OR REPLACE ROLE PC_HUNTERS_DB_COLLECTION_ROLE COMMENT = 'Hunters Role To Query SNOWFLAKE.ACCOUNT_USAGE Tables';
-- We use GOVERNANCE_VIEWER To access SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE PC_HUNTERS_DB_COLLECTION_ROLE;
-- We use SECURITY_VIEWER To access SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE PC_HUNTERS_DB_COLLECTION_ROLE;
-- We use READER_USAGE_VIEWER and MONITOR USAGE To access SNOWFLAKE.READER_ACCOUNT_USAGE.LOGIN_HISTORY and SNOWFLAKE.READER_ACCOUNT_USAGE.QUERY_HISTORY
GRANT DATABASE ROLE SNOWFLAKE.READER_USAGE_VIEWER TO ROLE PC_HUNTERS_DB_COLLECTION_ROLE;
GRANT MONITOR USAGE ON ACCOUNT TO ROLE PC_HUNTERS_DB_COLLECTION_ROLE;
-- To query the tables an active warehouse in session is required
GRANT USAGE ON WAREHOUSE WH TO ROLE PC_HUNTERS_DB_COLLECTION_ROLE;

📘 Why do you need this role?
ACCOUNTADMIN is mandatory because the integration requires a role with manage privileges to the shared SNOWFLAKE db. Learn more here.

Create a user for Hunters to query the database.
💡Tip
If you would like to use an existing user and grant them the new role created in step 1, skip this step and go directly to step 3.
Make sure the user can be logged in with user name and password as the login method and make sure you have access to those details.
```
-- Create a user for hunters to use when querying the database
CREATE OR REPLACE USER PC_HUNTERS_DB_COLLECTION_USER PASSWORD = 'replace with a safer password';
```

Grant the role created in step 1 to the user created (or chosen) in step 2.

GRANT ROLE PC_HUNTERS_DB_COLLECTION_ROLE TO USER PC_HUNTERS_DB_COLLECTION_USER;

Gather the following details to be used in the next step:
- Snowflake Account - use your account identifier. Note that the account identifier does not include the snowflakecomputing.com suffix. For example, if your Snowflake console URL is john-smith.snowflakecomputing.com, you should enter only john-smith.
  💡Tip
  If you're not sure what your Snowflake account identifier is, use SELECT CURRENT_ACCOUNT() in Snowflake to retrieve it.
- User name
- Password
- Warehouse name - an active warehouse in session is required when querying.

Step 2: Connect data source on Hunters

Complete the process on the Hunters platform, following this guide.

💡Upon connecting the data source
When you're setting up Snowflake on Hunters, note the following:
User, Password, Account, Warehouse, Role - If you are self hosted, use the credentials configured in step 1. Otherwise use your Reader Account credentials.
Is Reader Account - If you are self hosted, keep as the default false, otherwise change to true.
Database - If you are self hosted, keep this field empty (the DB that will be used is Snowflake), otherwise supply the relevant DB.

📘 Troubleshoot Authentication Error
If you've received an Authentication Error while trying to set up the Query History and Login History data types, it's most likely because your Snowflake is Hunters hosted.
In that case, you have no privileges to the non-reader account data types in the Snowflake integration. Turn off these data types for the connection to succeed.

Expected format

In case Snowflake events are already being collected on your environment, it is possible to ship them to Hunters via a shared storage such as AWS S3. Below are the expected formats per data type, in JSON:

Snowflake Login History sample

{"EVENT_ID":1999078362042,"EVENT_TIMESTAMP":1699858922614,"EVENT_TYPE":"event_type1","USER_NAME":"username1","CLIENT_IP":"12.123.123.123","REPORTED_CLIENT_TYPE":"Type1","REPORTED_CLIENT_VERSION":"","FIRST_AUTHENTICATION_FACTOR":"RSA_KEYPAIR","SECOND_AUTHENTICATION_FACTOR":null,"IS_SUCCESS":"YES","ERROR_CODE":null,"ERROR_MESSAGE":null,"RELATED_EVENT_ID":0.0,"CONNECTION":null}

Snowflake Reader Account Login History sample

{"READER_ACCOUNT_NAME":"ABC12345","EVENT_ID":"54685712345","EVENT_TIMESTAMP":"1687208220882","EVENT_TYPE":"event_type","USER_NAME":"username1","CLIENT_IP":"12.123.123.123","REPORTED_CLIENT_TYPE":"type1","REPORTED_CLIENT_VERSION":"2.9.0","FIRST_AUTHENTICATION_FACTOR":"PASSWORD","SECOND_AUTHENTICATION_FACTOR":null,"IS_SUCCESS":"YES","ERROR_CODE":null,"ERROR_MESSAGE":null,"RELATED_EVENT_ID":"0","READER_ACCOUNT_DELETED_ON":null}

Snowflake Query History sample

{"QUERY_ID": "01a02bcd-0304-e56f-0007-089001d0f22a", "QUERY_TEXT": "SELECT MAX(metadata$insertion_time) FROM (example)", "DATABASE_ID": 5.0, "DATABASE_NAME": "STAG_DB", "SCHEMA_ID": 11.0, "SCHEMA_NAME": "RAW", "QUERY_TYPE": "SELECT", "SESSION_ID": 11220783641022330, "USER_NAME": "STAG_LINK", "ROLE_NAME": "STAG_INVESTIGATION_ENGINE", "WAREHOUSE_ID": 40.0, "WAREHOUSE_NAME": "SAMPLE_WH", "WAREHOUSE_SIZE": null, "WAREHOUSE_TYPE": "STANDARD", "CLUSTER_NUMBER": null, "QUERY_TAG": "{\"service_name\":\"samplebolt\",\"organization\":\"stag\",\"processor_id\":\"sample_id\",\"processor_type\":\"global\",\"mode\":\"prod\"}", "EXECUTION_STATUS": "SUCCESS", "ERROR_CODE": null, "ERROR_MESSAGE": null, "START_TIME": 1699887143693, "END_TIME": 1699887143723, "TOTAL_ELAPSED_TIME": 30.0, "BYTES_SCANNED": 0.0, "PERCENTAGE_SCANNED_FROM_CACHE": 0.0, "BYTES_WRITTEN": 0.0, "BYTES_WRITTEN_TO_RESULT": 0.0, "BYTES_READ_FROM_RESULT": 0.0, "ROWS_PRODUCED": 1.0, "ROWS_INSERTED": 0.0, "ROWS_UPDATED": 0.0, "ROWS_DELETED": 0.0, "ROWS_UNLOADED": 0.0, "BYTES_DELETED": 0.0, "PARTITIONS_SCANNED": 0.0, "PARTITIONS_TOTAL": 0.0, "BYTES_SPILLED_TO_LOCAL_STORAGE": 0.0, "BYTES_SPILLED_TO_REMOTE_STORAGE": 0.0, "BYTES_SENT_OVER_THE_NETWORK": 0.0, "COMPILATION_TIME": 29.0, "EXECUTION_TIME": 1.0, "QUEUED_PROVISIONING_TIME": 0.0, "QUEUED_REPAIR_TIME": 0.0, "QUEUED_OVERLOAD_TIME": 0.0, "TRANSACTION_BLOCKED_TIME": 0.0, "OUTBOUND_DATA_TRANSFER_CLOUD": null, "OUTBOUND_DATA_TRANSFER_REGION": null, "OUTBOUND_DATA_TRANSFER_BYTES": 0.0, "INBOUND_DATA_TRANSFER_CLOUD": null, "INBOUND_DATA_TRANSFER_REGION": null, "INBOUND_DATA_TRANSFER_BYTES": 0.0, "LIST_EXTERNAL_FILES_TIME": 0.0, "CREDITS_USED_CLOUD_SERVICES": 4e-06, "RELEASE_VERSION": "7.40.0", "EXTERNAL_FUNCTION_TOTAL_INVOCATIONS": 0.0, "EXTERNAL_FUNCTION_TOTAL_SENT_ROWS": 0.0, "EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS": 0.0, "EXTERNAL_FUNCTION_TOTAL_SENT_BYTES": 0.0, "EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES": 0.0, "QUERY_LOAD_PERCENT": null, "IS_CLIENT_GENERATED_STATEMENT": false, "QUERY_ACCELERATION_BYTES_SCANNED": 0.0, "QUERY_ACCELERATION_PARTITIONS_SCANNED": 0.0, "QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR": 0.0, "TRANSACTION_ID": 0.0, "CHILD_QUERIES_WAIT_TIME": 0.0, "ROLE_TYPE": "ROLE", "QUERY_HASH": "10c71bc4e22e58d0108197baa4e2323c", "QUERY_HASH_VERSION": 2.0, "QUERY_PARAMETERIZED_HASH": "10c71bc4e22e58d0108197baa4e2323c", "QUERY_PARAMETERIZED_HASH_VERSION": 1.0}

Snowflake Reader Account Query History sample

{ "READER_ACCOUNT_NAME": "ABC12343", "QUERY_ID": "01aa2000-1111-8d90-1234-12340001", "QUERY_TEXT": "grant usage on schema TEST_DB.raw to role TEST_readonly;", "QUERY_TYPE": "GRANT", "SESSION_ID": "1.00111E+11", "USER_NAME": "TEST_READER_ADMIN", "ROLE_NAME": "ADMIN", "SCHEMA_ID": null, "SCHEMA_NAME": null, "DATABASE_ID": 1, "DATABASE_NAME": "SNOWFLAKE", "WAREHOUSE_ID": "12345", "WAREHOUSE_NAME": "COMPUTE_SERVICE_WH_SNOWFLAKEDB_UPGRADE_POOL_XLARGE_0", "WAREHOUSE_SIZE": "X-Large", "WAREHOUSE_TYPE": "STANDARD", "CLUSTER_NUMBER": "2", "QUERY_TAG": null, "EXECUTION_STATUS": "SUCCESS", "ERROR_CODE": "1089", "ERROR_MESSAGE": "Uncaught exception type", "START_TIME": "1695948860279", "END_TIME": "1695948860360", "TOTAL_ELAPSED_TIME": "114", "BYTES_SCANNED": "0", "ROWS_PRODUCED": null, "COMPILATION_TIME": "100", "EXECUTION_TIME": "9", "QUEUED_PROVISIONING_TIME": "0", "QUEUED_REPAIR_TIME": "0", "QUEUED_OVERLOAD_TIME": "0", "TRANSACTION_BLOCKED_TIME": "0", "OUTBOUND_DATA_TRANSFER_CLOUD": null, "OUTBOUND_DATA_TRANSFER_REGION": null, "OUTBOUND_DATA_TRANSFER_BYTES": "0", "INBOUND_DATA_TRANSFER_CLOUD": null, "INBOUND_DATA_TRANSFER_REGION": null, "INBOUND_DATA_TRANSFER_BYTES": "0", "LIST_EXTERNAL_FILES_TIME": "0", "CREDITS_USED_CLOUD_SERVICES": "0.000001", "READER_ACCOUNT_DELETED_ON": null }