Welcome to a new 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

Sometimes you are not interested in the individual events captured by a session, but you want to extract some information from a series of events, by grouping and aggregating them. This is the case of events that happen very often, like, logon events, that can help you validate your story.

Imagine that you inherited a big, busy and chaotic SQL Server instance, with lots of databases and logins. One of the things that you probably want to do is track which logins are active and which ones are not and can be safely disabled.

One of the possible ways of doing this is to enable successful and failed login auditing in ERRORLOG, but this creates a lot of noise. No thanks, I don’t want a messy ERRORLOG.

Another possibility is to capture login events with an Extended Events session and write them to a file target. However, with this approach you capture the individual events, which you don’t need. What you really want is a very simple information: when has each login accessed the server the last time? Writing session data to a file target does not answer that question directly, but forces you to read and aggregate all the data in the file at a later time.

XESmartTarget can help with this, using the GroupedTableAppenderResponse. This Response type aggregates the data in memory before writing it to a target table, where it is merged with the existing data.

The session

Let’s set up a session for this. You need to capture the “login” event, which contains all the data that you need.

IF NOT EXISTS ( SELECT * FROM sys.server_event_sessions WHERE name = 'Recipe06')
CREATE EVENT SESSION [Recipe06] ON SERVER 
ADD EVENT sqlserver.login(
    SET collect_database_name=(1)
    ACTION(
        sqlserver.client_app_name,
        sqlserver.server_principal_name
    )
)
GO
IF NOT EXISTS ( SELECT * FROM sys.dm_xe_sessions WHERE name = 'Recipe06')
    ALTER EVENT SESSION Recipe06 ON SERVER STATE = START;

XESmartTarget

This is what the configuration looks like:

{
    "Target": {
        "ServerName": "$ServerName",
        "SessionName": "Recipe06",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "$ServerName",
                "DatabaseName": "XERecipes",
                "TableName": "Recipe_06_LoginAudit",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "server_principal_name",
                    "database_name",
                    "client_app_name",
                    "MIN(collection_time) AS first_seen", 
                    "MAX(collection_time) AS last_seen", 
                    "COUNT(collection_time) AS logon_count" 
                ],
                "Events": [
                    "login"
                ]
            }
        ]
    }
}

It’s very similar to the TableAppenderResponse, with one main difference: you can have aggregated columns, using the usual aggregation functions and aliases to assign a name to the columns. All the columns included in the output and not aggregated are used to create the groups. The data is aggregated first in memory and then it is merged with the data already in the table, using the group by columns as the join key.

There is one important rule to respect though: the name of the aggregated column cannot be the same as any other column from the events, so make sure to assign a new name.

Let’s save the file as c:tempRecipe_06_Login_Audit.json and run XESmartTarget:

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

The console window informs us that the rows are not being written directly to the target table, but they are grouped and aggregated according to the configuration.

Inspecting the data in the target table confirms that we have indeed 7 rows:

If you let XESmartTarget run for a while, it will keep writing more data to the target tables and it will update the counts, merging the data from memory with the data already in the table:

If you want to identify unused logins, all you have to do is run XESmartTarget for a meaningful amount of time (let’s say one month) and it will fill the data in the target table. Easy peasy.

Recap

GroupedTableAppenderResponse is a powerful tool to perform aggregations on the events captured by Extended Events sessions. It can be used to accomplish a lot of tasks.

In the next recipe you will learn how to use the GroupedTableAppenderResponse to identify unused tabled in the database. Keep watching the XESmartTarget tag!