Easy Breezy Login Audit for your Ol’ 2000 Server
In the last post on this topic I put up the preparatory steps I’ve been using for server migrations. Here I am posting some code that has worked well for us to trace who/what is connecting to our older SQL Server 2000 machines. It’s a simple audit of login events, tracing the login name, host name, database, and last login time for connections to the server, and gave us valuable insight into who was really using the machines and which databases might be candidates to retire.
First we created a rolling trace on the older servers that would restart at midnight each night. The trace gathers basic login information and outputs it to one file per day. I let this run for about a month on the older servers. Next, I wrote a merge that would grab all the distinct combinations of host name, login name, application and so on from the whole set of trace files and load those into a summary table. Then we just waited a month, imported the results, voila!
If you try this, keep in mind the usual caveats with Profiler: be careful not to fill the disk, and watch out for performance issues.
The summary table
CREATE TABLE dbo.loginTraceSample (
HostName nvarchar(128) NULL,
ApplicationName nvarchar(128) NULL,
NTUserName nvarchar(128) NULL,
LoginName nvarchar(128) NULL,
DatabaseID int NULL,
Success int NULL,
lastLogin datetime NULL
)
The trace definition
/*
Makes a 24 hour trace of all logins with client machine information.
Rolls previous trace, if any exists, so that this job can
be repeated to create fresh trace files daily or hourly.
Must be run at least 1 minute apart to avoid output file name
collisions.
Merrill Aldrich 3/3/2010
*/
DECLARE @rc int
DECLARE @TraceID int
DECLARE @maxfilesize bigint
DECLARE @stoptime datetime
DECLARE @tracefileprefix nvarchar(1000)
DECLARE @tracefile nvarchar(1000)
SET @maxfilesize = 100
SET @stoptime = DATEADD(hour, 24, GETDATE())
SET @tracefileprefix = N'L:\SQLTrace\MYSERVER_Logins_'
-- Append datestamp with minute resolution
SET @tracefile = @tracefileprefix
+ REPLACE(REPLACE(REPLACE(CONVERT(nvarchar(17), GETDATE(), 121), ':', ''),
'-', ''), ' ', '')
-- See if this trace exists, and stop/clear it if it does
SET @TraceID = ( SELECT TOP 1 traceid
FROM ::FN_TRACE_GETINFO(NULL)
WHERE property = 2
AND CAST(value AS nvarchar(245))
LIKE @tracefileprefix + '%'
)
SELECT @TraceID
WHILE @TraceID IS NOT NULL
BEGIN
-- Stop existing trace
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 0
-- Close and delete existing trace
EXEC sp_trace_setstatus @traceid = @TraceID, @status = 2
-- See if there's another trace with this file name
SET @TraceID = ( SELECT TOP 1 traceid
FROM ::FN_TRACE_GETINFO(NULL)
WHERE property = 2
AND CAST(value AS nvarchar(245))
LIKE @tracefileprefix + '%'
)
END
-- Create a new, blank trace
EXEC @rc = sp_trace_create @TraceID OUTPUT, @options = 0,
@tracefile = @tracefile, @maxfilesize = @maxfilesize,
@stoptime = @stoptime
IF ( @rc != 0 )
GOTO error
-- Set the trace events
DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @TraceID, 14, 3, @on
EXEC sp_trace_setevent @TraceID, 14, 6, @on
EXEC sp_trace_setevent @TraceID, 14, 8, @on
EXEC sp_trace_setevent @TraceID, 14, 10, @on
EXEC sp_trace_setevent @TraceID, 14, 11, @on
EXEC sp_trace_setevent @TraceID, 14, 12, @on
EXEC sp_trace_setevent @TraceID, 14, 14, @on
EXEC sp_trace_setevent @TraceID, 14, 23, @on
EXEC sp_trace_setevent @TraceID, 14, 35, @on
-- Set the trace Filters
DECLARE @intfilter int
DECLARE @bigintfilter bigint
-- Set the trace to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future reference
-- select TraceID=@TraceID
GOTO finish
error:
SELECT ErrorCode = @rc
finish:
GO
The above script was created by making a trace definition in Profiler, then saving out the script that Profiler generates to run a server-side trace. We added a little logic to make the traces safely, automatically re-startable. Finally, the script was incorporated into a SQL Agent job that would run every night at midnight, kicking off a new trace to a new, date-stamped file. That’s mainly useful because you can’t select from the trace file for a running trace, and this rolling-file method makes all the data from prior days available by closing the files and starting new ones. It’s possible to adjust the time span as needed if you wanted smaller, more frequent files or were constrained for disk space.
Summarizing for the Last Login
Lastly, this script can read and summarize a whole set of the trace files using the summary table:
DECLARE @dateToProcess datetime
SET @dateToProcess = '2010-03-04'
DECLARE @traceFile varchar(1000)
WHILE @dateToProcess <= '2010-04-21'
BEGIN
SET @traceFile = 'L:\SQLTrace\MYSERVER_Logins_'
+ CONVERT(varchar(8), @dateToProcess, 112) + '0000.trc'
PRINT @tracefile
INSERT INTO loginTraceSample
( HostName,
ApplicationName,
NTUserName,
LoginName,
DatabaseID,
Success,
lastLogin
)
SELECT HostName,
ApplicationName,
NTUSerName,
LoginName,
DatabaseID,
Success,
MAX(StartTime) AS lastLogin
FROM ::FN_TRACE_GETTABLE(@traceFile, NULL) trace
WHERE ApplicationName NOT LIKE 'SQLAgent%'
AND NOT EXISTS ( SELECT 1
FROM loginTraceSample existing
WHERE ( existing.hostname = trace.hostname
OR ( existing.hostname IS NULL
AND trace.hostname IS NULL
)
)
AND existing.ApplicationName = trace.ApplicationName
AND ( existing.NTUSerName = trace.NTUSerName
OR ( existing.NTUserName IS NULL
AND trace.NTUserName IS NULL
)
)
AND existing.LoginName = trace.LoginName
AND existing.DatabaseID = trace.DatabaseID
AND existing.Success = trace.Success )
GROUP BY HostName,
ApplicationName,
NTUSerName,
LoginName,
DatabaseID,
Success
UPDATE loginTraceSample
SET lastLogin = COALESCE(( SELECT MAX(StartTime)
FROM ::FN_TRACE_GETTABLE(@traceFile,
NULL) trace
WHERE ( loginTraceSample.hostname = trace.hostname
OR ( loginTraceSample.hostname IS NULL
AND trace.hostname IS NULL
)
)
AND loginTraceSample.ApplicationName = trace.ApplicationName
AND ( loginTraceSample.NTUSerName = trace.NTUSerName
OR ( loginTraceSample.NTUserName IS NULL
AND trace.NTUserName IS NULL
)
)
AND loginTraceSample.LoginName = trace.LoginName
AND loginTraceSample.DatabaseID = trace.DatabaseID
AND loginTraceSample.Success = trace.Success
), lastLogin)
SET @dateToProcess = DATEADD(DAY, 1, @dateToProcess)
END