DBA Scripts: Get XE Session Activity

Peter Whyte

–

Part of the DBA-Tools Project.

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
*/
-- 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,
    COALESCE(database_name, '(server-level)')   AS database_name,
    COALESCE(nt_username, username)              AS login_name,
    client_hostname,
    client_app_name,
    COUNT(*)                                     AS occurrences,
    MIN(event_time)                              AS first_seen,
    MAX(event_time)                              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:

Example Output

event_name database_name login_name client_app_name occurrences span_hours
login master NT SERVICE\SQLTELEMETRY SQLServerCEIP 20953 827
login master HPAI01\Peter Core Microsoft SqlClient Data Provider 610 827
login msdb NT SERVICE\SQLSERVERAGENT SQLAgent – Job Manager 604 587
login WatchtowerMetrics NT SERVICE\SQLSERVERAGENT SQLAgent – TSQL JobStep (Job 0x1B78…) 265 587
login msdb NT SERVICE\SQLSERVERAGENT SQLAgent – Schedule Saver 181 587
login master HPAI01\Peter SSMS IntelliSense 106 705
login RandomLab HPAI01\Peter Core Microsoft SqlClient Data Provider 86 104
error_reported master (blank) Core Microsoft SqlClient Data Provider 2 0

Real output, read back from a LoginActivity session pointed at a local SQL Server 2025 instance that’s genuinely been running this trace, off and on, for over a month, 36 distinct rows in total, trimmed here to the top by occurrence count plus one row worth calling out specifically. That last row is a deliberately triggered failed login (sqlcmd/Invoke-Sqlcmd with a bad SQL login and a wrong password), captured as a real error_reported event with login_name blank because the login attempt never resolved to a valid principal.

Understanding the Results

  • occurrences tells you what’s routine before you even look at anything else. The SQLServerCEIP telemetry service alone accounts for nearly 21,000 of the 36 rows’ worth of events above, SQL Server’s own telemetry, logging in roughly every two and a half minutes, was the single loudest caller on this instance by a wide margin.
  • span_hours is 827 for the top rows here, meaning this trace has genuinely captured over a month of real activity. sys.fn_xe_file_target_read_file‘s wildcard pattern reads every rollover file matching the session name, not just the ones from the session’s most recent start, so recreating a session with Create-LoginActivitySession doesn’t reset this history unless the old .xel files are actually deleted from disk.
  • The error_reported row with a blank login_name is exactly the kind of finding this session exists to catch, a failed login that never resolved to a real account. In production, a pattern like this repeating against a real login name is worth investigating immediately, not just noting.
  • SQL Agent’s own service account shows up constantly here (SQLAgent - Job Manager, SQLAgent - Schedule Saver, and multiple job-step logins), a reminder that “who’s connecting” always includes the instance’s own automation, not just applications and people.

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, particularly a DecommissionAudit capture left with no database filter on a busy instance, this query has real XML shredding work to do and can take noticeably longer to return than a filtered, lower-volume 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.

Related Scripts

You may also find these scripts useful:

Frequently Asked Questions

How do I read Extended Events .xel file data with T-SQL?

Use sys.fn_xe_file_target_read_file, pointed at the file path (a wildcard pattern catches rollover files), then shred the returned event_data XML column with .value() calls for the fields you need. This script does exactly that and rolls the result up into a summary, rather than leaving you with one row per raw event.

Why does this script return no rows for a session I know is running?

The most common cause is a session that hasn’t hit MAX_DISPATCH_LATENCY yet, events sit buffered before they’re flushed to the file target, so a session that’s only been running for a few seconds may genuinely have nothing written to disk. Give it a minute, then check MAX_DISPATCH_LATENCY in the session definition if it’s still empty after that.

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.

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *