August 30, 2021 at 5:47 pm
@Grant
CREATE EVENT SESSION [MySession1] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,
sqlserver.server_principal_name,sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(10240))
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 have a smiliar session which dumps data into a .xel file.
CREATE EVENT SESSION [MySession2] ON SERVER
ADD EVENT sqlserver.login(SET collect_database_name=(1),collect_options_text=(1)
ACTION(package0.collect_system_time,sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[username],N'NT SERVICE\ReportServer')))
ADD TARGET package0.event_file(SET filename=N'E:\AL.xel',max_file_size=(10),max_rollover_files=(4000))
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=ON,STARTUP_STATE=ON)
GO
These events capture a "lot" of data I do not need.As I mention 1 row for every DML/any activity should be enough like SQLAudit captures.
Thanks
August 30, 2021 at 9:20 pm
You are collecting every single statement. Of course it's a lot. I'd suggest changing that to batch & rpc completed. You'll still get a ton, but not as much as you get with every statement. That login event should only fire once per login, so if you're seeing more than one, something is up.
"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
September 2, 2021 at 5:29 am
I still see a lot of data. Is it possible to confine the result set to 1 record/row per dml activity in extended events.
September 2, 2021 at 12:15 pm
You get one row per batch if you collect batch_completed. You get one row per procedure call if you collect rpc_completed. You get one row per statement if you collect statement_completed. That's just how it works.
"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
September 6, 2021 at 7:40 am
@Grant and All
When I run a select ... this is what I see in the result set under the statement column when all I want is the last line...select * from MYTABLENAME(like database sqlaudit does)
How do I filter the rest of the records.
I used sql_statement_completed as suggested.
Advise/References would help.
CREATE EVENT SESSION [MyEvent] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[server_principal_name],N'ABCD%') AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[username],N'EFGH')))
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
select @@trancount
SET LOCK_TIMEOUT 10000
SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname);
SELECT case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType', SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition, SERVERPROPERTY('ProductVersion') AS ProductVersion, @@MICROSOFTVERSION AS MicrosoftVersion
select N'Windows' as host_platform
if @edition = N'SQL Azure'
exec ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol')
select @HkeyLocal=N'HKEY_LOCAL_MACHINE'
select @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
select @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'
select @FilestreamRegPath=@InstanceRegPath + N'\Filestream'
select @SetupRegPath=@MSSqlServerRegPath + N'\Setup'
select @RegPathParams=@InstanceRegPath+'\Parameters'
select @ServicesRegPath=N'SYSTEM\CurrentControlSet\Services'
select @SqlServiceRegPath=@ServicesRegPath + N'\MSSQLSERVER'
select @BrowserServiceRegPath=@ServicesRegPath + N'\SQLBrowser'
select @InstanceNamesRegPath=N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
select @NpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Np'
select @TcpRegPath=@InstanceRegPath + N'\SuperSocketNetLib\Tcp'
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'NumErrorLogs', @NumErrorLogs OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'LoginMode', @SmoLoginMode OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'MailAccountName', @SmoMailProfile OUTPUT
if 1=isnull(cast(SERVERPROPERTY('IsLocalDB') as bit), 0)
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'BackupDirectory', @BackupDirectory OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'Performance', @SmoPerfMonMode OUTPUT
if @SmoPerfMonMode is null
set @SmoPerfMonMode = 1000
exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLDataRoot', @InstallSqlDataDir OUTPUT
select @MasterPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'master'
select @LogPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) from master.sys.database_files where name=N'mastlog'
select @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512))
select @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)))
exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @SmoRoot OUTPUT
EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT
EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT
EXEC master.sys.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLPath', @InstallSharedDirectory OUTPUT
EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'Start', @ServiceStartMode OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @SetupRegPath, N'SQLGroup', @SqlGroup OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'EnableLevel', @FilestreamLevel OUTPUT
exec master.dbo.xp_instance_regread @HkeyLocal, @FilestreamRegPath, N'ShareName', @FilestreamShareName OUTPUT
BEGIN TRY
SELECT @cluster_name = cluster_name, @quorum_type = quorum_type, @quorum_state = quorum_state FROM sys.dm_hadr_cluster
END TRY
SELECT @SmoAuditLevel AS [AuditLevel], ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles], (case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode], ISNULL(@SmoMailProfile,N'') AS [MailProfile], @BackupDirectory AS [BackupDirectory], @SmoPerfMonMode AS [PerfMonMode], ISNULL(@InstallSqlDataDir,N'') AS [InstallDataDirectory], CAST(@@SERVICENAME AS sysname) AS [ServiceName], @ErrorLogPath AS [ErrorLogPath], @SmoRoot AS [RootDirectory], CAST(case when 'a' <> 'A' then 1 else 0 end AS bit) AS [IsCaseSensitive], @@MAX_PRECISION AS [MaxPrecision], CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled], SERVERPROPERTY(N'ProductVersion') AS [VersionString], CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition], CAST(SERVERPROPERTY(N'ProductLevel') AS sysname) AS [ProductLevel], CAST(ISNULL(SERVERPROPERTY(N'ProductUpdateLevel'), N'') AS sysname) AS [ProductUpdateLevel], CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser], CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition], convert(sysname, serverproperty(N'collation')) AS [Collation], CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered], CAST(SERVERPROPERTY(N'MachineName') AS sysname) AS [NetName], @LogPath AS [MasterDBLogPath], @MasterPath AS [MasterDBPath], SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile], SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog], SERVERPROPERTY(N'ResourceVersion') AS [ResourceVersionString], SERVERPROPERTY(N'ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime], SERVERPROPERTY(N'CollationID') AS [CollationID], SERVERPROPERTY(N'ComparisonStyle') AS [ComparisonStyle], SERVERPROPERTY(N'SqlCharSet') AS [SqlCharSet], SERVERPROPERTY(N'SqlCharSetName') AS [SqlCharSetName], SERVERPROPERTY(N'SqlSortOrder') AS [SqlSortOrder], SERVERPROPERTY(N'SqlSortOrderName') AS [SqlSortOrderName], SERVERPROPERTY(N'BuildClrVersion') AS [BuildClrVersionString], ISNULL(@ServiceAccount,N'') AS [ServiceAccount], CAST(@NamedPipesEnabled AS bit) AS [NamedPipesEnabled], CAST(@TcpEnabled AS bit) AS [TcpEnabled], ISNULL(@InstallSharedDirectory,N'') AS [InstallSharedDirectory], SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], @ServiceStartMode AS [ServiceStartMode], ISNULL(suser_sname(sid_binary(ISNULL(@SqlGroup,N''))),N'') AS [SqlDomainGroup], case when 1=msdb.dbo.fn_syspolicy_is_automation_enabled() and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server%' ) then 1 else 0 end AS [PolicyHealthState], @FilestreamLevel AS [FilestreamLevel], ISNULL(@FilestreamShareName,N'') AS [FilestreamShareName], -1 AS [TapeLoadWaitTime], CAST(SERVERPROPERTY(N'IsHadrEnabled') AS bit) AS [IsHadrEnabled], SERVERPROPERTY(N'HADRManagerStatus') AS [HadrManagerStatus], ISNULL(@cluster_name, '') AS [ClusterName], ISNULL(@quorum_type, 4) AS [ClusterQuorumType], ISNULL(@quorum_state, 3) AS [ClusterQuorumState], SUSER_SID(@ServiceAccount, 0) AS [ServiceAccountSid], CAST(SERVERPROPERTY('IsPolyBaseInstalled') AS bit) AS [IsPolyBaseInstalled], N'Windows' AS [HostPlatform], CAST( serverproperty(N'Servername') AS sysname) AS [Name], CAST( ISNULL(serverproperty(N'instancename'),N'') AS sysname) AS [InstanceName], CAST(0x0001 AS int) AS [Status], N'\' AS [PathSeparator], 0 AS [IsContainedAuthentication], CAST(null AS int) AS [ServerType]
drop table #SVer
SELECT SYSTEM_USER
SET ROWCOUNT 0
SET TEXTSIZE 2147483647
SET NOCOUNT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET LOCK_TIMEOUT -1
SET QUERY_GOVERNOR_COST_LIMIT 0
SET DEADLOCK_PRIORITY NORMAL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET ANSI_NULLS ON
SET ANSI_NULL_DFLT_ON ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CURSOR_CLOSE_ON_COMMIT OFF
SET IMPLICIT_TRANSACTIONS OFF
select @@spid
select SERVERPROPERTY('ProductLevel')
SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname);
SELECT case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType', SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition, SERVERPROPERTY('ProductVersion') AS ProductVersion, @@MICROSOFTVERSION AS MicrosoftVersion
select N'Windows' as host_platform
if @edition = N'SQL Azure'
exec ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol')
SELECT @@SPID
SELECT * FROM MYTABLENAME
Thanks
September 14, 2021 at 12:18 pm
So what you're doing here is creating a whole series of result sets with that dynamic query. There's not a way to put stuff together, of course. Instead, you should be using either, a query that does actual JOINs & such to put the stuff together, or, using a single session and causality tracking to create a single set of behaviors. Personally, I'd probably go with the causality tracking approach.
HOWEVER...
You're capturing just a ton of information. That's putting a load on the system (and causality tracking will add to that) and resulting in lots of data. You have to be prepped to deal with that. Or, have limited run times or filter based on query time or resources or something. Capturing everything, all the time, everywhere, is just inherently expensive. Extended Events are wonderful and powerful, but they're not magic. You can make them hurt your systems if you try.
"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
September 15, 2021 at 5:56 am
@thanks Grant/All
What I need is to track whoever(Users only...not service accounts) has logged into the server and what have those logins done...dml's ..select..insert etc) and track the hostname,application name,database name.
Can you throw some light on this ->causality tracking approach.
I will work on the JOIN query using extended events.....Thanks again.
@Grant - Is there a better way of tracking logins - Can't afford a 3rd party app, server/database specification audits do not capture(app and hostname), no triggers. Thanks
September 15, 2021 at 12:32 pm
I have a few blog posts that show how Causality Tracking works. The concept is really simple. Things like a login and a DML statement are connected. So, group them and order them. However, that grouping and ordering comes with additional overhead. Testing is your friend in all cases.
I can't strongly emphasize enough, I think you're trying to collect way too much data. Managing this is going to be a bit of a nightmare. You may also put undo load on the system.
"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
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply