Table: snowflake_session - Query Snowflake Sessions using SQL

Snowflake Sessions are an integral part of the Snowflake Data Cloud platform, which allows users to manage and execute SQL queries. Each session represents a connection from a client to the Snowflake service, and it is used to execute SQL statements. The sessions are ephemeral and are automatically terminated after a period of inactivity or when the client disconnects.

Table Usage Guide

The snowflake_session table provides comprehensive insights into Snowflake sessions. As a database administrator or data engineer, you can use this table to explore session-specific details, including user actions, session duration, and associated metadata. This can be particularly useful for monitoring user activity, optimizing session performance, and troubleshooting issues related to session connectivity.

Examples

Basic info

Explore which users have logged into your Snowflake environment and when, allowing you to monitor user activity and understand usage patterns. This information can be particularly useful for auditing and security purposes.

select
  session_id,
  user_name,
  authentication_method,
  created_on,
  client_environment ->> 'APPLICATION' as client_application
from
  snowflake_session;

select
  session_id,
  user_name,
  authentication_method,
  created_on,
  json_extract(client_environment, '$.APPLICATION') as client_application
from
  snowflake_session;

List distinct authentication methods used in the last year

Explore which unique authentication methods have been used by different users in the past year. This can help in understanding user behavior and enhancing security measures.

select
  distinct user_name,
  authentication_method
from
  snowflake_session
order by
  user_name;

select
  distinct user_name,
  authentication_method
from
  snowflake_session
order by
  user_name;

List sessions authenticated without Snowflake MFA with passsword in last 30 days

Explore which user sessions have been authenticated without the use of Snowflake multi-factor authentication (MFA) and with a password in the past 30 days. This query can help identify potential security risks and enforce stricter authentication methods.

select
  distinct user_name,
  authentication_method,
  client_environment ->> 'APPLICATION' as client_application
from
  snowflake_session
where
  split_part(authentication_method, '+', 2) = ''
  and authentication_method like 'Password%'
  and created_on > now() - interval '30 days'
order by
  user_name desc;

Error: SQLite does not support split_part function.

Schema for snowflake_session

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
account	text	=, !=, ~~, ~~, !~~, !~~	The Snowflake account ID.
authentication_method	text	=	The authentication method used to access Snowflake.
client_application_id	text		The identifier for the Snowflake-provided client application used to create the remote session to Snowflake (e.g. JDBC 3.8.7)
client_application_version	text		The version number (e.g. 3.8.7) of the Snowflake-provided client application used to create the remote session to Snowflake.
client_build_id	text		The build number (e.g. 41897) of the third-party client application used to create a remote session to Snowflake, if available. For example, a third-party Java application that uses the JDBC driver to connect to Snowflake.
client_environment	jsonb		The environment variables (e.g. operating system, OCSP mode) of the client used to create a remote session to Snowflake.
client_version	text		The version number (e.g. 47154) of the third-party client application that uses a Snowflake-provided client to create a remote session to Snowflake, if available.
created_on	timestamp with time zone	>, >=, =, <, <=	Date and time when the session was created.
login_event_id	bigint		The unique identifier for the login event.
region	text		The Snowflake region in which the account is located.
session_id	bigint	=	The unique identifier for the current session.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
user_name	text	=	The user name of the user.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- snowflake

You can pass the configuration to the command with the --config argument:

steampipe_export_snowflake --config '<your_config>' snowflake_session