如何使用 Amazon Redshift Spectrum 分析我的审计日志?

上次更新时间:2020 年 8 月 19 日

我想使用 Amazon Redshift Spectrum 分析我的审计日志。如何查询审计日志?

简短描述

在开始使用 Redshift Spectrum 之前,请务必完成以下任务:

1.    启用审计日志

注意:您的审计日志可能需要一些时间才能显示在您的 Amazon Simple Storage Service (Amazon S3) 存储桶中。

2.    创建 AWS Identity and Access Management (IAM) 角色。

3.    将 IAM 角色与 Amazon Redshift 集群相关联。

要在 Redshift Spectrum 中查询您的审计日志,请创建外部表,并将其配置为指向公用文件夹(由您的文件使用)。然后,使用隐藏的 $path 列和正则表达式函数创建视图,生成供分析所用的行。

解决方法

要在 Redshift Spectrum 中查询您的审计日志,请执行以下步骤:

1.    创建外部架构:

create external schema s_audit_logs 
from data catalog 
database 'audit_logs' 
iam_role 'arn:aws:iam::your_account_number:role/role_name' create external database if not exists

替换 your_account_number 以与您的真实账号匹配。对于 role_name,请指定附加到您的 Amazon Redshift 集群的 IAM 角色。

2.    创建外部表

create external table s_audit_logs.user_activity_log(
    logrecord varchar(max)
)
 ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region'

在本例中,您将创建用户活动日志表。替换 bucket_nameyour_account_id区域,以与您的实际存储桶名称、账户 ID 和区域匹配。

3.    创建连接日志表:

CREATE EXTERNAL TABLE s_audit_logs.connections_log(
  event varchar(60),  recordtime varchar(60), 
  remotehost varchar(60),  remoteport varchar(60), 
  pid int,  dbname varchar(60), 
  username varchar(60),  authmethod varchar(60), 
  duration int,  sslversion varchar(60), 
  sslcipher varchar(150),  mtu int, 
  sslcompression varchar(70),  sslexpansion varchar(70), 
  iamauthguid varchar(50),  application_name varchar(300))
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region';

替换 bucket_nameyour_account_id区域,以与您的实际存储桶名称、账户 ID 和区域匹配。

4.    创建本地架构以查看审计日志:

create schema audit_logs_views;

5.    在数据库中创建视图(使用无架构绑定选项)以访问外部表:

CREATE VIEW audit_logs_views.v_connections_log AS
select *
FROM s_audit_logs.connections_log
WHERE "$path" like '%connectionlog%'
with no schema binding;

返回的文件由隐藏的 $path 列限制,以匹配 connectionlog 条目。

在下面的示例中,隐藏的 $path 列和正则表达式函数用于限制为 v_connections_log 返回的文件:

CREATE or REPLACE VIEW audit_logs_views.v_useractivitylog AS
SELECT    logrecord,
          substring(logrecord,2,24) as recordtime,
          replace(regexp_substr(logrecord,'db=[^" "]*'),'db=','') as db,
          replace(regexp_substr(logrecord,'user=[^" "]*'),'user=','') AS user,
          replace(regexp_substr(logrecord, 'pid=[^" "]*'),'pid=','') AS pid,
          replace(regexp_substr(logrecord, 'userid=[^" "]*'),'userid=','') AS userid,
          replace(regexp_substr(logrecord, 'xid=[^" "]*'),'xid=','') AS xid,
          replace(regexp_substr(logrecord, '][^*]*'),']','') AS query
   FROM s_audit_logs.user_activity_log
   WHERE "$path" like '%useractivitylog%'
   with no schema binding;

返回的文件与 useractivitylog 条目匹配。

注意:存在与用户活动日志中的多行查询有关的限制。因此,最佳做法是直接查询列日志记录。