Easily Shred Event Data

shred_xeIt has been nearly a year since I started an extensive series about Extended Events. Previous to that start, I had already written a bunch of articles about Extended Events.

Among the batch of articles preceding the 60 day series one can find an article about shredding extended event data. Then as a part of the series (the first article in the 60 day series) one can find how to shred the action payload data attached to an extended event session.

You can read the article on shredding XEvent payload data here. Then you can follow that up by reading how to shred the Action data here. And once you are done with those and you really want to learn more about Extended Events, please read the whole series which can be found here.

All of that reading to catch up should keep you busy for a tad bit.

Upgrade

A year is a really long time to go without updating a script – according to some. Today, I have an update for both of the XML shredding scripts you just read about in the list of articles mentioned moments earlier in this post.

Before I dive into the script, let’s revisit a sample of the XML from an event session payload. Within an event session, you can see that there are nodes for both the data and the actions (highlighted in green and red). With all of this information found within the same XML for the session, it somewhat makes sense to try and parse all of the data at once.

action_xml

In addition to parsing all of the XML for the data and the actions at the same time, it seems to also make sense to generate the statements that would parse the XML within a single effort. As you would have noted, my previous scripts were just that – scripts. That implies executing a separate script for each the data and the actions. Maybe it would make more sense to execute a single script.

leaving_painIt is that notion of a single script that constitutes this upgraded version of the script.

One Script

In the following script, I have tried to accomplish just that – a single script to create the entire XML parser for me, for you, and for anybody wishing to use it. I don’t want to have to remember the subtle nuances of how to parse each of the events each time I need to parse the session data. I want something that is quick, easy, and repeatable.

With all of that said, here is the script that I now use to parse my session data. You should notice that it has been simplified and is more extensive now.

Not only does this script slice and dice for you…Wait that’s not quite right.

This script doesn’t just generate the XML shredding strings for the payload and action data. This script also will produce the rest of the tsql statements that should precede and conclude the XML parsing statements. Yes it uses dynamic sql. That is to your advantage in this case.

Could there be other enhancements? Absolutely! More will be coming.

Enjoy this script and happy parsing.

Extra Extra – Read All About It!

From the comments, you will see that Brent Ozar (blog | twitter) made an excellent suggestion. To be honest, I considered sharing some examples during the initial write of this article. I had opted against it then, not considering it in the same way that Brent puts it. So, in this addendum I have a quick example using an XEvent Session that I have not yet written about in all of my articles on Extended Events (pseudo spoiler alert).

Let’s use the AuditSelects XEvent Session I have deployed and running (remember a session can be deployed but may be in the “stopped” state). If I want to figure out what data has been captured for this session, I can take the session name and plug it into the script shown in this article.

parsexe_params

Ignore the green tsql comments for now (you can read them direct from the script). Highlighted in gold is the variable for the session name. By entering a valid session name for this variable, I can restrict the results to just the metadata for that specific session. In addition, note that I have highlighted, in a pale chartreuse, two variables that will give me unique column names for each of the events and actions within each session. This is important because some events have the same name for different attributes as other events. If events with the same attribute names are used in the same session, you will get a duplication of data. Maybe you want the duplicate data. Maybe you don’t.

The session name variable is used in each of the next three code segments. Each segment in the script will generate sql statements that will need to be copied into a new query window. I will leave the further breakdown of the script as an exercise for you. What I want to do here is show the results from executing this script.

When I execute the script, I will have four result sets returned to me (in grid mode). It will look something like shown in the following image.

parser_results

Breaking this down into three colors is probably easiest to see how things fit together. Highlighted in red, I have the build out of the pre-processing statements and the build of the “select” statement that will be returning our data to us. Then in blue comes the guts of the query – all of the beautiful XML parsing statements. The segments in blue correlates to the columns in the select list. And at the bottom of the image I have the finishing pieces that includes my predicates, from, and joins. Each of the highlighted segments will then be copied and pasted to a new query window and look something like the following.

parsed_gluedtogether

And there you have it. Sure there are still some manual steps in it, but you no longer need to memorize all of that glorious XML parsing syntax. You only need to copy and paste with this version of the script.

Enjoy your adventures in parsing XML!

 Comments (8) 

  1. Brent Ozar says:

    Looks neat! Could you post a sample of what the output looks like, too? That might get folks off the benches.

  2. Mark Kimsey says:

    This is very cool, thank you very much!

  3. MvH says:

    Nice script! I like general solutions like this.

    If you test it with a session that contains events (rpc_completed/data_stream, rpc_completed/output_parameters) with a value data type binary_data and xml, you will get an error of the value-method.
    Would also be nice, if the complete statement will be displayed as xml value, so that you can click on it

    declare @sql nvarchar(max)=’
    /* parse the data */
    IF EXISTS (SELECT OBJECT_ID(”tempdb.dbo.#xmlprocess”))
    … concatenated result of the four result sets…

    ORDER BY timestamp
    , event_data.value(”(event/action[@name=”event_sequence”]/value)[1]”,
    ”varchar(max)”);’

    select convert(xml,@sql)

    • Jason Brimhall says:

      Thank you for the feedback. I will update and include the necessary changes. I knew there had to be some data types I had missed somewhere.

  4. michael graham says:

    Jason, great job on your post. It started me down the path of making a truly dynamic xel shredding and import process. I actually took your code and made a tsql version of my own and then moved on to a powershell version. My dynamic powershell version is here: https://github.com/mgraham-cracker/ExtendedEvents-BulkLoadParser

    In my previous TSQL version I used your code along with the STUFF command to keep from having to manually combine datasets for column definitions. But I found that for large files it just took way to long to import using the TSQL tvf method.

    Thanks again for sharing your insightful post. It got me moving in the right direction for the solution I came up with.

    • Jason Brimhall says:

      Michael,
      Thanks for the excellent feedback. I am glad somebody took this and adapted it to posh. I will have to check it out. Mind sharing your tsql version?

 Leave a comment 

Your email address will not be published. Required fields are marked *

*


 © 2017 - SQL RNNR