turbot/snowflake
steampipe plugin install snowflake

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
accounttext=, !=, ~~, ~~*, !~~, !~~*The Snowflake account ID.
authentication_methodtext=The authentication method used to access Snowflake.
client_application_idtextThe identifier for the Snowflake-provided client application used to create the remote session to Snowflake (e.g. JDBC 3.8.7)
client_application_versiontextThe version number (e.g. 3.8.7) of the Snowflake-provided client application used to create the remote session to Snowflake.
client_build_idtextThe 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_environmentjsonbThe environment variables (e.g. operating system, OCSP mode) of the client used to create a remote session to Snowflake.
client_versiontextThe 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_ontimestamp with time zone>, >=, =, <, <=Date and time when the session was created.
login_event_idbigintThe unique identifier for the login event.
regiontextThe Snowflake region in which the account is located.
session_idbigint=The unique identifier for the current session.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
user_nametext=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