Beschreibung der Prozedur

Die Prozedur dbo.sp_ReadErrorLogFiltered dient als moderne, sichere und flexible Ablösung der veralteten xp_readerrorlog-Aufrufe. Sie bietet:

• Unterstützung für SQL Server 2012 und höher (durch Verwendung von sys.sp_readerrorlog).

• Vordefinierte Ereignistypen: login, backup, spn oder all für eigene Suchbegriffe.

• Statusfilterung (failed, success) – speziell für Logins und Backups.

• Zeitraumfilter über @startDate und @endDate (beliebig kombinierbar).

• Auswahl des Log-Archivs über @logArchiveNo (0 = aktuelles Log, höhere Zahlen = ältere Archive).

• Freie Textsuche über @searchText1 und @searchText2 (UND-Verknüpfung) für maximale Flexibilität.

• Fehlerbehandlung – fängt Fehler beim Lesen des Logs ab und gibt sie weiter.

• Keine Änderung von Serverkonfigurationen – im Gegensatz zu xp_cmdshell oder xp_regread ist diese Prozedur rein lesend und sicher.

Typische Einsatzbereiche:

• Tägliche Überwachung von fehlgeschlagenen Anmeldeversuchen (Security-Audit).

• Prüfung, ob nächtliche Backups erfolgreich waren.

• Analyse von Kerberos/SPN-Problemen.

• Allgemeine Fehlersuche im Fehlerprotokoll, z. B. nach Deadlocks, Fehlern 823, 824 usw.

Hinweis: Die Prozedur verwendet eine temporäre Tabelle, um die Logdaten zwischenzuspeichern und nach Datum zu filtern. Dies ist auch bei sehr großen Logdateien performant, da sp_readerrorlog bereits die Hauptfilterung (nach Suchtexten) vornimmt.

CREATE OR ALTER PROCEDURE dbo.sp_ReadErrorLogFiltered
    @eventType      NVARCHAR(20)   = 'all',      -- 'login', 'backup', 'spn', 'all'
    @status         NVARCHAR(10)   = NULL,       -- 'failed', 'success', NULL (alle)
    @startDate      DATETIME       = NULL,       -- Beginn des Zeitraums
    @endDate        DATETIME       = NULL,       -- Ende des Zeitraums
    @logArchiveNo   INT            = 0,          -- 0 = aktuelles Log, 1 = ältestes Archiv, usw.
    @searchText1    NVARCHAR(255)  = NULL,       -- zusätzlicher freier Suchtext (z.B. 'error')
    @searchText2    NVARCHAR(255)  = NULL        -- zweiter Suchtext (UND-Verknüpfung)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @search1 NVARCHAR(255),
            @search2 NVARCHAR(255),
            @logType INT = 1;                   -- 1 = SQL Server Log, 2 = SQL Agent Log

    -- Ereignistyp-spezifische Suchbegriffe festlegen
    IF @eventType = 'login'
    BEGIN
        SET @search1 = 'login';
        IF @status = 'failed' SET @search2 = 'failed';
        ELSE IF @status = 'success' SET @search2 = 'successfully';
        -- sonst kein zweiter Suchbegriff
    END
    ELSE IF @eventType = 'backup'
    BEGIN
        SET @search1 = 'backup';
        IF @status = 'failed' SET @search2 = 'failed';
        ELSE IF @status = 'success' SET @search2 = 'successfully';
    END
    ELSE IF @eventType = 'spn'
    BEGIN
        SET @search1 = 'spn';
        SET @search2 = NULL;   -- SPN-Meldungen haben keinen standardisierten Status
    END
    ELSE IF @eventType = 'all'
    BEGIN
        SET @search1 = @searchText1;
        SET @search2 = @searchText2;
    END
    ELSE
    BEGIN
        RAISERROR('Ungültiger @eventType. Erlaubt: login, backup, spn, all', 16, 1);
        RETURN;
    END

    -- Temporäre Tabelle für die Logausgabe
    CREATE TABLE #logOutput
    (
        LogDate     DATETIME,
        ProcessInfo NVARCHAR(50),
        LogText     NVARCHAR(MAX)
    );

    BEGIN TRY
        INSERT INTO #logOutput
        EXEC sys.sp_readerrorlog 
            @logArchiveNo,
            @logType,
            @search1,
            @search2,
            NULL,           -- @startDate (wird ignoriert, wir filtern nachher)
            NULL,           -- @endDate
            N'desc';        -- Sortierung absteigend (neueste zuerst)
    END TRY
    BEGIN CATCH
        DROP TABLE #logOutput;
        THROW;
    END CATCH

    -- Datumsfilter anwenden (falls angegeben)
    IF @startDate IS NOT NULL
        DELETE FROM #logOutput WHERE LogDate < @startDate;
    IF @endDate IS NOT NULL
        DELETE FROM #logOutput WHERE LogDate > @endDate;

    -- Ergebnis ausgeben
    SELECT 
        LogDate,
        ProcessInfo,
        LogText
    FROM #logOutput
    ORDER BY LogDate DESC;

    DROP TABLE #logOutput;
END;
GO

Beispiele für die Verwendung

1. Fehlgeschlagene Logins der letzten 7 Tage anzeigen

EXEC dbo.sp_ReadErrorLogFiltered
    @eventType = 'login',
    @status = 'failed',
    @startDate = DATEADD(day, -7, GETDATE());

2. Alle erfolgreichen Backups des aktuellen Monats

EXEC dbo.sp_ReadErrorLogFiltered
    @eventType = 'backup',
    @status = 'success',
    @startDate = '2025-04-01',
    @endDate = '2025-04-30';

3. SPN-bezogene Meldungen (egal ob Fehler oder Info)

EXEC dbo.sp_ReadErrorLogFiltered @eventType = 'spn';

4. Eigene freie Suche im Fehlerlog (z. B. nach „deadlock“)

EXEC dbo.sp_ReadErrorLogFiltered
    @eventType = 'all',
    @searchText1 = 'deadlock',
    @startDate = '2025-04-01';

5. Komplettes aktuelles Log 

EXEC dbo.sp_ReadErrorLogFiltered @eventType = 'all';

6. Älteres Log-Archiv durchsuchen (z. B. Archiv Nr. 2)

EXEC dbo.sp_ReadErrorLogFiltered
    @eventType = 'backup',
    @status = 'failed',
    @logArchiveNo = 2;