Zenn
❄️

【Snowflake】account_usageからIPごとの最終アクセスをよしなに見たいと思ったあなたへ

2024/09/05に公開
SQL
Snowflake
tech

Snowflakeを使っていて定期的に

  • アクセス元のIP確認
  • 最終アクセス

を確認する機会があると思います。

そんなときにサクッとコピペで使えるクエリがあると楽だなと思い
個々に書き残しておきます。

このクエリを使うと

  • 最終のアクセス履歴を用いたSnowflakeの棚卸し
  • 変なIPでユーザがログインしてないかの確認

が可能になります。

ぜひ活用してください。

クエリを動かす前提

  • account_usageにアクセス出来る権限
  • Warehouseが利用できる状態

SQL

今回のSQLでは下記の条件を加えています。

  • 過去3ヶ月のアクセス履歴
  • すべてのユーザを検索
  • すべてのIPを検索

お使いのご要件に合わせて編集してください。

select
    query_history.user_name
    ,max(query_history.end_time) as last_access
    ,login_history.client_ip
from
    snowflake.account_usage.query_history
left join snowflake.account_usage.sessions
    on query_history.session_id = sessions.session_id
    and query_history.user_name = sessions.user_name
left join snowflake.account_usage.login_history
    on sessions.login_event_id = login_history.event_id
where
    /* 過去3ヶ月間におけるSnowflakeへのアクセス履歴に絞る */
    to_date(query_history.end_time) > dateadd('months', -3, current_timestamp())
group by
    query_history.user_name
    ,login_history.client_ip
/* IPやユーザで絞りを掛けたい場合用いる */
having
    login_history.client_ip like '%'
    and query_history.user_name like '%'
order by last_access desc
;

出力例

USER_NAME LAST_ACCESS CLIENT_IP
hoge YYYY-MM-DD HH:MM:SS xx.xx.xx.xx
fuga YYYY-MM-DD HH:MM:SS yy.yy.yy.yy
hoge YYYY-MM-DD HH:MM:SS zz.zz.zz.zz

Discussion