This is something that I’ve seen pop up a couple of times on various forums recently, how can we export the results of an XEvent session. So I thought I’d answer the question here.

It’s actually a very simple thing to do in SSMS, although the option is not always easy to spot.

When we open an XEvent, we’ll also get a nice new toolbar appear with all sorts of options to do with XEvents. There are buttons to set up filters, group data, aggregate data amongst others, but there doesn’t seem to be anything letting up export data.

sys.fn_xe_file_target_read_file

One option that you’ve got if you’re writing your results to a file is to query the XEvent result file using sys.fn_xe_file_target_read_file. Something like the following can be used to pull back everything from the AlwaysOn_Health XEvent session.

SELECT *
FROM sys.fn_xe_file_target_read_file('AlwaysOn_Health*.xel', NULL, NULL, NULL)

sys.dm_xe_session_targets

If you’re writing out to the ring buffer then unfortunately sys.fn_xe_file_target_read_file isn’t going to work for you, there’s no file to read. In that case, you’ll want to query sys.dm_xe_session_targets.

The ring buffer data is stored as XML, which may mean that, depending on how you’re wanting to export it, you might need to do a little extra work to shred it into something that’s usable to you. There are a number of methods of doing that which I won’t go into here (but could be an interesting subject for a future post).

The following query returns the XML from my ‘Connection Monitor’ session.

SELECT CAST(dm_xe_session_targets.target_data AS XML)<br>FROM sys.dm_xe_session_targets<br>JOIN sys.dm_xe_sessions ON dm_xe_session_targets.event_session_address = dm_xe_sessions.address<br>WHERE dm_xe_sessions.name = 'Connection Monitor'

Life’s Easier in SSMS

The above queries are nice but we can do all this in a much simpler way using SSMS.

“But wait, didn’t you say that SSMS doesn’t give us an option to export?!”, I hear you all say.

Well, no I didn’t say that exactly. I said that there’s no export option on that XEvents tool bar and this is where I think people get stuck and miss what’s hidden in plain sight.

Open up your XEvent session file in SSMS and not only will you see an XEvents toolbar appear but look at the menu bar, there’s also an Extended Events menu that’s just appeared. I think this is what people are missing.

Open that up and down at the bottom, there’s an ‘Export to’ option which will give you a number of export locations including to a table and a CSV file.

If you’re writing to the ring buffer then you won’t see this when you open the results but instead you’ll be presented with the XML in a similar way that you were when tapping up sys.dm_xe_session_targets. Unfortunately, just like when using that DMV, you’re going to have to do a little work to shred the XML and make it usable for whatever you’re trying to do with it.

I’ve got no idea why SSMS doesn’t also give us the export option in the toolbar, it’d seem a logical thing to do when all the other options are there and so many people seem to be missing that menu bar item.

Thanks for reading and I hope you’ve found this useful.