Audir information

• New persopn

  SSCarpal Tunnel

  Points: 4019

  More actions

  May 4, 2016 at 3:03 am

  #307352

  Hi All,

  I am capturing the audit information through SQL audit, I want to replicate the audit information in to one table. For that I have created table using below query.

  CREATE TABLE [dbo].[Audit_rows](

  [event_time] [datetime2](7) NULL,

  [sequence_number] [int] NULL,

  [action_id] [varchar](4) NULL,

  [succeeded] [bit] NOT NULL,

  [permission_bitmask] [bigint] NOT NULL,

  [is_column_permission] [bit] NOT NULL,

  [session_id] [smallint] NOT NULL,

  [server_principal_id] [int] NULL,

  [database_principal_id] [int] NULL,

  [target_server_principal_id] [int] NULL,

  [target_database_principal_id] [int] NULL,

  [object_id] [bigint] NULL,

  [class_type] [varchar](10) NULL,

  [session_server_principal_name] [nvarchar](100) NULL,

  [server_principal_name] [nvarchar](100) NULL,

  [server_principal_sid] [nvarchar](100) NULL,

  [database_principal_name] [nvarchar](100) NULL,

  [target_server_principal_name] [nvarchar](100) NULL,

  [target_server_principal_sid] [nvarchar](100) NULL,

  [target_database_principal_name] [nvarchar](100) NULL,

  [server_instance_name] [nvarchar](100) NULL,

  [database_name] [nvarchar](100) NULL,

  [schema_name] [nvarchar](100) NULL,

  [object_name] [nvarchar](100) NULL,

  [statement] [nvarchar](max) NULL,

  [additional_information] [nvarchar](500) NULL,

  [file_name] [nvarchar](500) NULL,

  [audit_file_offset] [bigint] NULL,

  [user_defined_event_id] [int] NULL,

  [user_defined_information] [nvarchar](100) NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  And importing the data in to table using below query.

  INSERT INTO db_name.dbo.Audit_rows

  SELECT * FROM fn_get_audit_file('FileName_WithFullPath', default, default)

  Here if we are executing the above query through joba nd proper schedule it will capture the information from particular file only.

  How can we implement whenever we are executing the job it should load the data which is not present in the database and needs to take the latest files as well.

  Please suggest