Access the Audit Log In the Database
- 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.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 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:
Implement custom classes that either extend the
AuditDataItemPersistent
andAuditEFCoreWeakReference
classes, or implement theIAuditDataItemPersistent
andIEFCoreWeakReference
interfaces.Note that the class that implements
IAuditDataItemPersistent
must contain four foreign keys that refer to the primary key of the class that implementsIEFCoreWeakReference
:AuditedObject
OldObject
NewObject
UserObject
In ASP.NET Core Blazor and WinForms applications, modify the
WithAuditedDbContext
method call to assign your custom types toAuditTrailOptions.AuditPersistentItemType
andAuditTrailOptions.AuditWeakReferenceType
properties.File: MySolution.Blazor.Server/Startup.cs., MySolution.Win/Startup.cs
public void ConfigureServices(IServiceCollection services) {-+ // ... services.AddXaf(Configuration, builder => { // ... builder.ObjectSpaceProviders .AddSecuredEFCore().WithAuditedDbContext(contexts => { contexts.Configure<MySolutionEFCoreDbContext, MySolutionAuditingDbContext>( (serviceProvider, businessObjectDbContextOptions) => { /* ... */}, (serviceProvider, auditHistoryDbContextOptions) => { /* ... */ }, (options) => { options.AuditPersistentItemType = typeof(CustomAuditDataItemPersistent); options.AuditWeakReferenceType = typeof(CustomAuditEFCoreWeakReference); } ); // ... }) // ... } // ... }
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.
Add an additional connection string for a separate database to the application configuration files.
File: MySolution.Blazor.Server\appsettings.json, MySolution.Win\App.config
{ "ConnectionStrings": { "AuditConnectionString": "Integrated Security=SSPI;Pooling=false;MultipleActiveResultSets=true;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=DXApplicationAudit", // ... }, // ... }
Ensure that your application’s main module has an additional auditing DbContext that contains code that registers the default
AuditDataItemPersistent
andAuditEFCoreWeakReference
types. If you use custom persistent objects to store audit data, register your custom types instead.If the application’s main DbContext contains code that registers the same types (the default setting), remove or comment out this code.
File: MySolution.Module\BusinessObjects\AdditionalDbContext.cs`
using Microsoft.EntityFrameworkCore; using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail; // Additional auditing DbContext public class MySolutionAuditingDbContext : DbContext { public MySolutionAuditingDbContext(DbContextOptions<MySolutionAuditingDbContext> options) : base(options) { } public DbSet<AuditDataItemPersistent> AuditData { get; set; } public DbSet<AuditEFCoreWeakReference> AuditEFCoreWeakReference { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.HasChangeTrackingStrategy(ChangeTrackingStrategy.ChangingAndChangedNotificationsWithOriginalValues); modelBuilder.Entity<AuditEFCoreWeakReference>() .HasMany(p => p.AuditItems) .WithOne(p => p.AuditedObject); modelBuilder.Entity<AuditEFCoreWeakReference>() .HasMany(p => p.OldItems) .WithOne(p => p.OldObject); modelBuilder.Entity<AuditEFCoreWeakReference>() .HasMany(p => p.NewItems) .WithOne(p => p.NewObject); modelBuilder.Entity<AuditEFCoreWeakReference>() .HasMany(p => p.UserItems) .WithOne(p => p.UserObject); } } public class MySolutionEFCoreDbContext : DbContext { // ... // public DbSet<AuditDataItemPersistent> AuditData { get; set; } // public DbSet<AuditEFCoreWeakReference> AuditEFCoreWeakReference { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { // ... //modelBuilder.Entity<AuditEFCoreWeakReference>() // .HasMany(p => p.AuditItems) // .WithOne(p => p.AuditedObject); //modelBuilder.Entity<AuditEFCoreWeakReference>() // .HasMany(p => p.OldItems) // .WithOne(p => p.OldObject); //modelBuilder.Entity<AuditEFCoreWeakReference>() // .HasMany(p => p.NewItems) // .WithOne(p => p.NewObject); //modelBuilder.Entity<AuditEFCoreWeakReference>() // .HasMany(p => p.UserItems) // .WithOne(p => p.UserObject); // ... }
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:File: MySolution.Blazor.Server/Startup.cs., MySolution.Win/Startup.cs
public void ConfigureServices(IServiceCollection services) {-+ // ... services.AddXaf(Configuration, builder => { // ... builder.ObjectSpaceProviders .AddSecuredEFCore().WithAuditedDbContext(contexts => { contexts.Configure<MySolutionEFCoreDbContext, MySolutionAuditingDbContext>( (serviceProvider, businessObjectDbContextOptions) => { string connectionString = null; if (Configuration.GetConnectionString("ConnectionString") != null) { connectionString = Configuration.GetConnectionString("ConnectionString"); } ArgumentNullException.ThrowIfNull(connectionString); businessObjectDbContextOptions.UseSqlServer(connectionString); // ... }, (serviceProvider, auditHistoryDbContextOptions) => { string connectionString = null; if (Configuration.GetConnectionString("AuditConnectionString") != null) { connectionString = Configuration.GetConnectionString("AuditConnectionString"); } ArgumentNullException.ThrowIfNull(connectionString); auditHistoryDbContextOptions.UseSqlServer(connectionString); // ... } ); // ... }) // ... }) // ... }
If you want to display audit records from a separate database in the UI, register an additional object space provider for the auditing DbContext:
File: MySolution.Blazor.Server/Startup.cs., MySolution.Win/Startup.cs
public void ConfigureServices(IServiceCollection services) {-+ // ... services.AddXaf(Configuration, builder => { // ... builder.ObjectSpaceProviders .AddSecuredEFCore().WithAuditedDbContext(contexts => { contexts.Configure<MySolutionEFCoreDbContext, MySolutionAuditingDbContext>( (serviceProvider, businessObjectDbContextOptions) => { /* ... */ }, (serviceProvider, auditHistoryDbContextOptions) => { /* ... */ }); .AddSecuredEFCore() .WithDbContext<MySolutionAuditingDbContext>((serviceProvider, options) => { string connectionString = null; if (Configuration.GetConnectionString("AuditConnectionString") != null) { connectionString = Configuration.GetConnectionString("AuditConnectionString"); } ArgumentNullException.ThrowIfNull(connectionString); options.UseSqlServer(connectionString); options.UseChangeTrackingProxies(); options.UseObjectSpaceLinkProxies(); options.UseLazyLoadingProxies(); }) // ... }) // ... }}
In the
Updater
class, check whether the current object space can create anApplicationUser
object (or any other persistent object used by the application):// ... namespace YourApplicationName.Module.DatabaseUpdate; public class Updater : ModuleUpdater { public override void UpdateDatabaseAfterUpdateSchema() { base.UpdateDatabaseAfterUpdateSchema(); if (!ObjectSpace.CanInstantiate(typeof(ApplicationUser))) { return; } // ... } }
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:
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):
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:
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).