How to Search Audit Data in Audit.Net?

[StarGazer202424]

Hi

I am looking if Audit.NET can help me out. It seems pretty powerful, but I am having problems on how after I get the data "logged" how to actually efficiently search through it.

My requirements are this

• I must be able to store all selects/inserts/deletes/updates (This seems to be achievable with EF Plugin & AuditCommandInterceptor)
• Be able to search a date range (easy thing to do)
• Be able to search by who did those "audits" this is easy by adding a "username" field
• Be able to search for data against all stored audits, like "I want to search everyone who did a query to try to find someone named "Jim" (this is the hard one).

Basically the last one is the one that is tripping me up, as something like first name would be stored in the JSON data which gets kind of hard and slow to do searched on in SQL Server even with the json improvements they made. I could make a computed value but then I am basically needed to do this on every single column from every single table as in that database as everything pretty much needs to be searchable (lastname, address, city, country, etc)

I found this post from a few year ago in Stack: https://stackoverflow.com/questions/68128212/how-to-achieve-generic-audit-net-json-data-processing

So it seems I might be using the wrong tool for the job and maybe this CosmosDB will help, but I am not sure if it will solve my problem still.

The example given was for changes to like "address"" what is great but like I said I need to look at "selects as well"

When I look at what is being stored in my sql database via the AuditCommandInterceptor I see something like

"EventType: "ExectureReader",
"CommandEvent": {
               "CommandText":  Select s.[Id], s.[FirstName], s.[Lastname] from People AS [s] Where s.[Id] = @___i_0
               "Paramaters":  { "@__i_0:  "jim"}
}

Now even if I am using CosmosDB how the heck do I search for and find records that had a search of "Jim" . I know I can filter down by "ExectureReader" but after that I am lost. I don't know if I have to somehow search down to "parameters" property and then somehow just look for "jim" as the value and ignore this "@__i_0: " or what.

The kicker is even if I did know the property name like in the "stack" example of they look for "Address" well my audit log going to have many different applications logs in it every time we add a new application it will be in this log as requirement is to search all our applications and these applications may have different databases.

So just imagine that there are 2 applications and when need to find jim. Well maybe in application 1 the data of "name" is being stored under a column called "First_Name" and the other one "First".

How could I do a search that still fast and find all records of "jim" I can't keep track of all the different column names.

[thepirat000]

The command interceptor output is the SQL generated internally by the Entity Framework, making it nearly impossible to parse without tightly coupling it with the EF code. Consider the issue from a different perspective: if you control the code responsible for generating the query, you could implement a correlated log within that code.

Is this an ASP.NET API?

[StarGazer202424]

The command interceptor output is the SQL generated internally by the Entity Framework, making it nearly impossible to parse without tightly coupling it with the EF code. Consider the issue from a different perspective: if you control the code responsible for generating the query, you could implement a correlated log within that code.

Is this an ASP.NET API?

I am using Blazor Server right now.

"if you control the code responsible for generating the query, you could implement a correlated log within that code."

Do you mean manually adding the audit logs myself instead of auto capturing it? I am not sure how that would 100% look like, but I was hoping to have to automatically capture so prevent people forgetting to log important information

Can you provide a quick example what you mean. Do you think I can get away doing this all in SQL Sever or does CosmoDB still make more sense?

[StarGazer202424]

I have been playing around with it not sure if this what you mean, but my plan right now is this.

1. Use a CosmoDB plus maybe even Azure AI Search (formerly known as "Azure Cognitive Search")
2. Use Audit.NET + Audit.EntityFramework

Audit.EntityFramework will handle all the inserts/updates/deletes as it seems to be in a decent standard format with having the TableName, Changes (update) and etc.

The problem becomes the selects and the only way I can think of is something like this

public void FindStudent(string firstName, string lastName, string address){
    var searchInputs = new {
        firstName = firstName,
        lastName = lastName,
        address = address
    };
    
    AuditScope.Log("Select", searchInputs);
    var foundStudent = context.Students.First(x => x.firstName == firstName, && x.lastName == lastName, && x.address == address);
}

// results in
{
    "EventType": "Select",
    "Enviroment": {
        ...
    }
    "StartDate": ,
    "EndDate": ,
    "Duration": ,
    "SearchInputs": { "firstName": "bob", "lastName": "smith", "address": "123 fake street"}
}

Problem though is you really don't have any idea what table was being searched on so it would probably be good to be able to show the raw sql query with it, but I am not sure how to this.

Then with Azure AI Search I guess I could index "SearchInputs" and then do a search for like "fake street" what should bring that back.

I could also add all the results returned by this query too by adding another property called "Results".

Sure there is a better way of using the Audit.net library to do this but at this time this is all I can come up with.

[thepirat000]

Yes, that's exactly what I was suggesting—creating an additional audit event. However, the challenge lies in correlating the manually created log with the EF log. If you were using MVC, this could be easily accomplished by leveraging the IHttpContextAccessor to attach the TraceId to all audit events via a custom action.

I will investigate further and let you know.

[StarGazer202424]

Okay good that I am on the right track. Are you saying the what would be like the ideal way would be to take whatever EF core is getting from doing it's thing and then combining it with my custom additonal audit events to basiclly get a full picture (ie see the raw SQL and etc)?

Yeah I am using blazor server right now.

[thepirat000]

Yes, it makes sense to me to log the user's action (searching for students) separately from the database query and other events.

There are several ways to correlate these events:

1. One option is to enable Activity Trace output in the audit events:

   • Audit.NET Activity Trace
   • Distributed Tracing Instrumentation Walkthroughs

   This approach will only work if you have an activity listener, as noted here: Activity Listener Notes.

With Activity Trace enabled, you'll see that events generated from the same Blazor circuit share the same Activity.TraceId in the audit event output, regardless of whether the event was triggered by Audit.EntityFramework or manually logged using AuditScope.Log().

For example:

// On your startup logic:

// Include the activity trace
Audit.Core.Configuration.IncludeActivityTrace = true;

// Make sure there is one listener
ActivitySource.AddActivityListener(new ActivityListener()
{
    ShouldListenTo = f => true,
    Sample = (ref ActivityCreationOptions<ActivityContext> _) => ActivitySamplingResult.AllData
});

2. Another option is to implement a scoped CircuitHandler to store the Circuit ID for the current scope:
   • CircuitHandler Implementation

You can then use a Custom Action in the AuditScope to retrieve the scoped Circuit ID and store it in a custom field within the audit event.

For example:

public class AuditedCircuitHandler : CircuitHandler
{
    private readonly ICircuitAccessor _circuitAccessor;

    public AuditedCircuitHandler(ICircuitAccessor circuitAccessor)
    {
        this._circuitAccessor = circuitAccessor;
    }

    public override async Task OnCircuitOpenedAsync(Circuit circuit, CancellationToken cancellationToken)
    {
        _circuitAccessor.CurrentCircuit = circuit;
        await LogAsync("Opened", circuit);
        await base.OnCircuitOpenedAsync(circuit, cancellationToken);
    }

    public override async Task OnCircuitClosedAsync(Circuit circuit, CancellationToken cancellationToken)
    {
        await LogAsync("Closed", circuit);
        await base.OnCircuitClosedAsync(circuit, cancellationToken);
    }

    private Task LogAsync(string action, Circuit circuit)
    {
    	// Log the circuit action
        return Audit.Core.AuditScope.LogAsync($"CIRCUIT_{action}", new { CircuitId = circuit.Id, Action = action });
    }
}

public interface ICircuitAccessor
{
    Circuit CurrentCircuit { get; set; }
}

public class CircuitAccessor : ICircuitAccessor
{
    public Circuit? CurrentCircuit { get; set; }
}

In your startup logic, register the circuit handler and accessor as Scoped, and set up a custom action to retrieve the scoped accessor using the DbContext GetService extension

builder.Services.AddScoped<ICircuitAccessor, CircuitAccessor>();
builder.Services.AddScoped<CircuitHandler, AuditedCircuitHandler>();

Audit.Core.Configuration.AddOnCreatedAction(scope =>
{
    var cmd = scope.GetCommandEntityFrameworkEvent();
    if (cmd != null)
    {
        var circuitAccessor = cmd.GetDbContext().GetService<ICircuitAccessor>();
        scope.SetCustomField("CircuitId", circuitAccessor.CurrentCircuit.Id);
    }
});

[StarGazer202424]

The IncludeReaderEventsPredicate configuration was added in version 27.0.1, please upgrade your references and re-test

Great, I will check it out. I am also wondering, I see the Circuit Open/Closed gets logged as well into the database. Can I omit that if needed? I am not sure if that information will be that needed in my organization.

[thepirat000]

yes, sure, just remove the LogAsync calls on the CircuitHandler code

[StarGazer202424]

yes, sure, just remove the LogAsync calls on the CircuitHandler code

Great, that is what I thought was needed to be done. I think I got enough information now to make a proof of concept and see what my other team members think. I will report back if I run into any other problems.

Thanks for your help.

[StarGazer202424]

Hey, finally been given time to implement this while this discussion was going on I was using Version 26. I have now gone to version 27 and looked at the example you posted with it, but I noticed that my "Circuit Open" and "Circuit Close" have the traceId and CircuitID but all my other requests do not and are null.

I just tried to copy everything you had, so I am not sure what I am missing.

I also what is the difference between CreateScoped and CreateLog that I see you added.

[StarGazer202424]

@thepirat000 - i think actually CreateLog is logging the TracerId and CirciutId, but like the EF and Sql Sever ones are just leaving it null.

So do I need to add something to their providers. I am not seeing what I am missing.

[thepirat000]

Given that you're using the latest version, a better approach would be to register a custom AuditScopeFactory that embeds the contextual information into the audit events.

This eliminates the need for the Custom AddOnCreatedAction. Instead, you can define your own audit scope factory and integrate it within your startup logic.

public class CustomAuditScopeFactory : AuditScopeFactory
{
    private readonly ICircuitAccessor _circuitAccessor;

    public CustomAuditScopeFactory(ICircuitAccessor circuitAccessor)
    {
        _circuitAccessor = circuitAccessor;
    }

    public override void OnScopeCreated(AuditScope auditScope)
    {
        auditScope.SetCustomField("CircuitContext", _circuitAccessor);
    }
}

// And in your start-up:
builder.Services.AddScoped<IAuditScopeFactory, CustomAuditScopeFactory>();

I'm attaching a working sample Blazor project using EF Core, featuring a custom audit circuit handler, circuit accessor, and a custom audit scope factory, all configured to log circuit context information into the audit events.

The project is built upon the default Blazor template and includes the following files:

• AuditedCircuitHandler.cs: Captures circuit/user information
• CircuitAccessor.cs: Provides scoped access to circuit information
• Program.cs: Contains the startup logic and audit configuration
• PeopleContext.cs: A sample DbContext
• CustomAuditScopeFactory.cs: A custom factory that adds circuit information to audit scopes
• Components/Pages/Home.razor: The razor page for executing a search and creating the manual log

Once compiled and run, you should be able to register a new user, confirm the registration, log in, navigate to the Search page, and perform a search, which will be logged accordingly

You should see the logs being generated in your "C:\Logs" folder:

Sample project:
BlazorApp1.zip

[StarGazer202424]

@thepirat000

Okay so I looked at your project and yea it's working for you but in my project it is not. I am not sure what I am missing.

I have this (this is hand typed all of it works and complies)

Audit.EntityFramework.Configruation.Setup()
.ForContext<DbContext>(config => config.ReloadDatabaseValues(true))
.UseOptOut();

Audit.Core.Configruation.Setup()
.UseSqlServer(config => config.connectionSTring(constring))


builder.Services.AddScoped<ICircuitAccessor, CircuitAccessor>()
builder.Services.AddScoped<CircuitHandler, AuditedCircuitHandler>();
builder.Services.AddScoped<IAuditScopeFactory, CustomAuditScopeFactory>();

public class DbContext: AuditDbContext
{
    public DbContext(DbContextOptions options, IAuditScopeFactory auditScopeFactory) : base(options)
    {
        auditScopeFactory = auditScopeFactory
    }

    protected override void OnConfiguring(DbContextOptionsBuilder builder){
        builder.AddInterceptors(new AuditIntercptor()
        {
               AuditEventType = "EF_COMMAND",
                ExcludeNonQueryEvents = true,
                ExcludeScalarEvents = true,
                IncludeReaderEventsPredicate = cmdData => cmdData.CommandSource == CommandSource.LinqQuery,
                IncludeReaderResults = true,
                LogParameterValues = true
        }).EnableSensitiveDataLogging()
    }
}

I copied all the files I see exactly as you have it in the sample file.

When I look at a "EF Command" the extra fields are all empty. It's just an empty object.

[thepirat000]

How are you instantiating the DbContext? Are you injecting it into your Razor page as in the sample project? Could you share the relevant code where the DbContext is used?

It's also worth noting that naming your context class DbContext can cause conflicts with the Entity Framework DbContext class.

Additionally, I didn’t quite get the purpose of the line auditScopeFactory = auditScopeFactory in your code.

[StarGazer202424]

Sorry, I did not call it "DbContext" I just removed the project name from it. You can think of it as "MyDbContext"

I inject a "service" that has all the calls to the database

public class MyService {
    pbulic MyService (IDbContextFactory<MyDbContext> dbContextFactory){
        this.dbContextFactory = dbContextFactory;
    }

    public myMethod(){
        using (var context = dbContextFactory.CreateDbContext()){
            context.MyTable.ToList();
        }
    }
}

builder.Services.AddDbContextFactory<MyDbContext>((DbContextOptionsBuilder options) => options.UseSqlServer(connectionSTring))

auditScopeFactory = auditScopeFactory

I think that is left over from when you were helping me out with version 26. It does not seem to be used.

Also when I do something like this

using var scope = AuditFactory.Create(c => c.EventType("SEARCH").ExtraFields(new { SearchText = SearchText ?? "" }));

That seems to work fine. It's more the EF_Command

[StarGazer202424]

Okay I think I got it to work. I moved

    builder.AddInterceptors(new AuditIntercptor()
        {
               AuditEventType = "EF_COMMAND",
                ExcludeNonQueryEvents = true,
                ExcludeScalarEvents = true,
                IncludeReaderEventsPredicate = cmdData => cmdData.CommandSource == CommandSource.LinqQuery,
                IncludeReaderResults = true,
                LogParameterValues = true
        }).EnableSensitiveDataLogging()

to the Program.cs like you have

```
builder.Services.AddDbContextFactory<PeopleContext>(opt =>
        opt.UseSqlite($"Data Source={nameof(PeopleContext)}.db")
            .AddInterceptors(new AuditCommandInterceptor()
            {
                AuditEventType = "EF_COMMAND",
                ExcludeNonQueryEvents = true,
                ExcludeScalarEvents = true,
                IncludeReaderEventsPredicate = cmdData => cmdData.CommandSource == CommandSource.LinqQuery,
                IncludeReaderResults = true,
                LogParameterValues = true
            })
            .EnableSensitiveDataLogging(), ServiceLifetime.Scoped);

Probably because of the "Scoped" I did not see how to add it in "MyDbContext" file. 

[thepirat000]

Makes sense. The issue was with the service lifetime. You can still add the interceptor in the DbContext's OnConfiguring method if that suits your preference, but ensure you register the DbContext factory as Scoped

[StarGazer202424]

@thepirat000

I have a question about the new

AuditFactory.Create(c => c.EventType("Search").ExtraFields(new { SearchText = SearchText ?? "")));

I noticed that the json it does not have a "database" property like all the other json files have. Do I need to pass that in myself always? Or am I missing a setting?

If I want to also store the "query" results just the EF_Command I am guessing I am going to have to first do the actual query and then after do the AuditFactory.Create() and pass in the results to it as well?

Finally, I see the resulted json is like this

{
   "SearchText": "hi"
}

I am guessing if I add another property that it will be at this level too.

{
   "SearchText": "hi",
   "Age" : 20
}

If I wanted to have a wrapper around this, I would need to do it myself, right?

{
      SearchParams: {
           "SearchText": "hi",
          "Age" : 20
     }
}

[thepirat000]

Events manually created using AuditFactory.Create will only include the details you explicitly specify, such as custom fields. The custom field can be any object, allowing you to define an object with nested properties:

AuditFactory.Create(c => c
    .EventType("Search")
    .ExtraFields(new
    {
        SearchParams = new
        {
            SearchText = "test",
            Age = 20
        }
    }));

[StarGazer202424]

Events manually created using AuditFactory.Create will only include the details you explicitly specify, such as custom fields. The custom field can be any object, allowing you to define an object with nested properties:

AuditFactory.Create(c => c
    .EventType("Search")
    .ExtraFields(new
    {
        SearchParams = new
        {
            SearchText = "test",
            Age = 20
        }
    }));

Yeah I just ended up writing my own. Extension method so I don't need to keep writing that wrapper, also for passing in results.

Do wish it would automatically record the database name as now got to. Either hardcode it or if using it in. A context I can get the name from the. Context and pass it in.