Welcome to the third recipe of this Extended Events cookbook! You will find the first blog post of the series here and you can browse all recipes with the xesmarttarget tag on this blog.

The problem

In the previous recipe, we wrote event data to a table in the database and each event used field and action names to map to the column names in the table. The same information (the text of the command) was stored in two separate columns, depending on the event type:

batch_text for sql_batch_completed events
statement for rpc_completed events

SSMS has a nice feature that allows you to create a merged column using data from several columns. Here is how you do it:

As you can see, the two even types are returning NULL for the columns that they don’t have:

You can create a merged column by right clicking the table header and clicking “Choose Columns”:

In the dialog you can click on “New” in the section for merged columns:

In the next dialog you can select which columns to merge. In this case you want to merge batch_text and statement:

The result looks like this:

When the data is missing from one column, it is taken from the other column and the merged column always contain some data to show.

It would be nice if I could do that with XESmartTarget as well, merging multiple columns before writing them to the target table. Turns out it is possible, with some work on the configuration file.

The session

First, we need a session. Since it is the same session as the one used for the second recipe, I will show you how you can recreate the same session with a different name (Recipe03) using the previous session as a template. Of course, I will use dbatools for that.

Get-DbaXESession -SqlInstance "localhostSQLEXPRESS" -Session Recipe02 |
    Export-DbaXESessionTemplate -Path C:tempxe |
    Import-DbaXESessionTemplate  -SqlInstance "localhostSQLEXPRESS" -Name "Recipe03"
Start-DbaXESession -SqlInstance "localhostSQLEXPRESS" -Session Recipe03

It could not be easier! dbatools is the best!

If you insist doing things the hard way, you can reuse the script from the first recipe and change the name of the session.

XESmartTarget

The configuration needs to be a bit more complex this time. The column “text” on the target table will have to receive data from different events, which have attributes with different names. To accomplish this, the configuration file will have to leverage two features: event filters and expression columns.

The first one is easy: you can decide which events gets processed by the Response using the “Events” attribute. I introduced this possibility in Recipe 1.

Expression columns use the same syntax as calculated columns in SQL Server. To use an expression column in XESmartTarget, you can declare in the configuration file in the form column name = expression. This will allow you to calculate expressions, using fields and actions from the events as operands and all the functions and operators available in .NET DataTable objects.

In this case, the rpc_completed event will have to rename its “statement” column to “sql” and the sql_batch_completed event will have to rename its “batch_text” column to “sql”. While we’re at it, let’s also create a column “total_io” that contains logical_reads + writes. Let’s see how you can do it:

{
    "Target": {
        "ServerName": "$ServerName",
        "SessionName": "Recipe03",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "TableAppenderResponse",
                "ServerName": "$ServerName",
                "DatabaseName": "XERecipes",
                "TableName": "Recipe_03_Queries",
                "AutoCreateTargetTable": true,
                "OutputColumns": [
                    "name", 
                    "collection_time", 
                    "client_app_name", 
                    "server_principal_name", 
                    "database_name",
                    "sql AS statement",
                    "total_io AS logical_reads + writes"
                ],
                "Events": [
                    "rpc_completed"
                ]
            },
            {
                "__type": "TableAppenderResponse",
                "ServerName": "$ServerName",
                "DatabaseName": "XERecipes",
                "TableName": "Recipe_03_Queries",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "name", 
                    "collection_time", 
                    "client_app_name", 
                    "server_principal_name", 
                    "database_name",
                    "sql AS batch_text",
                    "total_io AS logical_reads + writes"
                ],
                "Events": [
                    "sql_batch_completed"
                ]
            }
        ]
    }
}

Besides filters and expressions, this JSON file also introduces the ability to process the events using multiple response objects, even of different types.

Let’s save this file as c:tempRecipe_03_Output_Table_Expressions.json and run XESmartTarget:

"%ProgramFiles%XESmartTargetxesmarttarget.exe" --File c:tempRecipe_03_Output_Table_Expressions.json --GlobalVariables ServerName=(local)SQLEXPRESS

As you can see from the output, XESmartTarget initializes two independent TableAppenderResponse objects and each one works on the events defined in the filter and outputs the columns defined in the OutputColumns property, including the expressions described above.

Querying the target table, you can see that the “sql” column contains data from both events, achieving the same result as the merged column in SSMS:

Recap

Filters and Expression Columns allow you to achieve more complex results, like combining data from different fields or actions into a single column, or calculating expressions.

In the next recipe you will learn how to combine Responses of different types and use XESmartTarget to send email notifications when a particular event is captured. Keep an eye on the XESmartTarget tag for the next recipes!

Recipe 3: Merging and manipulating events