Audit applications that connect to a SQL Server database using Integrated Authentication

xe_audit_db_ntauth_applications_2012.sql

--
--	Audit applications that connects to a specified database using
--	Windows Integrated Authentication storing the applications names
--	and the number of connections.
--
--	Requires SQL Server version >= 2012
--	Activate SQLCMD mode before running
--

:setvar DatabaseName TestDatabase

--	Create event session
CREATE EVENT SESSION [AuditDBNTAuthApps_$(DatabaseName)] ON SERVER 
ADD EVENT sqlserver.login(
	SET collect_database_name=(1)
    ACTION(sqlserver.client_app_name, sqlserver.nt_username)
	WHERE (
				sqlserver.not_equal_i_sql_unicode_string(sqlserver.nt_user, N'')
			AND sqlserver.equal_i_sql_unicode_string(database_name, N'$(DatabaseName)')
	)
) 
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.login', SOURCE=N'sqlserver.client_app_name')
WITH (MAX_MEMORY=4096KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=ON)
GO

--	Start sesson 
ALTER EVENT SESSION [AuditDBNTAuthApps_$(DatabaseName)] ON SERVER STATE = START;
GO

:setvar DatabaseName TestDatabase
--	Query Example (substitute $(DatabaseName) to run it 
SELECT
	slot_node.value('(value)[1]', 'nvarchar(128)') AS histogram_value
,	slot_node.value('@count[1]', 'int') AS histogram_count
FROM	(
		SELECT	CAST(XT.target_data AS xml) AS target_data
		FROM	sys.dm_xe_sessions AS XS
		JOIN	sys.dm_xe_session_targets AS XT
		  ON	XS.[address] = XT.event_session_address
		WHERE	XS.name = N'AuditDBNTAuthApps_$(DatabaseName)'
		  AND	XT.target_name = N'histogram'	-- N'asynchronous_bucketizer' per 2008R2
		) AS ED
CROSS
APPLY	target_data.nodes('HistogramTarget/Slot') as XQ(slot_node);	-- 'BucketizerTarget/Slot' per 2008R2