SQL Server EE捕获登录计数：实现单账号计数上限10的过滤

要实现每个登录账号的计数最多显示10次，不能直接在事件层面用WHERE [package0].[counter] <=10过滤（这会只捕获前10次所有账号的登录事件），而是需要通过会话级变量跟踪每个账号的独立计数，在事件触发时动态判断是否继续捕获该账号的登录行为。

具体配置方案 #

创建带会话变量和原子操作的Extended Event会话，仅捕获每个账号的前10次登录事件，最终histogram目标的计数自然不会超过10：

CREATE EVENT SESSION [LoginCountWithLimit] ON SERVER 
ADD EVENT sqlserver.successful_login(
    ACTION(sqlserver.username)
    WHERE (
        -- 原子更新每个账号的登录计数，仅当计数<=10时捕获事件
        package0.atomic_compare_exchange(
            @login_counts,
            sqlserver.username,
            COALESCE(package0.lookup(@login_counts, sqlserver.username), 0) + 1,
            COALESCE(package0.lookup(@login_counts, sqlserver.username), 0)
        ) <= 10
    )
)
ADD TARGET package0.histogram(
    SET source=N'sqlserver.username',
        source_type=(0)
)
WITH (
    MAX_MEMORY=4096 KB, 
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS, 
    STARTUP_STATE=OFF
);

配置说明 #

1. 事件选择：使用sqlserver.successful_login捕获成功登录事件，若需包含失败登录，可替换为sqlserver.login。
2. 会话变量：@login_counts是自动创建的package0.string_map类型变量，键为用户名，值为该账号的登录计数，会话重启后计数重置。
3. 原子操作：package0.atomic_compare_exchange保证并发场景下计数更新的准确性，避免竞态条件：
   • 先通过package0.lookup获取当前账号的已有计数（不存在则为0）
   • 尝试将计数加1，若原计数未被并发修改，则更新为新值
   • 返回更新后的计数，判断是否<=10，仅满足条件的事件会被捕获
4. Histogram目标：按用户名分组统计捕获到的事件次数，最终每个账号的计数最多为10。

查看统计结果 #

启动会话后，可通过以下SQL查询histogram目标的统计数据：

SELECT 
    target_data.value('(/HistogramTarget/Slot/@count)[1]', 'int') AS LoginCount,
    target_data.value('(/HistogramTarget/Slot/@value)[1]', 'nvarchar(128)') AS Username
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_sessions s
    JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
    WHERE s.name = 'LoginCountWithLimit'
        AND t.target_name = 'histogram'
) AS data;

注意事项 #

• 会话变量仅在EE会话运行期间有效，重启会话后所有计数会重置。
• 若需持久化历史计数，可额外添加event_file目标配合后期分析，但会增加资源占用，需根据需求权衡。

内容的提问来源于stack exchange，提问作者mnDBA