Access the Audit Log In the Database

• Jul 09, 2025
• 7 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 can store the change history in the application database or separate database. This Module uses the following classes to access the information from this database:

DevExpress.Persistent.BaseImpl.EFCore.AuditTrail.AuditEFCoreWeakReferenceInformation on modified objects. Objects and their identifiers are stored as strings.DevExpress.Persistent.BaseImpl.EFCore.AuditTrail.AuditDataItemPersistentInformation on changes.

To use these classes, ensure that they are registered in your application’s auditing DbContext:

File: MySolution.Module\BusinessObjects\MySolutionDbContext.cs

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

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

Note

The names of these database tables depend on the names of the corresponding DbSet properties in the auditing 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 custom classes that either extend the AuditDataItemPersistent and AuditEFCoreWeakReference classes, or implement the IAuditDataItemPersistent and IEFCoreWeakReference interfaces.

2. In ASP.NET Core Blazor and WinForms applications, modify the WithAuditedDbContext method call to assign your custom types to AuditTrailOptions.AuditPersistentItemType and AuditTrailOptions.AuditWeakReferenceType properties.

Store Audit Data in a Separate Database

The Audit Trail Module allows you to configure the application’s auditing DbContext to use a separate database connection. Use this technique when you need to store audit records in a separate database.

1. Add an additional connection string for a separate database to the application configuration files.

2. Ensure that your application’s main module has an additional auditing DbContext that contains code that registers the default AuditDataItemPersistent and AuditEFCoreWeakReference types. If you use custom persistent objects to store audit data, register your custom types instead.

3. In ASP.NET Core Blazor and WinForms applications, modify the WithAuditedDbContext method call to use separate connection strings for the application’s main and auditing DbContexts:

4. If you want to display audit records from a separate database in the UI, register an additional object space provider for the auditing DbContext:

5. In the Updater class, check whether the current object space can create an ApplicationUser object (or any other persistent object used by the application):

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 Microsoft 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).