Profile SQL Server connections using Event Notification and AUDIT_LOGIN events

en_profile_logons.sql

--
--	Profile connections using Event Notification and AUDIT_LOGIN events
--	to create a summary of number of logins by database, application, login and hostname
--	additionally tracking domain name and user
--
USE DBPerfmon
GO

--	Prerequisites
ALTER DATABASE DBPerfmon SET ENABLE_BROKER;
GO

--	Create Queue to store login events
CREATE QUEUE queue_audit_logins;
GO

--	Create a Service to recevice login events
--	referencing event notification contract
CREATE SERVICE service_audit_logins
	ON QUEUE queue_audit_logins
	([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

--	Create a route to the local service broker
CREATE ROUTE route_audit_logins
WITH SERVICE_NAME = 'service_audit_logins', ADDRESS = 'LOCAL';
GO

--	Create an event notification for AUDIT_LOGIN events
--	dispatched to the service defined in the current database
CREATE EVENT NOTIFICATION audit_logins
ON SERVER
FOR AUDIT_LOGIN
TO SERVICE 'service_audit_logins', 'current database';
GO

--	Table to hold summarized data
--	Note: the primary key is defined for a maximum length of 1024 bytes
--	while the maximum allowed index key is 900 bytes, meaning an insert could
--	result in an error and a lost event, for this reason we ae just building
--	an index to support updates but we are not enforcing uniqueness
CREATE TABLE dbo.audit_logins_summary (
	DatabaseName		sysname		NOT NULL
,	ApplicationName		sysname		NOT NULL
,	LoginName			sysname		NOT NULL
,	HostName			sysname		NOT NULL
,	NTDomainName		sysname		NULL
,	NTUserName			sysname		NULL
,	LastStartTime		datetime	NULL
,	TotalLogins			bigint		NULL
--,	CONSTRAINT	pk_audit_logins_summary
--	PRIMARY KEY	(DatabaseName, ApplicationName, LoginName, HostName)
);
GO
CREATE CLUSTERED INDEX ix_audit_logins_summary_key
	ON	dbo.audit_logins_summary (DatabaseName, ApplicationName, LoginName)
GO

--	Create activation procedure to process queue events
CREATE PROCEDURE dbo.ProcessServerEvents
AS
	SET NOCOUNT ON;
WHILE (1 = 1)    
BEGIN
      DECLARE @messageBody VARBINARY(MAX);      
      DECLARE @messageTypeName NVARCHAR(256);           
      WAITFOR (
		RECEIVE TOP(1)
			@messageTypeName = message_type_name
		,	@messageBody = message_body                    
		FROM	queue_audit_logins                 
		), TIMEOUT 500
    -- If there is no message, exit      
    IF @@ROWCOUNT = 0        
    BEGIN         
        BREAK;
    END
	
	IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
	BEGIN
		DECLARE	@Event				xml
		,		@DatabaseID			smallint
		,		@DatabaseName		sysname
		,		@ApplicationName	sysname
		,		@LoginName			sysname
		,		@HostName			sysname
		,		@NTDomainName		sysname
		,		@NTUserName			sysname
		,		@LastStartTime		datetime;

		SET		@Event = CAST(@messageBody AS xml);

		SELECT	@DatabaseID			= @Event.value('(/EVENT_INSTANCE/DatabaseID)[1]', 'smallint')
		,		@DatabaseName		= ISNULL(NULLIF(DB_NAME(@DatabaseID), ''), 'Unknown')
		,		@ApplicationName	= ISNULL(NULLIF(@Event.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'sysname'), ''), 'Unknown')
		,		@LoginName			= ISNULL(NULLIF(@Event.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'), ''), 'Unknown')
		,		@HostName			= ISNULL(NULLIF(@Event.value('(/EVENT_INSTANCE/HostName)[1]', 'sysname'), ''), 'Unknown')
		,		@NTDomainName		= NULLIF(@Event.value('(/EVENT_INSTANCE/NTDomainName)[1]', 'sysname'), '')
		,		@NTUserName			= NULLIF(@Event.value('(/EVENT_INSTANCE/NTUserName)[1]', 'sysname'), '')
		,		@LastStartTime		= @Event.value('(/EVENT_INSTANCE/StartTime)[1]', 'datetime');

		MERGE INTO dbo.audit_logins_summary AS T1
		USING	(VALUES (@DatabaseName, @ApplicationName, @LoginName, @HostName, @NTDomainName, @NTUserName, @LastStartTime))
				AS T2 (DatabaseName, ApplicationName, LoginName, HostName, NTDomainName, NTUserName, LastStartTime)
		ON		T1.DatabaseName = T2.DatabaseName 
			AND	T1.ApplicationName = T2.ApplicationName
			AND	T1.LoginName = T2.LoginName
			AND	T1.HostName = T2.HostName
		WHEN MATCHED THEN
			UPDATE SET
				NTDomainName = T2.NTDomainName
			,	NTUserName = T2.NTUserName
			,	LastStartTime = T2.LastStartTime
			,	TotalLogins = ISNULL(TotalLogins, 0) + 1
		WHEN NOT MATCHED BY TARGET THEN
			INSERT	(DatabaseName, ApplicationName, LoginName, HostName, NTDomainName, NTUserName, LastStartTime, TotalLogins)
			VALUES	(DatabaseName, ApplicationName, LoginName, HostName, NTDomainName, NTUserName, LastStartTime, 1);
	END
END
GO

--	Assign the activation procedure to the queue
ALTER QUEUE queue_audit_logins
	WITH ACTIVATION (
		STATUS = ON
	,	PROCEDURE_NAME = dbo.ProcessServerEvents
	,	MAX_QUEUE_READERS = 4
	,	EXECUTE AS SELF
);
GO

--
--	Cleanup
--
--ALTER QUEUE queue_audit_logins WITH ACTIVATION (STATUS = OFF);
--DROP EVENT NOTIFICATION audit_logins ON SERVER;
--DROP ROUTE route_audit_logins;
--DROP SERVICE service_audit_logins;
--DROP QUEUE queue_audit_logins;
--DROP PROCEDURE dbo.ProcessServerEvents;
--DROP TABLE dbo.audit_logins_summary;
GO