Welcome to the first 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.

Recipes: what are the ingredients?

Every recipe starts with a problem to solve and has three ingredients:

A session to capture the events
A JSON configuration file for XESmartTarget to process the events
A client application to read the data produced by XESmartTarget

The problem

In this case, imagine that you wanted to observe the commands executed on a SQL Server instance and save them to a file to process them later. Of course, Extended Events can do that with the built-in targets. However, when you write to a file target, the file has to reside on the disks of the SQL Server machine (well, actually, the file could be sitting on a file share writable by SQL Server or even on BLOB storage on Azure, but let’s keep it simple). How do you use the storage of the client machine instead of using the precious filesystem of the server machine?

Here is where XESmartTarget can help you with a CsvAppenderResponse. This Response type writes all the events it receives to a CSV file, that can be saved on the client machine, without wasting disk space on the server. You can decide which events to process and which columns to include in the CSV file, but more on that later.

The session

First of all, you need a session: how do you create it? I know what you’re thinking: “if this was a trace, I would know how to get it”. Right. Many DBAs are still using Traces, because Extended Events have not been very friendly to them. Profiler was super easy to use: start the program, connect, select a template, choose events and columns, start the trace and you see the data right away in the GUI. Extended Events can be a bit overwhelming because there are multiple concepts to get hold of: sessions, events, targets, fields, actions… Yeah, it’s the same old story: more power, more responsibility.

It was not just you: everyone was confused by Extended Events when it first shipped with SQL Server 2012 (well, we had something in 2008, but it wasn’t really a replacement for traces until 2012).

Chrissy LeMaire was just as confused as you, so she did what the giants do: she studied the topic in depth and not only she made it easier for herself, but she made it easier for everyone using dbatools. If you don’t know what dbatools is, you really need to check it out: it’s a Powershell module that allows DBAs to perform all their day-to-day tasks on SQL Servers using powershell. Everything you can do with SSMS (and much more!) can be done with dbatools. Chrissy created a lot of dbatools commands to work with Extended Events and now everything is much easier!

But is it really as easy as working with traces? With Profiler, you would have the Standard template and you would only need to click start. Can it be that simple with dbatools? Let’s find out.

One of the great features that Chrissy added to dbatools is the ability to create a session from a template. She gathered a lot of useful session definitions from community blog posts, scripts, and templates from Microsoft tools, then she included those definitions in dbatools. It’s the same as in Profiler: all you have to do is select the appropriate template:

Get-DbaXESessionTemplate | Out-GridView

In this case you can use the “Profiler Standard” template and of course it’s there and you can do it with just a couple of lines of PowerShell:

Import-DbaXESessionTemplate -SqlInstance "localhostSQLEXPRESS" -Name "Recipe01" -Template "Profiler Standard"

Start-DbaXESession -Session "Recipe01" -SqlInstance " localhostSQLEXPRESS"

That couldn’t be easier! Notice that the commands above did not add any targets to the session: the streaming API will take care of processing the data.

If you don’t like dbatools or don’t want to use powershell, the script for the session is this:

IF NOT EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'Recipe01')
CREATE EVENT SESSION [Recipe01] ON SERVER 
ADD EVENT sqlserver.attention(
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
),
ADD EVENT sqlserver.existing_connection(
SET collect_options_text=(1)
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
),
ADD EVENT sqlserver.login(
SET collect_options_text=(1)
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
),
ADD EVENT sqlserver.logout(
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.nt_username
,sqlserver.server_principal_name
,sqlserver.session_id
)
),
ADD EVENT sqlserver.rpc_completed(
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(
 package0.event_sequence
,sqlserver.client_app_name
,sqlserver.client_pid
,sqlserver.database_id
,sqlserver.nt_username
,sqlserver.query_hash
,sqlserver.server_principal_name
,sqlserver.session_id
)
    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))
);
IF NOT EXISTS ( SELECT * FROM sys.dm_xe_sessions WHERE name = 'Recipe01')
    ALTER EVENT SESSION Recipe01 ON SERVER STATE = START;

Now that you have a session running you can work with the events it captures, using XESmartTarget.

XESmartTarget

The tool already ships with multiple Response types: all you have to do is prepare a configuration file. In this case you will use a CsvAppenderResponse. If you go to the documentation page, you will see what properties are exposed by this Response type, which can be set in a JSON configuration file.

OK, let’s do it! Following the example on the documentation, you can craft your own JSON file to configure XESmartTarget:

{
    "Target": {
        "ServerName": "(local)\SQLEXPRESS",
        "SessionName": "Recipe01",
        "Responses": [
            {
                "__type": "CsvAppenderResponse",
                "OutputFile": "c:\temp\output.csv",
                "OverWrite": "true",
                "OutputColumns": [
                    "name", 
                    "collection_time", 
                    "client_app_name", 
                    "server_principal_name", 
                    "database_name",
                    "batch_text",
                    "statement"
                ],
                "Events": [
                    "rpc_completed",
                    "sql_batch_completed"
                ]
            }
        ]
    }
}

There are a couple of things to note on this file. First, it’s a JSON file, so it has to comply with the syntax of Javascript: backslash in strings has to be escaped with “”, so it becomes “\”. For each object in the “Responses” array, the property “__type” controls which Response type is used, then you can use its properties inside that block. For CsvAppenderResponse, the property “OutputColumns” controls which columns will be written to the CSV file. These columns are fields or actions from the events.
The property “Events” controls which events are processed by the current Response.

If your session captures multiple event types, you can choose which ones are processed by each Response, using the “Events” property. In this case, the profiler default template capture events that you don’t want to process, like existing connections, so you can filter for “rpc_completed” and “sql_batch_completed” events only.

Now, save the file as c:tempRecipe_01_Output_CSV.json and you can use it with XESmartTarget, by running this:

"%ProgramFiles%XESmartTargetxesmarttarget.exe" --File c:tempRecipe_01_Output_CSV.json

The output on the cmd window won’t tell you much, except that XESmartTarget is running, it’s connected to the appropriate session and the it’s writing to the CSV file.

Let’s check what happens if you run some commands from Azure Data Studio:

The cmd window doesn’t say anything new, but if you open the CSV file with VSCode you will see that some data has been saved:

Code is great for inspecting CSV files, because it has the ability to reload the file when new rows are added and also has nice plugins like Rainbow CSV to help you interpret the contents correctly.

When you are finished with your capture, you can press CTRL+C on the cmd window where XESmartTarget is running and it will shut down.

Recap

You wanted to save all the commands executed on your SQL Server to a CSV file on your computer. You had to set up a session for that, which was super easy, thanks to dbatools. Then you configured XESmartTarget to process all the events and save them to the CSV file of your choice. You could also watch the events flowing to the file in real-time, thanks to Code and its plugins.

In this recipe you familiarized with XESmartTarget and had a glimpse of its capabilities. The next recipe will introduce more capabilities and showcase more features of the JSON configuration format. Stay tuned!

Recipe 1: Writing Extended Events to a CSV file