Can You Log Select Statements?

[chobo2]

Hi

I am wondering can this also log select statements for audit tracking?

[thepirat000]

I guess you are referring to Audit.EntityFramework.Core, if that's the case, you can use the low-level interceptor

https://github.com/thepirat000/Audit.NET/blob/master/src/Audit.EntityFramework/README.md#low-level-command-interception

[chobo2]

I guess you are referring to Audit.EntityFramework.Core, if that's the case, you can use the low-level interceptor

https://github.com/thepirat000/Audit.NET/blob/master/src/Audit.EntityFramework/README.md#low-level-command-interception

Yes, my plan is too hookup this auditor with EF and SQL database, I just need to log everything including all select statements, inserts, deletes and updates.

is there more an example on how to do it with this low level command interpreter? It does not really show enough for me to understand right now.

Also can these all be saved in the same table that is generated? Looking to have more 1 audit table for everything.

[thepirat000]

Take into account that you cannot use the EntityFramework data provider to store the low-level interceptor events, since the EF data provider is exclusively for the high-level SaveChanges interception events.

So if you want to store low-level events to a SQL table, you can use the Audit.NET.SqlServer data provider.

For example, you can configure your DbContext to include both high-level and low-level as follows:

// Inheriting from AuditDbContext will intercept SaveChanges for high-level events. 
// Inherit from DbContext if you only want low-level events.
public class MyAuditedContext : AuditDbContext
{
    public DbSet<User> Users { get; set; }

    public DbSet<AuditEntity> Audits { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            // Add the low-level interceptor
            optionsBuilder.AddInterceptors(new AuditCommandInterceptor());
            optionsBuilder.UseSqlServer(Settings.SqlConnectionString);
        }
    }
}

// Sample audit entity class to store in the database
[AuditIgnore]
public class AuditEntity
{
    [Key] public int Id { get; set; }
    public DateTime DateTime { get; set; }
    public string Event { get; set; }
}

So you can configure the SQL data provider like this:

Audit.Core.Configuration.Setup()
    .UseSqlServer(cfg => cfg
        .ConnectionString(Settings.SqlConnectionString)
        .TableName("Audits")
        .IdColumnName("Id")
        .CustomColumn("DateTime", ev => DateTime.Now)
        .JsonColumnName("Event"));

[chobo2]

Settings

Okay I will try that but where is Settings coming from?

Also where do you put the last chunk of code .Setup()?

My project is split into many different projects where the ApplicationDBContext is on 1 project and the Entities and the setup of the context is in a seperate project

looks something like this

public static IServiceCollection Add(this IServiceCollection, services, IConfigruation config)
{
   services.AddDbContext<MyDbContext>(options => options.UseSqlServer(configuration.GetConnectionString("string"));

}

Also your making a AuditTable that will be used to store all the aduits right? Is this including the high level ones? Would the json data just go into the Events?

[thepirat000]

Setting.SqlConnectionString was just an example to connect with a connection string.

Yes, the table will include all the events.

Nothing stops you to try and see by yourself.

Audit.Core.Configuration.Setup() should be called on your app startup.

[chobo2]

I
o

Setting.SqlConnectionString was just an example to connect with a connection string.

Yes, the table will include all the events.

Nothing stops you to try and see by yourself.

Audit.Core.Configuration.Setup() should be called on your app startup.

Okay I think I got it to work in my project. It seems to be logging though I am not sure if it doing the delete (or maybe how my code is setup maybe it does not work).

Some tables that are for drafts I clear the entire record and then reinsert the new record, I only see the insert not the clearing of the old record but maybe I missed it.

Also I need to attach to all high and low level events a userId, I cannot set that in the startup at a customColumn, am I able to call something per request and add this information in?

[chobo2]

I think the delete might be fine

still trying to see how I could dynamically add the userId to each CRUD request

[thepirat000]

it depends, how do you get the current userId?

[chobo2]

it depends, how do you get the current userId?

Right now it would be through a claim via the http context on each request.

[thepirat000]

If you use asp net core, then you can access the current httpcontext by injecting an IHttpContextAccesor, for example, on your StartUp class:

public void Configure(IApplicationBuilder app, IHttpContextAccessor contextAccessor)
{
    Audit.Core.Configuration.Setup()
        .UseSqlServer(cfg => cfg
            .ConnectionString("...")
            .TableName("Audits")
            .IdColumnName("Id")
            .CustomColumn("User", _ => contextAccessor.HttpContext.User.Identity.Name)
            .JsonColumnName("Event"));

   // ...
}

[chobo2]

If you use asp net core, then you can access the current httpcontext by injecting an IHttpContextAccesor, for example, on your StartUp class:

public void Configure(IApplicationBuilder app, IHttpContextAccessor contextAccessor)
{
    Audit.Core.Configuration.Setup()
        .UseSqlServer(cfg => cfg
            .ConnectionString("...")
            .TableName("Audits")
            .IdColumnName("Id")
            .CustomColumn("User", _ => contextAccessor.HttpContext.User.Identity.Name)
            .JsonColumnName("Event"));

   // ...
}

Okay, I am using .net core but I think there is a bug in my project as we are using the http context in some other places but the claims are empty.....so I have to figure that out and then try again.

I do have some other questions as I re implemented everything to better understand it.

1. Which packages are these from?

Audit.Core.Configuration.Setup()
    .UseSqlServer(cfg => cfg                              // this  is from the [Audit.NET.SqlServer ???
        .ConnectionString(Settings.SqlConnectionString)
        .TableName("Audits")
        .IdColumnName("Id")
        .CustomColumn("DateTime", ev => DateTime.Now)
        .JsonColumnName("Event"));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            // Add the low-level interceptor
            optionsBuilder.AddInterceptors(new AuditCommandInterceptor());
            optionsBuilder.UseSqlServer(Settings.SqlConnectionString);  // this is from the EF Core Provider package?
        }

Even though all the low level and high level commands are being logged into the database I am also still see them being logged to the JSON files as well.

What could be causing this?

[chobo2]

Also with the low level logging can we also log the actual results that come back and not just the query that was done?

[thepirat000]

1. Yes, the extension method is defined on Audit.Core namespace, in Audit.NET.SqlServer package.

2. Maybe you have two apps running at the same time and one of them is not configured?

3. The actual results for query commands (SELECT FROM...) are not included in the audit output, mainly because the DbDataReader intercepted is forward-only, so once it's read, the EF will not see any results.

I think the only option is to buffer the results in memory, but that will require some work and of course, it will impact performance so this should be an optional mechanism. If you want, you can create a new issue here and ask for support for including query results.

[chobo2]

1. Yes, the extension method is defined on Audit.Core namespace, in Audit.NET.SqlServer package.
2. Maybe you have two apps running at the same time and one of them is not configured?
3. The actual results for query commands (SELECT FROM...) are not included in the audit output, mainly because the DbDataReader intercepted is forward-only, so once it's read, the EF will not see any results.

I think the only option is to buffer the results in memory, but that will require some work and of course, it will impact performance so this should be an optional mechanism. If you want, you can create a new issue here and ask for support for including query results.

2. Well I have implemented it only on 1 project but in this solution it has like 5 projects in it but most are more libraries then executables. What is the line that actually tells it to store it in the database is it

Audit.Core.Configuration.Setup()
    .UseSqlServer(cfg => cfg
        .ConnectionString(Settings.SqlConnectionString)
        .TableName("Audits")
        .IdColumnName("Id")
        .CustomColumn("DateTime", ev => DateTime.Now)
        .JsonColumnName("Event"));

[chobo2]

@thepirat000

Hey with AuditIgnore for the EF provider does this work on low level commands as well (ie if I put AuditIgnore on a entity and do a select statement on it will it still log in the db?)?

[thepirat000]

yes that should be the problem.
Note you can disable audit with
Audit.Core.Cofiguration.AuditDisabled = true; and reenable iby setting it to false.

[chobo2]

Great that is exactly what I needed, I disable it at the start of the program.cs and then enable it back at the end.

I noticed with the low level logging though the select statements it logs are not runnable. First it stores the \r\n (no big deal, can parse that out easily) but I noticed if there is a where clause part that needs a dynamic parameter (like where userid =1). It does not log the parameter value of in my example 1. Just has the parameter place holder name.

Is this expected behavior?

[thepirat000]

I'm not sure, can you share a code example of how are you querying the database?

[thepirat000]

Never mind, I was able to reproduce. I'll take a look

[thepirat000]

You need to enable the parameter logging on the DbContext, or by setting LogParameterValues to true on the AuditCommandInterceptor.

optionsBuilder.AddInterceptors(new AuditCommandInterceptor()
{
    // Boolean value to indicate whether to log the command parameter values. 
    // By default (when null) it will depend on EnableSensitiveDataLogging setting on the DbContext.
    LogParameterValues = true
});

Or:

optionsBuilder.EnableSensitiveDataLogging();

[chobo2]

@thepirat000

I am not 100% sure yet as I am still trying to do some testing, but I am getting some weird results where I am getting an error saying a Data Reader is open in the same context and it is throwing an error.

When I turn off the new flag that was released in the latest version to log the db results then I don't get this error anymore.

It is happening only in specific situation where I seem to be inserting data into multiple tables.

[thepirat000]

can you share a sample code of the operations that are causing the issue?

[chobo2]

I have created a bug request. I tried to add a bit of sample code and the error. but it is a bit hard to know for sure the cause.

[thepirat000]

oh, I see. I will take a look.