Some time ago, I started a project called XESmartTarget. I find it super useful and you should probably know about it. It’s totally my fault if you’re not using it and I apologize for all the pain that it could have saved you, but it didn’t because I did not promote it enough.
Now I want to remedy my mistake with a 10 days series of blog posts on XESmartTarget, which will show you how useful it can be and how it can be used to accomplish your daily DBA tasks using Extended Events.
In this first post of the series, I will introduce XESmartTarget, show how it works and how to configure it. For the next 10 days I will publish a post to show you how to solve a specific problem using XESmartTarget. Let’s go!
What is XESmartTarget?
XESmartTarget is a small command line utility that can connect to an Extended Events session using the streaming API and can perform actions in response to the events captured by the session. The actions can vary from saving to a table in a database, writing to a CSV file, sending alerts and many more.
You can think of XESmartTarget as a processing engine for Extended Events, that you can run from the command line, without having to write a single line of code.
Where does it run?
XESmartTarget does not need to run on the server, it can run on any Windows machine that can connect to the target SQL Server instance. You can certainly run it on the server, but you don’t need to. XESmartTarget depends on Microsoft Visual C++ 2013 Redistributable: if you have the client utilities (SSMS) on your computer then you’re good to go, otherwise you can always download from Microsoft. It doesn’t run on Linux, I’m sorry.
How do I get it?
It’s open-source software: you can download it from GitHub and install it. You have a x64 setup kit and a x86 setup kit: make sure to pick the correct version for your operating system. Your browser may complain about it being unsafe, despite being signed with a code signing cert (sigh…). Don’t worry, go ahead and download it. Windows may also complain when running the .msi, so you will have to bypass SmartScreen as well. By default, the software gets installed to c:Program FilesXESmartTarget
Why do I need it?
The built-in targets for Extended Events are great, but they don’t cover 100% of the spectrum. Some targets, like writing to a database table, would be extremely useful but are not there. There are multiple reasons, but mainly this is because of performance concerns: Extended Events have been designed to be fast and have a low performance impact on the server being monitored. Writing to a file or to a memory buffer is a fast operation, writing to a table or applying additional logic can end up slowing down the collection process and the SQL Server instance. However, Microsoft decided to give us the ability to post-process the events in the .xel files or process the events in near real-time using the streaming API for Extended Events. XESmartTarget uses the streaming API to receive the events from the server and the API itself has a built-in protection mechanism that prevents the server from being chocked by the client: if the client can’t keep up with the data rate from the server, it gets disconnected.
Having an API to process events means that we can write code to perform common actions on the events. I created 7 types of Response classes, that can receive data from the events and process them to let you perform actions that you can’t perform using the built-in targets:
- CsvAppenderReponse – writes event data to a CSV file
- EmailResponse – sends alerts via email based on event data
- ExecuteTSQLResponse – runs T-SQL commands for each event captured
- TableAppenderReponse – writes event data to a table in a SQL Server database
- GroupedTableAppenderReponse – aggregates event data in memory and then merges with existing data in the target table
- ReplayResponse – replays sql_batch_completed and rpc_completed events
- GelfTcpResponse – writes events to a GrayLog server
- TelegrafAppenderReponse – writes to an InfluxDB database using Telegraf
Will I have to write code?
If you really, really want to write code, you can do it: XESmartTarget is a .dll library that you can incorporate in your project. That’s what we did with dbatools. The license is super permissive, so go ahead and do it!
However, one of the strengths of XESmartTarget is that it requires absolutely no coding: all you have to do is configure XESmartTarget to do what you want. It is a command line tool and it accepts some parameters:
-F|--File <path to the .JSON configuration file>
Uses the supplied .json file to configure the source of the events and the list of responses
-N|--NoLogo
Hides copyright banner at startup
-Q|--Quiet
Suppresses output to console
-G|--GlobalVariables <variable1=value1 variableN=valueN>
Replaces $variableN with valueN in configuration files
-L|--LogFile <path to log file>
Writes the log to the file specified
As you can see, you can use a .json file to provide the configuration. Not everyone likes JSON for configuration files, but I find it easy to use and good enough for the purpose. A nice addition to the standard JSON format is the ability to add comments using the javascript notation.
A typical .json configuration file looks like this:
{
"Target": {
"ServerName": "server to monitor, where the session is running",
"SessionName": "name of the session",
"Responses": [
{
// Properties for Response1
},
{
// Properties for ResponseN
}
]
}
}
Each Response subclass has a set of public properties that can be set in the configuration file. You can visit the documentation page for each Response type to discover what are the properties available to you and see an example json file.
For instance, TableAppenderResponse has some properties to set the target server/database/table for the events and you can set them like this:
{
"Target": {
"ServerName": "(local)\SQLEXPRESS",
"SessionName": "commands",
"Responses": [
{
"__type": "TableAppenderResponse",
"ServerName": "(local)\SQLEXPRESS",
"DatabaseName": "DBAStuff",
"TableName": "queries",
"AutoCreateTargetTable": true,
"UploadIntervalSeconds": 10,
"OutputColumns": [
"name",
"collection_time",
"client_app_name",
"server_principal_name",
"database_name",
"batch_text",
"statement"
],
"Events": [
"rpc_completed",
"sql_batch_completed"
]
}
]
}
}
Once you have your .json configuration file ready and your Extended Events session running, you can start XESmartTarget. It’s a command like tool, so it won’t show any GUI, but it will print messages to the console or to the log file to indicate that it’s doing some work.
As an example, you can save the above as c:tempcapture_commands.json and run it with this command line:
“C:program filesxesmarttargetxesmarttarget.exe” --File c:tempcapture_commands.jsonYou will see something similar to this:
If you look in your database, you will see some rows in the target table:
If you want to stop XESmartTarget, you can press CTRL+C.
What else can it do?
The sky is the limit. In the next posts of this series, I will demonstrate how to accomplish typical DBA tasks using XESmartTarget and you will learn how to use the appropriate Response type for every need. You will also see how to unleash the most advanced features of the configuration files, to filter events, group and summarize data, use fields and actions as parameters and more.
Keep an eye on the xesmarttarget tag on this blog!