監査ログのインポート

監査ログをインポートするクエリ

code:sql

-- 監査ログのインポート

-- fn_get_audit_fileのFILEPATHを変える

USE AuditLogs

SET NOCOUNT ON

DECLARE @event_time datetime2

DECLARE @action_id varchar(4)

DECLARE @succeeded bit

DECLARE @server_principal_id int

DECLARE @server_principal_name nvarchar(128)

DECLARE @database_principal_id int

DECLARE @class_type varchar(2)

DECLARE @database_name nvarchar(128)

DECLARE @object_name nvarchar(128)

DECLARE @statement nvarchar(2000)

DECLARE @class_type_desc nvarchar(120)

DECLARE @action_name nvarchar(128)

-- fn_get_audit_file 関数でログを取得。カーソル処理。event_time を+9 時間

DECLARE csr1 cursor for

SELECT DATEADD(HOUR, 9, event_time) event_time, action_id, succeeded, server_principal_id

, server_principal_name, database_principal_id, class_type, database_name, object_name, statement

FROM sys.fn_get_audit_file('C:\ファイルパス\*', default, default)

/* -- 前回インポート以降の差分のみをインポートする場合は、以下のWHERE 句を付加する

WHERE event_time > (SELECT ISNULL( MAX(DATEADD(HOUR, -9, event_time)), '0001/01/01') FROM SQLServerAuditLogs)

*/

OPEN csr1;

FETCH NEXT FROM csr1

INTO @event_time, @action_id, @succeeded, @server_principal_id, @server_principal_name

, @database_principal_id, @class_type, @database_name, @object_name, @statement;

WHILE @@FETCH_STATUS = 0

BEGIN

-- 監査 class_type_desc の取得

SELECT @class_type_desc = class_type_desc FROM sys.dm_audit_class_type_map WHERE class_type = @class_type

-- 監査アクション名の取得

SELECT TOP 1@action_name = name FROM sys.dm_audit_actions WHERE action_id = @action_id

-- ログのINSERT

INSERT SQLServerAuditLogs VALUES (@event_time, @action_id, @action_name, @succeeded, @server_principal_id, @server_principal_name

, @database_principal_id, @class_type, @class_type_desc, @database_name, @object_name, @statement)

FETCH NEXT FROM csr1

INTO @event_time, @action_id, @succeeded, @server_principal_id, @server_principal_name, @database_principal_id

, @class_type, @database_name, @object_name, @statement;

END

CLOSE csr1;

DEALLOCATE csr1

SET NOCOUNT OFF