2

I am using SQL Profiler in MS SQL Server. I can filter data columns by clicking 'Column Filters'. But this filter applies to all events. I want to filter login only for 2 events. Like, audit login and logout only for specified login, but all other events for all logins. Is it possible to do that, and how if yes. Thanks for your time!

enter image description here

3

Unfortunately, this can't be done in the trace definition. Each filter defined executes sp_trace_setfilter filter which groups filters by column, but applies all the filters as a whole...

In the trace definition snippet below it ends up grouped like this:

(DatabaseId = 5 OR DatabaseID = 6) AND (DatabaseId <> 1 AND DatabaseID <> 0) AND (Login like User1 OR Login like User2)

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

/******Filter by database ID include*****/
SET @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter  -- 3,0,0 = ColumnID, logical operator (and/or), comparison operator (0 = EQUAL, 1 = NOT EQUAL)

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

/******Filter by database ID exclude*****/
SET @intfilter = 1
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

set @intfilter = 0
exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

/******Filter by database Login LIKE*****/
exec sp_trace_setfilter @TraceID, 11, 0, 6, N'user1' -- 11,0,6 = ColumnID, logical operator (and/or), comparison operator (6 = LIKE)
exec sp_trace_setfilter @TraceID, 11, 1, 6, N'user2'

For real-time filtering you'd need to use something like the Save to table option and filter a query against the output there.

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.