Extended events to capture login info, tablename, DB name, application name...

• LearningDBA

  SSCertifiable

  Points: 6616

  More actions

  November 5, 2018 at 8:14 am

  #382200

  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]

• LearningDBA

  SSCertifiable

  Points: 6616

  More actions

  November 5, 2018 at 10:01 am

  #2012420

  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]

• LearningDBA

  SSCertifiable

  Points: 6616

  More actions

  November 5, 2018 at 12:07 pm

  #2012438

  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]