CODEWITHSUNDEEP
sql-serverextended-events
mnDBA
mnDBA

Reputation: 139

Extended Event - Capture Login Count - Filter on Action

I need to verify is logins on my SQL Server are being utilized. I can think of a couple of ways to do this, but want to try to use EE.

The initial need is very basic, I just need the username and a count of logins while the session is running. This is easy enough, as I can do this:

CREATE EVENT SESSION [LoginCounter]
ON SERVER
    ADD EVENT sqlserver.login
    (ACTION
     (
         sqlserver.username
     )
    )
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.login', source = N'sqlserver.username')
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

However, the counts can get pretty high and honestly I do not need to know if the count > 10 and there's no need to spend the resources to gather that.

enter image description here

In a perfect world, I would love to be able to filter the counts, inline, so it will show at max 10 for each login. I'm looking for a way to do that in the EE definition but I do not think I can add a Filter to an Action in this manner. I hope I am wrong.

Very similar in concept, I would like it to act like this:

CREATE EVENT SESSION [LoginCounter]
ON SERVER
    ADD EVENT sqlserver.login
    (ACTION
     (
         sqlserver.username
     )
     WHERE ([package0].[counter] <= (10))
    )
    ADD TARGET package0.event_counter
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

but this just filters at the event level

enter image description here

If I leave the filter in place with the first example, I just get the first 10 occurrences for any login and then it stops. That makes sense, as the filter is at the package level. I would love to be able to filter at the action level.

CREATE EVENT SESSION [LoginCounter]
ON SERVER
    ADD EVENT sqlserver.login
    (ACTION
     (
         sqlserver.username
     )
     WHERE ([package0].[counter] <= (10))
    )
    ADD TARGET package0.histogram
    (SET filtering_event_name = N'sqlserver.login', source = N'sqlserver.username')
WITH
(
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

enter image description here

What I am really hoping for is this format

enter image description here

Any help would be appreciated. Hope I am missing something simple.

Upvotes: 0

Views: 74

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89371

I don't think there is any significant performance reason to do that.

First, Login is not a high frequency event. Second, adding one to the value of a histogram bucket is cheap.

Upvotes: 1

Related Questions