Vous êtes ici :
Cette requête extrait et consolide les « login failed » depuis les journaux d’erreur (errorlog) de SQL Server.
Login failed consolidated
Transact-SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
IF OBJECT_ID('tempdb..#LogRecords') IS NOT NULL DROP TABLE #LogRecords; CREATE TABLE dbo.#LogRecords ( EventTime DATETIME, MetaText VARCHAR(128), Message NVARCHAR(MAX), ID INT IDENTITY(1, 1) --primary key-- clustered ); IF OBJECT_ID('tempdb..#ErrorLogs') IS NOT NULL DROP TABLE #ErrorLogs; CREATE TABLE #ErrorLogs ( EventTime DATETIME, MetaText VARCHAR(128), Message NVARCHAR(MAX), ID INT IDENTITY(1, 1) --primary key-- clustered ); DECLARE @CurrentLog SMALLINT; DECLARE @LogSize BIGINT; IF OBJECT_ID('tempdb..#EnumErrorLogs') IS NOT NULL DROP TABLE #EnumErrorLogs; CREATE TABLE dbo.#EnumErrorLogs ( ArchiveNo INT, CreateDate NVARCHAR(128), Size INT ); INSERT dbo.#EnumErrorLogs EXEC sp_enumerrorlogs; DECLARE elc CURSOR FOR SELECT ArchiveNo,Size FROM #EnumErrorLogs ELT --WHERE ArchiveNo < 4 -- limite le nombre lus ORDER BY ArchiveNo; OPEN elc; FETCH NEXT FROM elc INTO @CurrentLog,@LogSize; WHILE @@FETCH_STATUS = 0 BEGIN TRUNCATE TABLE #LogRecords; INSERT #LogRecords (EventTime, MetaText, Message) EXEC xp_readerrorlog @CurrentLog, 1, N'Login failed'; INSERT #ErrorLogs SELECT EventTime, MetaText, Message FROM #LogRecords ORDER BY ID DESC FETCH NEXT FROM elc INTO @CurrentLog,@LogSize; END; CLOSE elc; DEALLOCATE elc; SELECT COUNT(*) AS countseen, MIN(EventTime) AS firstseen, MAX(EventTime) AS lastseen,Message FROM #ErrorLogs GROUP BY Message ORDER BY lastseen DESC GO |
Table of Contents