Diagnosing SQL Server 2012 Using Extended Events - SQL Server 2012 Internals and Troubleshooting - page 373

This will reconi gure the live data view to
look something like what is shown in
Figure 13-24.

Viewing Saved Data
If you've coni gured your session to use an
event i le as its target, then the captured
events will also be written to disk. You can
read the event i le's content from disk while the session is still running, or after the session has been
stopped, you might copy it from the server to your laptop.

FIGURE 13-24

➪ ➪

To open the i le, select File File within SQL Server Management Studio, from where you

can then browse to wherever you keep your event i les. By default, they are in the following path:

Open

C:\Program Files\Microsoft SQL Server\MSSQL11.x\MSSQL\Log

and have a default i le extension of . xel . Opening one of these i les presents you with the same data
you saw when live data was being shown.

Event i les can also be queried using T-SQL. Like SQL Trace, the function sys.fn_xe_file_
target_read_file reads an operating system i le and returns it as XML-based table data. Writing
queries to handle XML is something you'll become good at if you want to perform a lot of analysis
on Extended Events data; but for now, the following query shows an example of how you can read
the event i le to see the login events you captured:

select event_data.value('(event/@timestamp)[1]', 'datetime2') as
[Event_Time_UTC],
event_data.value('(event/action[@name="nt_username"]/value)[1]',
'varchar(100)') as [NT_Username],
event_data.value('(event/action[@name="client_hostname"]/value)[1]',
'varchar(100)') as [Client_Hostname],
event_data.value('(event/action[@name="client_app_name"]/value)[1]',
'varchar(100)') as [Client_Appname]

from (select cast(event_data as xml)
from sys.fn_xe_file_target_read_file('C:\Program
Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Log\
Logins_0_129805987593580000.xel', null, null, null)
) as results(event_data)
order by [Event_Time_UTC]

The preceding returns the following results:

Event_Time_UTC NT_Username Client_Hostname Client_Appname
--------------------------- ---------------- --------------- -------------------
2012-05-04 09:55:06.6850000 Gavin-VAIO\Gavin GAVIN-VAIO Microsoft SQL
Server Management
Studio
2012-05-04 09:55:06.7000000 Gavin-VAIO\Gavin GAVIN-VAIO Microsoft SQL
Server Management
Studio