All docs
V21.2
21.2
21.1
20.2
The page you are viewing does not exist in version 20.2. This link will take you to the root page.
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.
18.1
The page you are viewing does not exist in version 18.1. This link will take you to the root page.
17.2
The page you are viewing does not exist in version 17.2. This link will take you to the root page.

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 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; }
    // ...
}

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:

Database diagram

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

    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.

    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.

Note

You should create a separate database for audit records because XAF does not generate it automatically.

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.

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

    WinForms
    File: MySolution.Win\App.config.

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

    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.

    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.

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