[Resolvido] Leitura dos arquivos de log

[advaldomesquita 93]

Procedures para leitura dos arquivos de log

sp_enumerrorlogs

sp_readerrorlog 1

 

Fazer uma busca nos arquivos de LOG do SQL SERVER

DECLARE @TSQL  NVARCHAR(2000)
DECLARE @lC    INT


CREATE TABLE #TempLog (
     LogDate     DATETIME,
     ProcessInfo NVARCHAR(50),
     [Text] NVARCHAR(MAX))


CREATE TABLE #logF (
     ArchiveNumber     INT,
     LogDate           DATETIME,
     LogSize           INT
)

INSERT INTO #logF  
EXEC sp_enumerrorlogs
SELECT @lC = MIN(ArchiveNumber) FROM #logF


WHILE @lC IS NOT NULL
BEGIN
     INSERT INTO #TempLog
     EXEC sp_readerrorlog @lC
     SELECT @lC = MIN(ArchiveNumber) FROM #logF
     WHERE ArchiveNumber > @lC
END

 

Somente dados que falharam

SELECT Text,COUNT(Text) Number_Of_Attempts
FROM #TempLog where
Text like '%failed%' and ProcessInfo = 'LOGON'
Group by Text

 

 

dados gerados, gravados e log com sucesso

SELECT Distinct MAX(logdate) last_login,Text

FROM #TempLog

where ProcessInfo = 'LOGON'and Text like '%SUCCEEDED%'

and Text not like '%NT AUTHORITY%'

Group by Text

[/sql/

 

Abçs

 

A.Jr