THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Server Migration Checklist II

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
    
Published Thursday, April 22, 2010 11:18 PM by merrillaldrich
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement