使用xp_readerrorlog审计失败及成功的登录

USE [master]
GODECLARE @FirstDay DATETIME,@LastDay DATETIME
SET @FirstDay = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, 0);  --默认上月首日
SET @LastDay = GETDATE();  IF OBJECT_ID('tempdb..#TempLog') IS NOT NULLBEGINDROP TABLE #TempLogEND;
IF OBJECT_ID('tempdb..#TempLog2') IS NOT NULLBEGINDROP TABLE #TempLog2END;
IF OBJECT_ID('tempdb..#TempLog3') IS NOT NULLBEGINDROP TABLE #TempLog3END;CREATE TABLE #TempLog (				--创建#TempLog，存放xp_readerrorlog的原始数据LogDate DATETIME,ProcessInfo NVARCHAR(50),TEXT NVARCHAR(2000));CREATE TABLE #TempLog2 (			--创建#TempLog2存放失败的登录[ServerName] NVARCHAR(255),[User] NVARCHAR(255),[IP] NVARCHAR(255),[FailedAttempts] INT,[Month] NVARCHAR(255));CREATE TABLE #TempLog3 (			--创建#TempLog3存放最后成功的登录[ServerName] NVARCHAR(255),[User] NVARCHAR(255),[IP] NVARCHAR(255),[LogDate] NVARCHAR(255));INSERT INTO #TempLog (LogDate, ProcessInfo, TEXT)
EXEC master.dbo.xp_readerrorlog -1, 1, NULL, NULL, @FirstDay, @LastDay;INSERT INTO #TempLog2				--Group failed logins
SELECT @@SERVERNAME AS [ServerName],SUBSTRING(TEXT,(CHARINDEX('''',TEXT)+1),(CHARINDEX('''',TEXT,24)-(CHARINDEX('''',TEXT)+1))) AS [User],SUBSTRING(TEXT,(CHARINDEX(':',TEXT)+2),(CHARINDEX(']',TEXT,25)-(CHARINDEX(':',TEXT)+2))) AS [IP],'1' AS [FailedAttempts],		--Failed login countDATENAME(m,LogDate) AS [Month]
FROM #TempLog
WHERE ProcessInfo = 'Logon'AND TEXT LIKE '%Login failed%'SELECT [ServerName],[User],[IP],COUNT([FailedAttempts]) AS [FailedAttempts],[Month]
FROM #TempLog2
GROUP BY [ServerName], [User], [IP], [Month]
ORDER BY [User];INSERT INTO #TempLog3			--Last successful login per user
SELECT@@SERVERNAME AS [ServerName],SUBSTRING(TEXT,(CHARINDEX('''',TEXT)+1),(CHARINDEX('''',TEXT,28)-(CHARINDEX('''',TEXT)+1))) AS [User],SUBSTRING(TEXT,(CHARINDEX('[',TEXT)+9),(CHARINDEX(']',TEXT,28)-(CHARINDEX('[',TEXT)+9))) AS [IP],LogDate AS [LastLogin]
FROM #TempLog
WHERE ProcessInfo = 'Logon'AND TEXT LIKE '%Login succeeded%'AND TEXT NOT LIKE '%NT AUTHORITY%'AND TEXT NOT LIKE '%Database Mirroring%'
GROUP BY TEXT, LogDate;SELECT[ServerName],[User],[IP],MAX(LogDate) AS [LastLogin]
FROM #TempLog3
GROUP BY [ServerName], [User], [IP];IF OBJECT_ID('tempdb..#TempLog') IS NOT NULLBEGINDROP TABLE #TempLogEND;
IF OBJECT_ID('tempdb..#TempLog2') IS NOT NULLBEGINDROP TABLE #TempLog2END;
IF OBJECT_ID('tempdb..#TempLog3') IS NOT NULLBEGINDROP TABLE #TempLog3END;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！