November 5, 2018 at 8:14 am
Hi, is there an extended event or a DMV in 2005, 2008 or 2008r2 where I can capture user related information(login, connection info etc.)?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 5, 2018 at 10:01 am
So I found something useful on MSSQLTIPS.com but I am wondering if we can insert the result from this event into a SQL table?
CREATE
EVENT SESSION MonitorExpensiveQuery ON SERVER
ADD
EVENT sqlserver.sql_statement_completed
(
ACTION
(
sqlserver.database_id,
sqlserver.session_id,
sqlserver.username,
sqlserver.client_hostname,
sqlserver.sql_text,
sqlserver.tsql_stack
) )
ADD
TARGET package0.asynchronous_file_target
(
SET FILENAME = N'D:\ExtendedEvents\Query.xet',
METADATAFILE = 'D:\ExtendedEvents\Query.xem'
)
GO
ALTER
EVENT SESSION MonitorExpensiveQuery
ON SERVER STATE = START
GO
SELECT
CAST(event_data AS XML) event_data, *
FROM sys.fn_xe_file_target_read_file
('D:\ExtendedEvents\Query*.xet',
'D:\ExtendedEvents\Query*.xem', NULL, NULL)
GO
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
November 5, 2018 at 12:07 pm
Got it.select top 10
xevents.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
xevents.event_data.value('(event/action[@name="username"]/value)[1]', 'nvarchar(max)') AS [UserName],
xevents.event_data.value('(event/action[@name="database_id"]/value)[1]', 'nvarchar(max)') AS [DBName],
xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS [client_hostname],
xevents.event_data
from sys.fn_xe_file_target_read_file
('D:\ExtendedEvents\Query.xet',
'D:\ExtendedEvents\Query.xem',
null, null)
cross apply (select CAST(event_data as XML) as event_data) as xevents)
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply