November 27, 2019 at 7:14 am
CREATE EVENT SESSION [AL_Alter] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text)
WHERE ([sqlserver].[database_name]=N'ABCDEF' AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'ALTER')
AND [sqlserver].[session_nt_user]<>N'ABCD\EFGH'))
ADD TARGET package0.event_file(SET filename=N'C:\ABCD\ExtendedEvents\AL_Alter',max_file_size=(5),max_rollover_files=(1000))
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
I am trying capture only ALTER statements which are executed on ABCDEF database and the session_nt_user can be anybody but this user-> ABCD\EFGH.
So anybody besides ABCD\EFGH who is running ALTER statements should be captured in the AL_Alter session.
My session_nt_user is ZZZ\DDD. And I excecuted an ALTER table statement which adds some columns to a table TableA.
But the session is not capturing my ALTER activity.Why is that so?
November 27, 2019 at 1:34 pm
Break it all down into pieces for testing. The easy part is the database name of course. But, next, test that you're capturing only statements with ALTER in them. See how it returns the user names. Test it with the user name you wish to exclude so that you can see the string in order to get the comparison right. Then add that in. Taking it apart and testing each piece separately is the best way to get the filtering right. Same thing I'd do with a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2019 at 9:28 pm
A bit of a hint:
What strings would the TSQL comparison LIKE 'ALTER' match? 🙂
Cheers!
November 28, 2019 at 4:20 am
Are you saying 'alter' works and not 'ALTER' ?
November 28, 2019 at 6:27 am
I tried to filter starting with one filter as suggested and still does not work.Obviously I am doing this completely wrong.Any suggestions please.I still find rows with AAAA\ZZZZ against nt_username column in the watch live window when they should have be ignored as per the SQL below.
I tried
WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[session_nt_user],N'AAAA\ZZZZ')))
WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[nt_user],N'AAAA\ZZZZ')))
Both filters are not working and I still find data with AAAA\ZZZZ.
And is nt_user and nt_username the same ?
Under ACTIONS it is : sqlserver.session_nt_username and sqlserver.nt_username
But under filters/predicates it is only nt_user and sqlserver.session_nt_username? Id nt_user in filter and nt_username under actions the same field we are referring to ?
Thanks
CREATE EVENT SESSION [AuditLogins_Alter] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1)
ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text)
WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[session_nt_user],N'AAAA\ZZZZ'))),
ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text)
WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[session_nt_user],N'AAAA\ZZZZ')))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvents\AL_Alter',max_file_size=(5),max_rollover_files=(1000))
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
November 28, 2019 at 6:52 am
When I use database_name as the only filter it works well.
But when I add nt_user /session_nt_username under predicates everything goes for a toss.I get data I do not want to see.
Rows from other databases show up etc..
I need to capture activities which happend on a particular database and by any user barrinf AAAA\ZZZZ.
Cant get this to work.
I read that predicate order is important and obviously this is giong above my head.
Responses are appreciated.
Thanks
November 29, 2019 at 12:43 am
You can't reference Action values in the predicate. Here are the values on which you can filter:
-- What Predicates (fields on which we can filter) are available?
SELECT p.name AS [Package], o.name AS [PredicateField], o.object_type, o.description
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
WHERE o.object_type LIKE 'pred_source' AND (o.name LIKE '%domain%' or o.name LIKE '%user%')
ORDER BY o.name;
/* results:
Package PredicateField object_type description
------------ -------------------- ------------- ---------------------------------------
sqlserver nt_domain pred_source Get the current NT domain
sqlserver nt_user pred_source Get the current NT user
sqlserver session_nt_domain pred_source Get the current session NT domain
sqlserver session_nt_user pred_source Get the current session NT user
sqlserver username pred_source Get the current username */
Note that there are separate fields for user name and domain name. Here's where you had issues: nt_user and session_nt_user do not contain the domain name. That's what nt_domain and session_nt_domain are for. I'll use [nt_user] and [nt_domain] for this.
Also, because you are only looking for ALTER commands, use the Event for that. Review the results of this query:
-- Is there an Event for ALTER?
SELECT p.name + '.' + o.name AS [Event], o.description
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
WHERE o.object_type = 'event' AND o.name like '%alter%';
GO
The Event sqlserver.object_altered looks like a good place to start. Description:
"Occurs when an object was altered by the ALTER statement. This event is raised
two times for every ALTER operation. The event is raised when the operation begins and when the
operation is either rolled back or committed. Add the nt_username or server_principal_name actions
to this event to determine who altered the object."
-- Check its columns:
SELECT * FROM sys.dm_xe_object_columns oc
WHERE oc.object_name = 'object_altered'
ORDER BY oc.column_type, oc.column_id;
GO
There's a SET column for capturing the database name. Because you are tracing only a specific database, leave this OFF for better performance (you know what the DB name is). Also, filter on database_id instead of the database name.
SELECT DB_ID('ABCDEF') AS [Filter database_id];
GOSlapping that all together into a Session definition (example uses database_id = 6):
IF EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name = 'AlterWatchr')
DROP EVENT SESSION AlterWatchr ON SERVER;
GO
CREATE EVENT SESSION AlterWatchr ON SERVER
ADD EVENT sqlserver.object_altered (SET collect_database_name = 0
ACTION (sqlserver.nt_username,sqlserver.session_nt_username, sqlserver.sql_text)
WHERE (database_id = 6 AND NOT (nt_domain = 'ABCD' AND nt_user = N'EFGH')))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 5 SECONDS,MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF);
GO
ALTER EVENT SESSION AlterWatchr ON SERVER STATE = START;
GO
...with a little XQuery to pull the info out of the trace:
-- Run from the context of the target database so
-- the joins to sys.objects and sys.indexes work
USE ABCDEF; -- replace with db name
GO
WITH
AlterWatchrEvents(EventSession, LatchEventData) AS
(SELECT xes.name AS [EventSession], CONVERT(xml, xest.target_data) AS LatchEventData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer' AND xes.name = 'AlterWatchr'
),
AlterEventDetails AS (
SELECT AlterEventNode.LatchEvent.value('(@name)[1]', 'varchar(128)') AS EventType,
AlterEventNode.LatchEvent.value ('(@timestamp)[1]', 'datetime') AS EventTime,
AlterEventNode.LatchEvent.value ('(data[@name="database_id"]/value)[1]', 'smallint') AS [database_id],
DB_NAME(AlterEventNode.LatchEvent.value ('(data[@name="database_id"]/value)[1]', 'smallint')) AS [database_name],
AlterEventNode.LatchEvent.value ('(data[@name="object_id"]/value)[1]', 'bigint') AS [target_object_id],
AlterEventNode.LatchEvent.value ('(data[@name="object_type"]/text)[1]', 'nvarchar(32)') AS [target_object_type],
AlterEventNode.LatchEvent.value ('(data[@name="index_id"]/value)[1]', 'int') AS [target_index_id],
AlterEventNode.LatchEvent.value ('(data[@name="related_object_id"]/value)[1]', 'bigint') AS [related_object_id],
AlterEventNode.LatchEvent.value ('(data[@name="ddl_phase"]/text)[1]', 'varchar(16)') AS [ddl_phase],
AlterEventNode.LatchEvent.value ('(data[@name="transaction_id"]/value)[1]', 'bigint') AS [transaction_id],
AlterEventNode.LatchEvent.value ('(data[@name="object_name"]/value)[1]', 'nvarchar(256)') AS [object_name],
AlterEventNode.LatchEvent.value ('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [SQLText],
AlterEventNode.LatchEvent.value ('(action[@name="session_nt_username"]/value)[1]', 'nvarchar(256)') AS [session_nt_username],
AlterEventNode.LatchEvent.value ('(action[@name="nt_username"]/value)[1]', 'nvarchar(256)') AS [nt_username]
FROM AlterWatchrEvents
CROSS APPLY LatchEventData.nodes('//RingBufferTarget/event') AS AlterEventNode(LatchEvent)
)
SELECT EventTime, database_name, target_object_type, N'[' + s.name + N'].[' + object_name + N']' AS target_object,
IsNull(i.name, '') AS [target_index], aed.target_object_id, aed.target_index_id, related_object_id, ddl_phase, SQLText, session_nt_username, nt_username
FROM AlterEventDetails aed
-- LEFT JOIN the object tables because this Event also captures ALTER DATABASE commands
LEFT JOIN sys.objects o ON o.object_id = aed.target_object_id
LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id
LEFT JOIN sys.indexes i ON aed.target_index_id != 0 AND aed.target_index_id = i.index_id
ORDER BY 2;
This should get you on your way.
-Eddie
Eddie Wuerch
MCM: SQL
November 29, 2019 at 9:24 am
Thanks for the elaborate reply but looks like I still cannot get this to work.
My Query:
CREATE EVENT SESSION [AlterExtendedEvent] ON SERVER
ADD EVENT sqlserver.object_altered(SET collect_database_name=(0)
ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text)
WHERE ([database_name]='AAAAAA' AND NOT ([nt_domain]='ABC' AND [nt_user]=N'DEF')))
ADD TARGET package0.event_file(SET filename=N'E:\ExtendedEvents\AletExtendedEvent.xel',max_file_size=(5),max_rollover_files=(1000))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
Under database AAAAAA I created a table A with 2 columns.Then I executed the alter statement to add columns to this table.But the alter table was not captured in the extended event window.Any thoughts.I presume that if i run any kind of alter statement (alter indexes ,alter table etc...)it should be captured here. But that does not seem to the case.
Thanks
November 29, 2019 at 10:22 am
Please ignore my previous comment.
Thanks for the response
November 29, 2019 at 3:58 pm
I'm glad you seem to have gotten this working with Eddie's solution.
For the record, my hint about the original session's capturing zero events was not related to the case of the string.
Without wildcards, TSQL's LIKE and XE's like_i_sql_unicode_string evaluate TRUE for exact matches only, so your original filter would match only completed statements that contained nothing more than the single word 'ALTER', which would naturally be 0 events.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply