Filtering extended events with configuration table

Question

I've created E.E. session to audit logins, but there are many logins that I need to filter out.
I'm wondering, if there is a better option(like creating conf. table with unwanted logins and filter sqlserver.username using those values) than just adding them one by one via gui\script.

Answer 1

Filters along the lines of sqlserver.username IN (SELECT name FROM sometable) are not allowed.

You can use dynamic SQL to build the filter string and concatenate it to your CREATE SESSION statement. Take into account that there's a limit to the length of the list of values. I remember that because I tried the same thing some time ago, but I don't remember what the limit is.

Answer 2

I would say it's better to collect the information then filter out at reporting time, because then at least you have it. Constantly updating the session seems fiddly and someone else might be able to make use of it: "Oh I hear you are tracking logins over time, do you track login X?"

Also, have you thought about using the histogram target? This might be a good use case for it. There would be a single entry per login with a count property indicating how many times that login has logged in during that extended event session. You would have to record the start and end time of the Extended Events (XE) session for it to be meaningful, but here's a simple example:

IF EXISTS ( SELECT * from sys.server_event_sessions WHERE name = 'xe_watchLogins' )
DROP EVENT SESSION [xe_watchLogins] ON SERVER 
GO

CREATE EVENT SESSION [xe_watchLogins] 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 = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF )
GO


ALTER EVENT SESSION [xe_watchLogins]
ON SERVER
STATE = START;
GO

/* Sample output from the histogram
<HistogramTarget truncated="0" buckets="256">
    <Slot count="4">
        <value>someDomain\bob</value>
    </Slot>
    <Slot count="1">
        <value>someSqlUser</value>
    </Slot>
</HistogramTarget>
*/

SELECT IDENTITY( INT, 1, 1 ) AS rowId, GETDATE() AS dateCreated, CAST( st.target_data AS XML ) XEhistogramData
INTO #tmp
FROM sys.dm_xe_sessions s
    INNER JOIN sys.dm_xe_session_targets st ON s.[address] = st.event_session_address 
WHERE st.target_name = 'histogram' 
  AND s.name = 'xe_watchLogins'


-- Optionally create xml index
ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( XEhistogramData );
GO

SELECT
    h.c.value('(value/text())[1]', 'SYSNAME') AS loginName,
    h.c.value('@count', 'INT') AS logins
FROM #tmp t
    CROSS APPLY t.XEhistogramData.nodes('HistogramTarget/Slot') AS h(c)
-- WHERE NOT EXISTS ( ... )

The histogram defaults to 256 buckets or distinct login names in this example.