Reading extended event files using client side tools only

For any serious tracing working you’ll be using extended events and sending the output to XEL target files. Various options exist when it comes to reading these files. You can open the files using the file open function in Management Studio. Or you can use the sys.fn_xe_file_target_read_file function to have your SQL Server database engine read the files.

If you want to read the XEL files programmatically and don’t want to be constrained by having a database connection you can use a .Net program and use the extended event reader functionality in Microsoft.SqlServer.XEvent.Linq.dll. For example, this post describes an approach using C#:

http://sqlblog.com/blogs/extended_events/archive/2011/07/20/introducing-the-extended-events-reader.aspx

If you prefer to use Powershell here’s one solution:

# If doing this on a client that just has the client tools and no database engine then you have to use 32 bit Powershell as Microsoft
# have not provided a 64 bit assembly for the client tools distribution.
# If you are running this on a server that has the database engine then you don't need to use 32 bit Powershell as the 64 bit assembly
# is present in the C:\Program Files\Microsoft SQL Server\120\Shared directory

# For SQL 2014 you have to add the reference to Microsoft.SqlServer.XE.Core.dll. You don't need this for SQL 2012

Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XE.Core.dll'
Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XEvent.Linq.dll'

$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData("c:\temp\test.xel")

$events | Foreach-Object { $_.Actions | Where-Object { $_.Name -eq 'client_hostname' } } | Group-Object Value

This can be used if, for example, you’re tracing the sql_batch_completed event and have added the client_hostname action. The script outputs the counts for each host name in the XEL file.

As can be seen in the comments it’s unfortunate that for the client side only solution we have no choice but to use the 32 bit assemblies. This is still the case for the SQL 2014 client side of things.

 

Advertisements
  1. #1 by Bruce on March 16, 2015 - 8:40 pm

    I am having some issues opening the log file, i was hoping you might have an idea what to do here. I am using an x86 powershell session even though I have a local install of sql 2014 (Microsoft SQL Server 2014 – 12.0.2000.8 (X64) ) it doesn’t work with a 64-bit powershell instance for some reason. I tried running it as administrator and the assemblies just won’t load so I switch to x86. Anyway, when I try to open the log file, it says: New-Object : Exception calling “.ctor” with “1” argument(s): “The file C:\script\xevent\blocking.xel is not a valid extended events log file and cannot be opened.”At line:1 char:11 The .xel file will open in SSMS without issue.

    • #2 by Bruce on March 16, 2015 - 8:46 pm

      It seems it works with the metadata file but not without it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: