Access the Audit Log In the Database

• Jul 07, 2021
• 3 minutes to read

You can use a database management system (DBMS) that supports SQL query execution to access the audit log in the application database. If you use Microsoft SQL Server, we recommend that you use Microsoft SQL Server Management Studio or sqlcmd utility to execute SQL queries. This topic describes the database tables that the Audit Trail Module uses and how to query their records.

Database Tables and Corresponding Classes

The Audit Trail Module stores change history in the application database. This Module uses the following classes to access the information in the database.

DevExpress.Persistent.BaseImpl.EFCore.AuditTrail.AuditEFCoreWeakReference

Information on modified objects. Objects and their identifiers are stored as strings.

DevExpress.Persistent.BaseImpl.EFCore.AuditTrail.AuditDataItemPersistent

Information on changes.

To use these classes, ensure that they are registered in the application DbContext (the MySolution.Module\BusinessObjects\MySolutionDbContext.cs file).

C#

public class MySolutionEFCoreDbContext : DbContext {
    // ...
    public DbSet<AuditDataItemPersistent> AuditData { get; set; }
    public DbSet<AuditEFCoreWeakReference> AuditEFCoreWeakReferences { get; set; }
    // ...
}

The Module adds new records to the AuditData and AuditEFCoreWeakReference database tables when a user changes an audited object. The following diagram demonstrates relationships between these tables.

Note

The names of these database tables depend on the names of the corresponding DbSet properties in DbContext and may differ from the default names described above.

Implement Custom Persistent Object to Store Audit Data

If you want to store additional audit information, do the following:

1. Implement the AuditDataItemPersistent and AuditEFCoreWeakReference descendants, or implement the IAuditDataItemPersistent and IEFCoreWeakReference interfaces in your custom classes. Note that the IAuditDataItemPersistent inheritor must contain four foreign keys to the IEFCoreWeakReference inheritor: AuditedObject, OldObject, NewObject, and UserObject.

2. In the Startup.ConfigureServices method (the MySolution.Blazor.Server\Startup.cs file), set the AuditPersistentItemsType and AuditWeakReferenceType properties to the custom types.

   C#

   services.AddAuditTrail(options => {
       options.AuditPersistentItemsType = typeof(CustomAuditDataItemPersistent);
       options.AuditWeakReferenceType = typeof(CustomAuditEFCoreWeakReference);
   })
   

Access the Audit Log

You can write SQL queries to access data from the audit log stored in the database. The following code demonstrates a sample query:

SQL

SELECT OperationType, ModifiedOn, uwr.DefaultString as UserName, PropertyName, OldValue, NewValue, owr.DefaultString as OldObject, nwr.DefaultString as NewObject
FROM AuditData ad
LEFT JOIN AuditEFCoreWeakReference awr ON ad.AuditedObjectID = awr.ID
LEFT JOIN AuditEFCoreWeakReference owr ON ad.OldObjectID = owr.ID
LEFT JOIN AuditEFCoreWeakReference nwr ON ad.NewObjectID = nwr.ID
LEFT JOIN AuditEFCoreWeakReference uwr ON ad.UserObjectID = uwr.ID

For example, to filter changes of a particular object, use the following SQL statement (for MS SQL):

SQL

SELECT OperationType, ModifiedOn, uwr.DefaultString as UserName, PropertyName, OldValue, NewValue, owr.DefaultString as OldObject, nwr.DefaultString as NewObject
FROM AuditData ad
LEFT JOIN AuditEFCoreWeakReference awr ON ad.AuditedObjectID = awr.ID
LEFT JOIN AuditEFCoreWeakReference owr ON ad.OldObjectID = owr.ID
LEFT JOIN AuditEFCoreWeakReference nwr ON ad.NewObjectID = nwr.ID
LEFT JOIN AuditEFCoreWeakReference uwr ON ad.UserObjectID = uwr.ID
WHERE awr.DefaultString = 'Office'
ORDER BY  ModifiedOn

Remove the Audit Log Part

The following SQL statements illustrate how to delete all audit log entries made before March 12, 2021:

SQL

DELETE FROM AuditData WHERE ModifiedOn < '2021-03-12';
DELETE FROM AuditEFCoreWeakReference WHERE LastModifiedDate < '2021-03-12'

You can also implement an Action that executes SQL statements (use standard ADO.NET techniques).