snowflake_login_history table | Snowflake plugin

turbot/snowflake

Overview

18Tables

Versions

Mods

GitHub

steampipe plugin install snowflakesteampipe plugin install snowflake

snowflake_account_grant snowflake_account_parameter snowflake_database snowflake_database_grant snowflake_login_history snowflake_network_policy snowflake_resource_monitor snowflake_role snowflake_role_grant snowflake_schemata snowflake_session snowflake_session_policy snowflake_user snowflake_user_grant snowflake_view snowflake_view_grant snowflake_warehouse snowflake_warehouse_metering_history

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_timestamp
from
  snowflake_login_history;

select
  user_name,
  first_authentication_factor,
  is_success,
  event_timestamp
from
  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_factor
from
  snowflake_login_history
where
  is_success = 'YES'
  and event_timestamp > now() - interval '30 days'
order by
  user_name;

select
  distinct user_name,
  first_authentication_factor
from
  snowflake_login_history
where
  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, e.g. connection_name.
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.
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