DBA Scripts: Get XE Session Activity

Standing up a trace session is the easy part. The actual decision, is this database safe to decommission, is that service account still connecting from the right host, comes from reading the results back and turning raw events into an answer. Extended Events writes to rolling .xel files, and reading those directly means shredding XML by hand every single time, exactly the kind of task worth writing once and never doing manually again.

This script reads a named session’s file target, shreds every captured event, and rolls it up into unique caller combinations, login, hostname, application, database, with an occurrence count and a first-seen/last-seen time range for each. Point it at Create Decommission Audit Session or Create Login Activity Session output and you get a straight answer instead of a pile of raw events.

Why XE Session Activity Matters

The value of any trace session is entirely in how it gets read back:

• A .xel file full of raw events isn’t an answer by itself, it needs grouping and summarising before “who’s connecting to this” turns into “these three applications, from these two hosts.”
• The first_seen/last_seen/span_hours columns turn a pile of events into a timeline, whether a caller showed up once by accident or has been connecting steadily across the whole capture window.
• This is the script named directly in the two Create scripts’ own headers as the intended way to review their output, they’re a pair by design, not a coincidence.

When to Run This Script

• After letting Create Decommission Audit Session run for its full observation window, to see exactly who’s still connecting before you decommission anything
• After letting Create Login Activity Session run, to build the list of accounts, hosts, and applications actually using a server
• Periodically during a long-running capture, to check progress rather than waiting until the very end to look
• Before removing a session with Remove XE Session, review its captured data one last time

The Script

Run the following script against your SQL Server instance.

/*
Script Name : Get-XeSessionActivity
Category    : traces
Purpose     : Reads and summarises Extended Events file target data for a named session.
              Returns unique caller combinations (login, hostname, app, database) with occurrence counts and time range.
              Primary use: reviewing DecommissionAudit or LoginActivity session output to determine if a database or server is still in active use.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-xe-session-activity/)
Requires    : VIEW SERVER STATE, read access to the XE output folder
Notes       : Windows path handling (backslash split); adjust the separator for a Linux
              file target. Large captures take real time to shred - a full 14-file rollover
              set at the Create scripts' defaults exceeded a 10-minute query timeout on the
              lab; archive old .xel files first if you only need the current window.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

/* ── Configuration ───────────────────────────────────────────────────────── */
DECLARE @SessionName NVARCHAR(128) = N'DecommissionAudit';
/* ─────────────────────────────────────────────────────────────────────────── */

/* Locate the file target path from the running session metadata */
DECLARE @FilePath NVARCHAR(500);

SELECT @FilePath =
    CAST(
        CAST(t.target_data AS XML).value(
            '(EventFileTarget/File/@name)[1]', 'nvarchar(500)')
    AS NVARCHAR(500))
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON t.event_session_address = s.address
WHERE s.name = @SessionName
  AND t.target_name = 'event_file';

IF @FilePath IS NULL
BEGIN
    /* Session stopped but definition still exists — get path from session fields */
    SELECT @FilePath = CONVERT(NVARCHAR(500), f.value)
    FROM sys.server_event_sessions ses
    JOIN sys.server_event_session_targets t ON t.event_session_id = ses.event_session_id
                                             AND t.name = 'event_file'
    JOIN sys.server_event_session_fields f ON f.event_session_id = ses.event_session_id
                                             AND f.object_id = t.target_id
                                             AND f.name = 'filename'
    WHERE ses.name = @SessionName;
END;

IF @FilePath IS NULL
BEGIN
    RAISERROR('Session "%s" not found. Check the session name and ensure it has been created.', 16, 1, @SessionName);
    RETURN;
END;

/* Build wildcard pattern: extract folder + session name + *.xel */
DECLARE @Folder NVARCHAR(500) = LEFT(@FilePath, LEN(@FilePath) - CHARINDEX(N'\', REVERSE(@FilePath)));
DECLARE @Pattern NVARCHAR(500) = @Folder + N'\' + @SessionName + N'*.xel';

/* Read, shred, and summarise */
;WITH raw AS (
    SELECT
        e.value('(event/@name)[1]', 'nvarchar(128)') AS event_name,
        e.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS database_name,
        e.value('(event/action[@name="username"]/value)[1]', 'nvarchar(128)') AS username,
        e.value('(event/action[@name="nt_username"]/value)[1]', 'nvarchar(128)') AS nt_username,
        e.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(128)') AS client_hostname,
        e.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS client_app_name,
        CAST(e.value('(event/@timestamp)[1]', 'nvarchar(30)') AS DATETIME2) AS event_time
    FROM (
        SELECT CAST(event_data AS XML) AS e
        FROM sys.fn_xe_file_target_read_file(@Pattern, NULL, NULL, NULL)
    ) AS src
)
SELECT
    event_name,
    /* An action that was collected but empty comes back as '', not NULL, so a plain
       COALESCE never falls through - NULLIF makes both forms behave the same */
    COALESCE(NULLIF(database_name, N''), '(server-level)') AS database_name,
    COALESCE(NULLIF(nt_username, N''), NULLIF(username, N''), N'(unknown)') AS login_name,
    client_hostname,
    client_app_name,
    COUNT(*) AS occurrences,
    /* whole-second precision - this is a who-and-when summary, not a latency trace */
    CAST(MIN(event_time) AS DATETIME2(0)) AS first_seen,
    CAST(MAX(event_time) AS DATETIME2(0)) AS last_seen,
    DATEDIFF(HOUR, MIN(event_time), MAX(event_time)) AS span_hours
FROM raw
GROUP BY event_name, database_name, nt_username, username, client_hostname, client_app_name
ORDER BY occurrences DESC, database_name, login_name;

This locates the named session’s output file (falling back to the session definition if it’s been stopped), builds a wildcard pattern to catch every rollover file, shreds each event’s XML into login, hostname, application, and database fields, then groups and counts by that unique combination, returning one row per distinct caller with how often they showed up and across what time span.

Change @SessionName before running. It defaults to DecommissionAudit, edit the DECLARE at the top of the script to point at LoginActivity, SpExecution, or any other session you’ve created.

How To Run From The Repo

Clone DBA Tools, initialize and run the script:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Summarise a named session's captured file target data:
.\run.ps1 Get-XeSessionActivity

# To run against a remote sql server:
.\run.ps1 Get-XeSessionActivity -ServerInstance SQLSERVER01

This script lives in the repo at:

• sql/traces/Get-XeSessionActivity.sql
• powershell/wrappers/traces/Get-XeSessionActivity.ps1

Example Output

Two captures, two stories. The screenshot above is a fresh DecommissionAudit run only minutes old, and even that short window already answers the question: on a quiet lab, SQL Agent’s own job machinery dominates every count, SSMS IntelliSense chats away in the background, and a collector job step even logged two real error_reported events. A longer capture tells an even better story; what a month of the same read looks like is covered under Understanding the Results below.

Understanding the Results

And what a longer capture looks like: this script has also been pointed at a LoginActivity session that ran on this lab, on and off, for over a month. The loudest caller by far was SQL Server itself; its own telemetry service logged in every couple of minutes and racked up more events than everything else combined, with SQL Agent’s job machinery filling most of the rest. “Who’s connecting” always includes the instance’s own automation, not just applications and people.

Two behaviours of that capture are worth knowing before you trust your own. The wildcard read picks up every rollover file matching the session name, so recreating a session does not reset history unless the old .xel files are actually deleted from disk. And the row that justified the whole exercise was a deliberately triggered failed login, captured as error_reported with login_name reading (unknown) because the attempt never resolved to a valid principal. In production, that pattern repeating against a real target is worth investigating immediately, not just noting.

Best Practices

• Run this against a session that’s been capturing for a meaningful window, several business days at minimum, a single hour of data will understate genuine but infrequent callers.
• Zero rows back doesn’t necessarily mean zero activity, confirm the session is actually running (Get Active XE Sessions) and pointed at the folder you expect before trusting a quiet result.
• Old .xel files from a previous incarnation of a same-named session stay on disk and get picked up by the wildcard read, if you want a genuinely clean window, delete the old files, don’t just recreate the session.
• On a session with high event volume, this query has real XML shredding work to do and can take minutes rather than seconds on a full set of rollover files. If you only need the current window, archive the old .xel files out of the folder first.
• The path handling assumes a Windows file target (backslash separators). On a Linux SQL Server, adjust the separator logic or the wildcard read will find nothing and look like a missing session.
• Read the results before deciding anything is safe to remove, span_hours and occurrences together tell you whether a caller is a one-off or a steady, ongoing pattern.

Microsoft’s reference covers sys.fn_xe_file_target_read_file, sys.dm_xe_sessions, and sys.dm_xe_session_targets in full.

Related Scripts

You may also find these scripts useful:

• Create Decommission Audit Session
• Create Login Activity Session
• Create SP Execution Session
• Get Active XE Sessions
• Remove XE Session
• DBA Scripts: The Complete Guide, the map across every script on this site

Frequently Asked Questions

Summary

A trace session’s output is only as useful as your ability to read it back and turn it into a decision. This script does the XML shredding once, so you get a clean summary of who’s actually connecting, calling, or running, complete with how often and over what window, instead of a raw event file nobody wants to page through by hand.

Point it at any session in this series once it’s had time to capture a real pattern, and let the occurrence counts and time spans do the talking.