Table: snowflake_login_history - Query Snowflake Login Histories using SQL
Snowflake Login History is a feature within Snowflake that allows you to monitor and track user login events across your Snowflake environment. It provides a comprehensive log of user login activity, including details such as user name, login time, IP address, and more. Snowflake Login History helps you stay informed about the login activities in your Snowflake environment and take appropriate actions when anomalies are detected.
Table Usage Guide
The snowflake_login_history
table provides insights into user login events within Snowflake. As a Security Analyst, explore user-specific login details through this table, including user name, login time, IP address, and more. Utilize it to uncover information about user login activities, such as login frequency, login timings, and the source IP addresses of the logins.
Examples
Basic info
Analyze login history to understand the success rate of user authentications and pinpoint specific instances where the first authentication factor was used. This could be beneficial in assessing the security measures and identifying potential vulnerabilities.
select user_name, first_authentication_factor, is_success, event_timestampfrom snowflake_login_history;
select user_name, first_authentication_factor, is_success, event_timestampfrom snowflake_login_history;
List all authentication methods used in the last 30 days
Explore the variety of successful authentication methods utilized by users in the past month. This can provide insights into user behaviour and security practices, aiding in the enhancement of system security protocols.
select distinct user_name, first_authentication_factorfrom snowflake_login_historywhere is_success = 'YES' and event_timestamp > now() - interval '30 days'order by user_name;
select distinct user_name, first_authentication_factorfrom snowflake_login_historywhere is_success = 'YES' and event_timestamp > datetime('now', '-30 days')order by user_name;
Schema for snowflake_login_history
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
account | text | =, !=, ~~, ~~*, !~~, !~~* | The Snowflake account ID. |
client_ip | text | IP address where the request originated from. | |
error_code | bigint | Error code, if the request was not successful. | |
error_message | text | Error message returned to the user, if the request was not successful. | |
event_id | bigint | = | Internal/system-generated identifier for the login attempt. |
event_timestamp | timestamp with time zone | >, >=, =, <, <= | Time (in the UTC time zone) of the event occurrence. |
event_type | text | Event type, such as LOGIN for authentication events. | |
first_authentication_factor | text | = | Method used to authenticate the user (the first factor, if using multi factor authentication). |
is_success | text | Whether the user's request was successful or not. | |
region | text | The Snowflake region in which the account is located. | |
related_event_id | bigint | Reserved for future use. | |
reported_client_type | text | Reported type of the client software, such as JDBC_DRIVER, ODBC_DRIVER, etc. This information is not authenticated. | |
reported_client_version | text | Reported version of the client software. This information is not authenticated. | |
second_authentication_factor | text | The second factor, if using multi factor authentication, or NULL otherwise. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
user_name | text | = | User associated with this event. |
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_login_history