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

  • maroju1

    Old Hand

    Points: 308

    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

     

     

  • Sue_H

    SSC Guru

    Points: 90673

    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: 308

    Thanks a lot.

    Attachments:
    You must be logged in to view attached files.
  • John Mitchell-245523

    SSC Guru

    Points: 148761

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

    John

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

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