Sql-server – Can SQL Profiler filter data columns only for one event

profilersql server

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

Best Answer

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.

Related Question