How can we get updated and old values from sys.fn_get_audit_file table

Question

How can we get updated and old values from sys.fn_get_audit_file table

maroju1

Old Hand

Points: 310
More actions
August 28, 2019 at 8:25 am

#3674981

Dear Team,
This is Naveen, i want to get the list of update new value and old values from audit table (sys.fn_get_audit_file table).
Similarly like Insert and Delete value list from mentioned same table.
Kindly help me on this.
Output:
Event_Time,Server_instance_name,database_Name,UpdateValues, Old Value, Client_Ip Statement
1 Instance01 Test (@Sno=10) (@sno=2) 10.00.00 Update
2 Instance01 Test2 (@Sno=59,@sname='Nav') @Sno=9,@sname='Test') 11.00.00 Update
3 Instance01 Test (@Sno=15) blank 12.00.00 Insert
4 Instance01 Test Blank (@sno=10) 10.00.00 Delete

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

That information isn't available when using a database audit. This article has a decent overview of different auditing methods:

Various techniques to audit SQL Server databases

It sounds more like you may want to create audit tables and populate those with triggers but I don't know enough about your auditing to say if that's a good direction or not. Keep in mind, sometimes you need to combine different methods to get what you need.

Sue

maroju1 Old Hand Points: 310 More actions · Answer 2

Thanks a lot.

Attachments:

You must be logged in to view attached files.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Depending on your precise requirement, you might also consider Change Data Capture and Temporal Tables.

John