Monitoring out of hours DB access

Recently a client has asked to monitor staff usage of a desktop application out of hours and out of the office. So the specified criteria for tracking db access were:

• Between 17:00 and 09:00 hours on weekdays
• Weekends
• Computers from outside company offices

 The client uses SQL Server 2005 so the two solutions that immediately came to mind were:

• Run a job with a query interrogating the sys.dm_exec_sessions dmv checking for sessions within the specified criteria
• Create a logon trigger that fires at each logon event and populates a table with the required data when a logon event occurs within the specified criteria

In this case I felt that creating a logon trigger would be the more efficient option as it would fire only when a user logged on and would execute the query only during the specified times. Where as a job would need to be scheduled and would run even when not needed, i.e. when no one is accessing the database within the specified times.

A while back I posted about Logon Triggers when I first learned about them. I gave the example found in BOL about limiting the number of connections a user can have at any one time. Since then I haven't had an opportunity to put such triggers into practice. So it was, sadly, quite exciting to finally jump into it.

First I created a table in my AdminDB: 

USE [AdminDB] CREATE TABLE dbo.tblLogonTracking
(
    stblLogonTrackingID int primary key clustered identity (1,1) NOT NULL,
    login_time datetime NOT NULL,
    [host_name] varchar(50) NOT NULL,
    [program_name] varchar(100) NOT NULL,
    [EventData] VARCHAR(15) NULL
) ON [PRIMARY]

Then the trigger itself:

USE [master]
ALTER TRIGGER trLogonTracking
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF    DATEPART(HH, getdate()) NOT BETWEEN 9 AND 17
    OR (DATEPART(DW,GETDATE()) IN (1,7))
    BEGIN
        INSERT INTO AdminDB.dbo.tblLogonTracking (login_time,[program_name],[host_name],[EventData])
        SELECT GETDATE(),APP_NAME(),HOST_NAME(),
        EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)')
        WHERE ORIGINAL_LOGIN() IN ('AppLogin1','AppLogin2')
        AND (APP_NAME() LIKE '%app1_name %' OR APP_NAME() LIKE '%app2_name%')
        AND NOT EXISTS (SELECT 1 FROM AdminDB.dbo.tblLogonTracking as a
                        WHERE DATEDIFF(mi,GETDATE(),a.login_time) > 60
                        AND APP_NAME() = a.[program_name]
                        AND HOST_NAME() = [host_name])
        AND EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)') NOT LIKE '10.1.%'
    END
END

I've created the trigger 'ON ALL SERVER' since I need to use it for several databases/applications. But it could be created just for a single database by running it on the required db and using 'ON DATABASE'. Due to the nature of the applications I've also included a NOT EXISTS statement so I don't record the same user logging in and out successively. The goal here is to know that someone is accessing the database in out of hours periods, not necessarily how many times they are accessing the system. But that should be relatively easy to modify based on requirements.
I also needed to access the IP address of the incoming connection - to filter out tthe company IP range. So I'm using the EVENTDATA() function. Event data is held as XML and therefore an XML statement was required. For more info on EVENTDATA() see MSDN.

With the above there is now a table to be accessed for usage/auditing reports. It would probably be a good idea to archive and/or cleanup the LogonTracking table just to keep this tidy and efficient.

It would be nice to hear what other people have used logonn triggers for as well.