2

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.

0

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.

  • That looks like a legit solution, thanks! – Michael Cherevko Jul 24 '16 at 5:16
1

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.

  • Thanks, but where are only a few logins that are allowed to connect to our prod servers that we need to audit, we collect other events to audit actions of another users. I've never heard about histogram target, I'll check it also! – Michael Cherevko Jul 24 '16 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.