View extended events data

 Viewing Event File Data  

Using an event file as your session’s target is a good choice, as it is the best choice of target given its flexibility when it comes to viewing the captured data in it. Using SQL Server Management Studio, you can view their contents both in real time as the data is being captured and as a standalone data file after the session has stopped. 

 

Viewing Live Data  

This example uses the session created earlier, which captures login events after ensuring it’s started. After right-clicking on the session in SQL Server Management Studio, click the Watch Live Data option. This opens a new tab in SQL Server Management Studio that says “Retrieving event information from server. . .” and then you will begin seeing events as they are captured. 

 

Viewing Saved Data  

If you’ve configured your session to use an event file as its target, then the captured events will also be written to disk. You can read the event file’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. To open the file, select File ➪ Open ➪ File within SQL Server Management Studio, from where you can then browse to wherever you keep your event files. By default, they are in the following path: C:\Program Files\Microsoft SQL Server\MSSQL11.x\MSSQL\Log and have a default file extension of .xel.  

Opening one of these files presents you with the same data you saw when live data was being shown. Event files can also be queried using T-SQL. Like SQL Trace, the function sys.fn_xe_file_ target_read_file reads an operating system file 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 file 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]