Capture Linked server queries using SQL Extended Events

Question

I tried many event types but could not achieve logging for Linked server. I could figure out events for all other databases but no luck with linked server. Any suggestions ?

Answer 1

You can use the OLEDB_DATA_READ event to track queries to linked servers. If the results from this event are more verbose than you want, you may need to add filters to capture the the particular information that is needed. The following DDL creates this extended event and adjusting the options (i.e. FILENAME, MAX_MEMORY, etc.) will probably be necessary.

CREATE EVENT SESSION [XE_Linked_Server_Test] ON SERVER 
ADD EVENT sqlserver.oledb_data_read
ADD TARGET package0.event_file(SET FILENAME=N'C:\Test\XE_Output_File.xel')
WITH (MAX_MEMORY=4096 KB,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=OFF)
GO

Answer 2

This is just an expansion of @userfl89's answer, with a query to parse the xevent. I needed this, but since I don't need a bunch of history, ran it through a 1mb ring buffer, and the startup_state is off (so it won't restart after a sql service restart). There's two pieces of code - the first is quick & dirty and will probably work well enough. The second batch of code is complicated because I use it for other Xevents that are larger and are really slow otherwise.

Quick & Dirty

/*
CREATE EVENT SESSION [XE_Linked_Server_Test] ON SERVER 
ADD EVENT sqlserver.oledb_data_read
(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username,sqlserver.client_pid)
--adding a bunch of other useful stuff. Who, what, where...
)
    ADD TARGET
        package0.ring_buffer
            (SET
                max_memory = 1000  ) -- Units of KB.
WITH (MAX_DISPATCH_LATENCY = 60SECONDS,STARTUP_STATE = off)
GO
ALTER EVENT SESSION [XE_Linked_Server_Test] 
    ON server --use "on database" for Azure SQL DB
    STATE = START;
GO
*/


DECLARE @target_data XML
SELECT @target_data =  target_data
    FROM sys.dm_xe_session_targets        AS st
    INNER JOIN sys.dm_xe_sessions       AS se
         ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))
WHERE
se.name = 'XE_Linked_Server_Test' AND st.target_name = 'ring_buffer'
select @target_data
SELECT DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), 
ed.c.value('(@timestamp)[1]', 'nvarchar(max)')) AS err_timestamp, ed.c.query('.') AS event_data
, ed.c.value('(data[@name="linked_server_name"]/value)[1]', 'varchar(50)') AS linked_server_name
,
ed.c.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
ed.c.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,
ed.c.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,
ed.c.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,
ed.c.value('(action[@name="nt_username"]/value)[1]', 'nvarchar(max)') AS nt_username,
ed.c.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS username,
ed.c.value('(action[@name="session_nt_username"]/value)[1]', 'nvarchar(max)') AS session_nt_username,
ed.c.value('(action[@name="event_sequence"]/value)[1]', 'nvarchar(max)') AS event_sequence,
ed.c.value('(action[@name="client_pid"]/value)[1]', 'int') AS client_pid

FROM (SELECT @target_data AS target_data) a
cross apply (select CAST(target_data as XML) as event_data) as xevents
CROSS APPLY xevents.event_data.nodes('RingBufferTarget/event') ed(c)

And a better-performing version for servers with a ton of calls in the event.

/*
CREATE EVENT SESSION [XE_Linked_Server_Test] ON SERVER 
ADD EVENT sqlserver.oledb_data_read
(
ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username,sqlserver.client_pid)
--adding a bunch of other useful stuff. Who, what, where...
)
    ADD TARGET
        package0.ring_buffer
            (SET
                max_memory = 1000  ) -- Units of KB.
WITH (MAX_DISPATCH_LATENCY = 60SECONDS,STARTUP_STATE = off)
GO
ALTER EVENT SESSION [XE_Linked_Server_Test] 
    ON server --use "on database" for Azure SQL DB
    STATE = START;
GO
*/
IF object_id('tempdb..#xevent') is not null
    DROP TABLE #xevent
CREATE TABLE #xevent (target_data XML)
INSERT INTO #xevent (target_data)
SELECT target_data
    FROM sys.dm_xe_session_targets        AS st  --For Azure SQL DB, use dm_xe_database_session_targets
    INNER JOIN sys.dm_xe_sessions       AS se --For Azure SQL DB, use dm_xe_database_sessions
         ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))
WHERE
se.name = 'XE_Linked_Server_Test' AND st.target_name = 'ring_buffer'

--need two temp tables to speed shredding; this one will hold one row per event.
if object_id('tempdb..#xevent2') is not null
    DROP TABLE #xevent2
CREATE TABLE #xevent2 (err_timestamp DATETIME2, target_data XML)
CREATE INDEX ncidx__xevent2__err_timestamp ON #xevent2(err_timestamp)

--shredded-to-node for speed and to filter 
INSERT INTO #xevent2 
SELECT DATEADD(MINUTE, DATEPART(TZoffset, SYSDATETIMEOFFSET()), 
ed.c.value('(@timestamp)[1]', 'nvarchar(max)')) AS err_timestamp, ed.c.query('.') AS event_data
FROM #xevent a
cross apply (select CAST(target_data as XML) as event_data) as xevents
CROSS APPLY xevents.event_data.nodes('RingBufferTarget/event') ed(c)

select err_timestamp,
target_data.value('(/event/data[@name="linked_server_name"]/value)[1]', 'varchar(50)') AS linked_server_name,
target_data.value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
target_data.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,
target_data.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,
target_data.value('(/event/action[@name="database_id"]/value)[1]', 'int') AS database_id,
target_data.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(max)') AS nt_username,
target_data.value('(/event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS username,
target_data.value('(/event/action[@name="session_nt_username"]/value)[1]', 'nvarchar(max)') AS session_nt_username,
target_data.value('(/event/action[@name="event_sequence"]/value)[1]', 'nvarchar(max)') AS event_sequence,
target_data.value('(/event/action[@name="client_pid"]/value)[1]', 'int') AS client_pid
, target_data AS event_data  --we can leave this off since it makes this run SO MUCH LONGER.
FROM #xevent2