SQL Server Audit

USE master;
GO
DECLARE @sql nvarchar(max) = N'';

;WITH DBs AS (
  SELECT name
  FROM sys.databases
  WHERE database_id > 4 AND state = 0  -- solo BDs de usuario ONLINE
)
SELECT @sql = STRING_AGG(CONVERT(nvarchar(max), N'
USE ' + QUOTENAME(name) + N';
IF EXISTS (SELECT 1 FROM sys.database_audit_specifications WHERE name = N''Spec_Seguridad_DB'')
BEGIN
    ALTER DATABASE AUDIT SPECIFICATION [Spec_Seguridad_DB] WITH (STATE = OFF);
    DROP DATABASE AUDIT SPECIFICATION [Spec_Seguridad_DB];
    PRINT ''Spec_Seguridad_DB eliminada para recreación.'';
END;
CREATE DATABASE AUDIT SPECIFICATION [Spec_Seguridad_DB]
FOR SERVER AUDIT [Audit_Seguridad]
    ADD (SCHEMA_OBJECT_CHANGE_GROUP)         -- create/alter/drop table/objeto esquema
  , ADD (DATABASE_PRINCIPAL_CHANGE_GROUP)    -- create/alter/drop user/role de BD
  , ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP); -- add/drop miembros de roles BD
ALTER DATABASE AUDIT SPECIFICATION [Spec_Seguridad_DB] WITH (STATE = ON);
PRINT ''Spec_Seguridad_DB habilitada en ' + name + N''';
'), NCHAR(10))
FROM DBs;

EXEC sys.sp_executesql @sql;
PRINT 'Especificaciones de auditoría creadas/habilitadas en todas las BDs de usuario.';
GO

USE Auditoria;
GO
IF OBJECT_ID(N'audit.usp_audit_ingest', N'P') IS NOT NULL
  DROP PROCEDURE audit.usp_audit_ingest;
GO
CREATE PROCEDURE audit.usp_audit_ingest
AS
BEGIN
  SET NOCOUNT ON;

  ;WITH src AS
  (
    SELECT 
      event_time,
      sequence_number,
      action_id,
      succeeded,
      server_principal_name,
      server_instance_name,
      database_name,
      schema_name,
      object_name,
      statement,
      additional_information,
      session_id,
      application_name,
      client_ip
    FROM sys.fn_get_audit_file(N'E:\SQLAudit\Audit_Seguridad_*.sqlaudit', DEFAULT, DEFAULT)
  )
  INSERT INTO audit.AUDIT_EVENTS
  (
    event_time, sequence_number, action_id, succeeded,
    server_principal_name, server_instance_name, database_name,
    schema_name, object_name, statement, additional_information,
    session_id, application_name, client_ip
  )
  SELECT s.*
  FROM src s
  WHERE NOT EXISTS
  (
    SELECT 1
    FROM audit.AUDIT_EVENTS t
    WHERE t.event_time            = s.event_time
      AND t.sequence_number       = s.sequence_number
      AND t.action_id             = s.action_id
      AND ISNULL(t.server_instance_name,'') = ISNULL(s.server_instance_name,'')
  );

  PRINT 'Ingesta de auditoría finalizada.';
END
GO
PRINT 'Procedimiento [audit].[usp_audit_ingest] creado.';
GO

USE msdb;
GO
-- Job
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'Audit - Ingest .sqlaudit to Auditoria')
BEGIN
  EXEC msdb.dbo.sp_add_job
    @job_name = N'Audit - Ingest .sqlaudit to Auditoria',
    @enabled = 1,
    @description = N'Ingresa eventos de auditoría desde archivos .sqlaudit a Auditoria.audit.AUDIT_EVENTS.';
  PRINT 'Job creado.';
END
ELSE
  PRINT 'Job ya existía.';
GO

-- Paso del Job
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobsteps WHERE step_name = N'Run ingest' AND job_id IN (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = N'Audit - Ingest .sqlaudit to Auditoria'))
BEGIN
  EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Audit - Ingest .sqlaudit to Auditoria',
    @step_name = N'Run ingest',
    @subsystem = N'TSQL',
    @database_name = N'Auditoria',
    @command = N'EXEC audit.usp_audit_ingest;',
    @on_success_action = 1,  -- Quit
    @on_fail_action = 2;     -- Quit with failure
  PRINT 'Paso del job creado.';
END
ELSE
  PRINT 'Paso del job ya existía.';
GO

-- Schedule cada 5 minutos
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysschedules WHERE name = N'Cada 5 minutos - Audit Ingest')
BEGIN
  EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Cada 5 minutos - Audit Ingest',
    @freq_type = 4,            -- daily
    @freq_interval = 1,
    @freq_subday_type = 4,     -- minutes
    @freq_subday_interval = 5,
    @active_start_time = 0;    -- 00:00
  PRINT 'Schedule creado.';
END
ELSE
  PRINT 'Schedule ya existía.';
GO

-- Vincular schedule y server
EXEC msdb.dbo.sp_attach_schedule
  @job_name = N'Audit - Ingest .sqlaudit to Auditoria',
  @schedule_name = N'Cada 5 minutos - Audit Ingest';
GO
EXEC msdb.dbo.sp_add_jobserver
  @job_name = N'Audit - Ingest .sqlaudit to Auditoria';
GO
PRINT 'Job programado y habilitado.';
GO

-- Ultimos 30 minutos (TOP 200)
USE Auditoria;
GO
SELECT TOP (200)
       event_time,
       server_principal_name AS login,
       database_name         AS dbname,
       action_id,
       object_name,
       statement
FROM   audit.AUDIT_EVENTS
WHERE  event_time >= DATEADD(MINUTE, -30, SYSUTCDATETIME())
ORDER  BY event_time DESC;

-- Últimos 7 días
USE Auditoria;
GO
SELECT
       event_time,
       server_principal_name AS login,
       database_name         AS dbname,
       action_id,
       object_name,
       statement
FROM   audit.AUDIT_EVENTS
WHERE  event_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
ORDER  BY event_time DESC;