How to Export Snowflake Logs for Security Investigations

· Original article ↗

Summary

The guide explains how to export Snowflake audit logs as JSON or JSONL, check login history for suspicious activity, and follow sessions into query history. It also covers access, latency, and export troubleshooting.

Key points

  • For small exports, query Snowflake ACCOUNT_USAGE views with SnowSQL and JSON output; for larger exports, use COPY INTO a stage and download the files with GET.
  • ACCOUNT_USAGE views retain a year of history but can lag by hours; INFORMATION_SCHEMA.LOGIN_HISTORY can provide the last seven days without that lag.
  • Check for bursts of failed logins, password logins without a second authentication factor, and unfamiliar client IPs or client types.
  • Follow suspicious login sessions into QUERY_HISTORY to see what was read.
  • The guide says ACCOUNT_USAGE access may require ACCOUNTADMIN, IMPORTED PRIVILEGES, or an appropriate database role; queries and unloads also require a running warehouse.
  • The article directs readers to upload the JSONL file to logtotal[.]com, but gives no details about the subsequent sanitization or review steps.

Article Details

Defense Focus
Export and review Snowflake login and query history to investigate suspicious account access.
Detection Methods
  • Look for bursts of failed logins.
  • Identify password logins with an empty SECOND_AUTHENTICATION_FACTOR field.
  • Review unfamiliar client IP addresses and client types.
  • Follow suspicious login sessions into QUERY_HISTORY to examine what was read.
Data Sources
  • SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
  • SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  • INFORMATION_SCHEMA.LOGIN_HISTORY
  • Security tables and event tables in the user's own Snowflake environment
Platforms
  • Snowflake
  • LogTotal
Defensive Actions
  • Use ACCOUNT_USAGE for historical login data, accounting for its delay of up to a couple of hours.
  • Use INFORMATION_SCHEMA.LOGIN_HISTORY for recent events from the last seven days when lower latency is needed.
  • Export smaller datasets as JSON with SnowSQL; for larger exports, unload JSON to a stage with COPY INTO and retrieve the files with GET.
  • Filter exports by time to limit query and warehouse costs.
  • Ensure the querying role has access to ACCOUNT_USAGE through IMPORTED PRIVILEGES or an appropriate database role.
  • Upload the exported JSONL file to LogTotal for review.

MITRE ATT&CK

Vendors

Products

Related Articles