I agree an upgrade is long due, but they are application dependent; till that happens I need this code to work.How can I fix it meanwhile.

• This reply was modified 1 year, 7 months ago by  mtz676.

But I need to capture information from databases whose compatibility_level is 80.

exactly.How do I overcome it.

Yes

1.SQL audit files are directly writen to the shared folder from across all servers to ServerA's shared folder.

2.Yes I can access the shared folder on ServerA from ServerB or ServerC...

@Grant Fritchey. Thanks for the response. Will do so.

• This reply was modified 2 years, 10 months ago by  mtz676.
• This reply was modified 2 years, 10 months ago by  mtz676.
• This reply was modified 2 years, 10 months ago by  mtz676.

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

• This reply was modified 3 years, 2 months ago by  mtz676.
• This reply was modified 3 years, 2 months ago by  mtz676.

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

• This reply was modified 3 years, 2 months ago by  mtz676.

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.

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

• This reply was modified 3 years, 3 months ago by  mtz676.

Hi All,

The host name is dependent on the connection string. If people leave this off, it's why you can't audit for it. You can use HOST_NAME to get this information...

@thanks Jeff..I can check the error log way but can I get references on how I can filter the all data but the login records into the .xel...

• This reply was modified 3 years, 3 months ago by  mtz676.

Sounds good to me but unless I am not responding with an answer to the original thread I wasn't sure if it would be appropriate to get into someone's thread...

Thanks @Johan.

I settled with this...

CREATE TABLE #Results (
     abc VARCHAR(MAX)
 )
INSERT INTO #Results
exec sp_msforeachdb '
Select ''
USE [?]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification ?]
FOR SERVER...

Thank you