With SQLAudit you cannot track HostName or IP address.

  • @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

    • This reply was modified 4 weeks ago by  mtz676.
  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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.

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • @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

    • This reply was modified 3 weeks, 1 day ago by  mtz676.
  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • @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

    • This reply was modified 1 week, 6 days ago by  mtz676.
    • This reply was modified 1 week, 6 days ago by  mtz676.
  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 8 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply