- Between 17:00 and 09:00 hours on weekdays
- Computers from outside company offices
- 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
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.tblLogonTrackingThen the trigger itself:
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]
USE [master]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.
ALTER TRIGGER trLogonTracking
ON ALL SERVER
IF DATEPART(HH, getdate()) NOT BETWEEN 9 AND 17
OR (DATEPART(DW,GETDATE()) IN (1,7))
INSERT INTO AdminDB.dbo.tblLogonTracking (login_time,[program_name],[host_name],[EventData])
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)','NVARCHAR(15)') NOT LIKE '10.1.%'
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.