Found the answer.
The login event is correct and the network protocol is exposed via the options_text action. options_text was always blank for me previously and this needs to be set on via SET collect_options_text=(1). An example session might be: -
CREATE EVENT SESSION [Login] ON SERVER
ADD EVENT sqlserver.login(SET collect_options_text=(1)
ACTION(
sqlserver.session_id
,sqlserver.username
))
ADD TARGET package0.ring_buffer
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=1 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,
STARTUP_STATE=ON
)
GO
And this would then populate options_text with something like: -
network protocol: TCP/IP set quoted_identifier on set arithabort off
set numeric_roundabort off set ansi_warnings on set ansi_padding on
set ansi_nulls on set concat_null_yields_null on set
cursor_close_on_commit off set implicit_transactions off set
language us_english set dateformat mdy set datefirst 7 set
transaction isolation level read committed
I found this out be chance by creating a session from the "Connection Tracking" Microsoft shipped extended events template.
SSMS | Management | Extended Events | Sessions | Right click | New session | General page | Template = Connection Tracking.
That template includes "SET collect_options_text=(1)" and I wasn't previously aware such an option existed.
An example is also given in this sql-server-performance article. The last screenshot shows options_text populated with "network protocol: LPC.." LPC is shared memory.
Unfortunately, there are several DDL-type events that don't actually count as DDL events for usage with DDL triggers, event notifications, etc.
You can capture sp_refreshsqlmodule
calls using the following Extended Events session:
CREATE EVENT SESSION [refreshes] ON SERVER
ADD EVENT sqlserver.module_end
(
SET collect_statement = (1)
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.username,
sqlserver.context_info
)
WHERE ([object_id] = -419385653) -- OBJECT_ID(N'sys.sp_refreshsqlmodule')
)
ADD TARGET package0.asynchronous_file_target
(
SET FILENAME = N'C:\temp\refreshes.xel'
);
GO
ALTER EVENT SESSION [refreshes] ON SERVER STATE = START;
GO
You may need to collect a different set of auditing columns; these were mostly just borrowed from a similar session I have. You may also want to add an additional filter for sp_refreshview
and possibly other events like recompiles and statistics updates (I do not know all of the procedure calls that might change modify_date
but aren't captured as proper DDL events).
Now, the session will merely capture the data. You can inspect it manually like this:
;WITH ee_data AS
(
SELECT x = CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file
(N'C:\temp\refreshes*.xel', NULL, NULL, NULL)
)
SELECT
[statement] = x.value('(event/data[@name="statement"]/value)[1]','nvarchar(4000)'),
[timestamp] = x.value('(event/@timestamp)[1]','datetime2'),
--username = x.value('(event/action[@name="username"]/value)[1]','nvarchar(400)'),
--[host] = x.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(400)'),
--app = x.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(400)')
-- you'll have to add the xquery stuff to get context_info
FROM ee_data;
Example output:
statement timestamp
-------------------------------------------- ---------------------------
EXEC sys.sp_refreshsqlmodule N'dbo.someview' 2015-10-16 16:45:35.6220000
You'll have to parse out the object name, and put the information into your own XML format to match EVENTDATA()
. Also note that timestamp
is in UTC, not your local time, so you'll need to adjust that for valid comparisons to modify_date
(which inherit server time). In my case the difference between modify_date
and timestamp
was 6 milliseconds, so the session does not record the exact moment the object was modified - you'll need to allow a little leeway to "match" these two values.
You'll then need to put whatever code you end up with into some kind of job that polls the file target for new rows (you may consider query notifications for this to avoid polling, but polling is much simpler), and inserts them into your DDL auditing table.
Best Answer
I'm going to say you are getting all the extra fields because you picked a template when you built your event session (or used the wizard). If you only want certain fields then go uncheck what you don't want. However, there will be some event level info that will always be there, what is shown under the "Event Fields" for each event are their by default and cannot be removed.
Default event for "sql_statement_completed" only captures this, after I checked the global field for client application name: