Logging errors with extended events excluding some users

Question

I use extended events to store database errors, like this:

CREATE EVENT SESSION [ErrorCapture] 
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE 
    (
        [severity] >= (11)
    )
) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works. I have some users called test1, test2 and so on that work interactively, and produce several errors. I don't want to log these errors. I tried to change the WHERE clause like this:

WHERE 
(
    [severity] >= (11) AND sqlserver.username NOT LIKE 'test%'
)

I get these errors:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'LIKE'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'TARGET'.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

How can I exclude the errors from some users from my logging?

Kin Shah · Accepted Answer · 2013-11-08 17:54:47Z

Since you are using sql 2008R2, you cannot use like predicate comparisons as they were introduced with SQL Sever 2012.

If you are on SQL 2012, then you can use below query to find what is available :

select xp.name, xo.name, xo.description, xo.type_name, xo.type_size from
sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp on xo.package_guid=xp.guid
where object_type like 'pred_compare'

SQL 2008 R2 does not have like_i_sql_unicode_string vs SQL 2012 has it.

enter image description here

So, below should work on SQL 2012

CREATE EVENT SESSION [ErrorCapture] 
ON SERVER 
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE (
            [severity] >= (11) AND
            (NOT sqlserver.like_i_sql_unicode_string(sqlserver.username,N'test%'))

    )

) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename='C:\ErrorCapture_Extended_Event.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

Refer: Connect item Extended events predicate - NOT LIKE required

Stack Exchange Network

Logging errors with extended events excluding some users

1 Answer 1

Your Answer

Hot Network Questions

Logging errors with extended events excluding some users

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions