Access the Audit Log In the Database

• Sep 22, 2021
• 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 the application DbContext (the MySolution.Module\BusinessObjects\MySolutionDbContext.cs file).

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

Public Class MySolutionEFCoreDbContext
    Inherits DbContext
    ' ...
    Public Property AuditData() As DbSet(Of AuditDataItemPersistent)
    Public Property AuditEFCoreWeakReferences() As DbSet(Of AuditEFCoreWeakReference)
    ' ...
End Class

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

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 descendant must contain four foreign keys that refer to the IEFCoreWeakReference descendant primary key: AuditedObject, OldObject, NewObject, and UserObject.

2. Set the AuditPersistentItemType and AuditWeakReferenceType properties to the custom types.

   ASP.NET Core Blazor
   File: MySolution.Blazor.Server\Startup.cs.

   • C#

   using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail;
   // ...
   public class Startup {
       // ...
       public void ConfigureServices(IServiceCollection services) {
           // ...
           services.AddAuditTrail(options => {
               options.AuditPersistentItemType = typeof(CustomAuditDataItemPersistent);
               options.AuditWeakReferenceType = typeof(CustomAuditEFCoreWeakReference);
           })
           // ...
       }
       // ...
   }
   

   WinForms
   File: MySolution.Win\WinApplication.cs.

   • C#
   • VB.NET

   using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail;
   // ...
   public partial class MySolutionWindowsFormsApplication : WinApplication {
       // ...
       protected override void CreateDefaultObjectSpaceProvider(CreateCustomObjectSpaceProviderEventArgs args) {
           AuditedDbContextFactory<DbContext> dbFactory = AuditedDbContextFactory.CreateFactory(typeof(MySolutionEFCoreDbContext), args.ConnectionString,
               (builder, connectionString) => {
                   // ...
               }, 
               (options) => {
                   // ...
                   options.AuditPersistentItemType = typeof(CustomAuditDataItemPersistent);
                   options.AuditWeakReferenceType = typeof(CustomAuditEFCoreWeakReference);
               }
           );
           // ...
       }
       // ...
   }
   

Use an Additional DbContext to Access Audit Data

The Audit Trail Module allows you to use an additional DbContext to store audit records. Use this technique in the following scenarios:

• to store audit records in a separate database;
• to allow users to access audit data without configuring Security System permissions.

Follow the steps below to create an additional DbContext for a separate database. If you added the Audit Trail Module when you created an XAF application, the Solution Wizard automatically generates the code demonstrated in this section:

1. Add an additional connection string for a separate database to an application configuration file. Skip this step if you want store audit records in the application database.

   Blazor
   File: MySolution.Blazor.Server\appsettings.json.

   • JSON

   {
     "ConnectionStrings": {
       "ConnectionStringToSeparateDataBase": "Integrated Security=SSPI;Pooling=false;MultipleActiveResultSets=true;Data Source=(localdb)\\mssqllocaldb;Initial Catalog=DXApplicationAdditional",
       // ...
     },
     // ...
   }
   

   WinForms
   File: MySolution.Win\App.config.

   • XML

   <?xml version="1.0"?>
     <configuration>
       <!-- ... -->
       <connectionStrings>
         <add name="ConnectionStringToSeparateDataBase" connectionString="Integrated Security=SSPI;MultipleActiveResultSets=True;Data Source=(localdb)\mssqllocaldb;Initial Catalog=DXApplicationAdditional" providerName="System.Data.SqlClient" />
         <!-- ... -->
     </connectionStrings>
   </configuration>
   

2. Create an additional DbContext descendant. In this descendant, register types that the Audit Trail Module uses to store records in a database. Comment out the same lines in the main DbContext descendant.

   The following code registers the default AuditDataItemPersistent and AuditEFCoreWeakReference types. If you use custom persistent objects to store audit data, register your custom types instead.

   File: MySolution.Module\BusinessObjects\AdditionalDbContext.cs(.vb).

   • C#
   • VB.NET

   using Microsoft.EntityFrameworkCore;
   using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail;
   //...
   public class AdditionalDbContext : DbContext {
       public AdditionalDbContext(DbContextOptions<AdditionalDbContext> options)
           : base(options) {
       }
       public DbSet<AuditDataItemPersistent> AuditData { get; set; }
       public DbSet<AuditEFCoreWeakReference> AuditEFCoreWeakReference { get; set; }
   }
   public class MySolutionEFCoreDbContext : DbContext {
       // ...
       // public DbSet<AuditDataItemPersistent> AuditData { get; set; }
       // public DbSet<AuditEFCoreWeakReference> AuditEFCoreWeakReference { get; set; }
   }
   

3. In ASP.NET Core Blazor applications, do the following in the Startup.ConfigureServices method:

   • call the AddDbContextFactory method, to add a factory with your DbContext descendant to the service collection;
   • call the AddAuditedDbContextFactory<TBusinessObjectDbContext, TAuditHistoryDbContext>() method instead of AddAuditedDbContextFactory<Tcontext>(). Pass the main DbContext as TBusinessObjectDbContext and additional AdditionalDbContext as TAuditHistoryDbContext.

   File: MySolution.Blazor.Server\Startup.cs.

   • C#

   using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail;
   using Microsoft.Extensions.DependencyInjection;
   using Microsoft.EntityFrameworkCore;
   //...
   public class Startup {
       // ...
       public void ConfigureServices(IServiceCollection services) {
           // ...
           services.AddDbContextFactory<AdditionalDbContext>((serviceProvider, options) => {
               string connectionString = Configuration.GetConnectionString("ConnectionStringToSeparateDataBase");
               options.UseSqlServer(connectionString);
               options.UseLazyLoadingProxies();
           }, ServiceLifetime.Scoped);
   
           //services.AddAuditTrail().AddAuditedDbContextFactory<MySolutionEFCoreDbContext>();
           services.AddAuditTrail().AddAuditedDbContextFactory<MySolutionEFCoreDbContext , AdditionalDbContext>();
       }
       // ...
   }
   

4. In WinForms applications, call the AuditedDbContextFactory.CreateFactory method to create the AuditedDbContextFactory<DbContext, DbContext> instance in the WinApplication.CreateDefaultObjectSpaceProvider method.

   File: MySolution.Win\WinApplication.cs.

   • C#
   • VB.NET

   using DevExpress.Persistent.BaseImpl.EFCore.AuditTrail;
   //...
   public partial class MySolutionWindowsFormsApplication : WinApplication {
       // ...
       protected override void CreateDefaultObjectSpaceProvider(CreateCustomObjectSpaceProviderEventArgs args) {
           AuditedDbContextFactory<DbContext, DbContext> dbFactory = AuditedDbContextFactory.CreateFactory(typeof(MySolutionEFCoreDbContext),
           args.ConnectionString,
           (builder, connectionString) => {
               builder.UseSqlServer(connectionString);
               // uncomment the following line in an application with the Security System
               // builder.UseSecurity((ISelectDataSecurityProvider)Security, TypesInfo);
           },
           typeof(AdditionalDbContext), 
           ConfigurationManager.ConnectionStrings.Item("ConnectionStringToSeparateDataBase").ConnectionString,
           (builder, connectionString) => {
               builder.UseSqlServer(connectionString);
           }
           // uncomment the following lines in an application with the Security System
           // , (options) => {
           //    options.AuditUserProvider = new AuditUserProvider(Security);
           // }
           );
           // ...
       }
       // ...
   }
   

5. If you want to show audit records from a separate database in the UI, register an Object Space Provider for an additional database as described in the following help topic: Populate the DefaultObjectSpaceProviders Collection.

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