The returned files are restricted by the hidden $path column to match the connectionlog entries. To access the external tables, create views in a database with the WITH NO SCHEMA BINDING option: CREATE VIEW audit_logs_views.v_connections_log AS Create a local schema to view the audit logs: create schema audit_logs_viewsĤ. LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region’ ģ. LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region' Ĭreate a user log table: create external table s_audit_log.user_log( Iamauthguid varchar(50), application_name varchar(300)) Sslcompression varchar(70), sslexpansion varchar(70), Username varchar(60), authmethod varchar(60), Remotehost varchar(60), remoteport varchar(60), 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region'Ĭreate a connection log table: CREATE EXTERNAL TABLE s_audit_nnections_log(Įvent varchar(60), recordtime varchar(60), Note: In the following examples, replace bucket_name, your_account_id, and region with your bucket name, account ID, and AWS Region.Ĭreate a user activity logs table: create external table s_audit_er_activity_log( For role_name, specify the IAM role attached to your Amazon Redshift cluster. Replace your_account_number to match your real account number. Iam_role 'arn:aws:iam::your_account_number:role/role_name' create external database if not exists Create an external schema: create external schema s_audit_logs To query your audit logs in Redshift Spectrum, follow these steps:ġ. Then, use the hidden $path column and regex function to create views that generate the rows for your analysis. To query your audit logs in Redshift Spectrum, create external tables, and then configure them to point to a common folder (used by your files). Associate the IAM role to your Amazon Redshift cluster. Create an AWS Identity and Access Management (IAM) role.ģ. Note: It might take some time for your audit logs to appear in your Amazon Simple Storage Service (Amazon S3) bucket.Ģ. Before you use Redshift Spectrum, complete the following tasks:
0 Comments
Leave a Reply. |