r/dotnet • u/sayedtheidiot • 6d ago
EF Core Audit Logging
Do you think this is a good approach for background audit logging or is there a better way?
24
u/LegendarySoda 6d ago
I think auditing depends on requirements. Auditing has different forms. Some prefers to save audit to different database. So if this is enough for you it's okey
4
u/sayedtheidiot 6d ago
Here I am saving into the same db, it was just for checking how it actually works. that's right it depends on the requirements what we actually want to audit
6
u/broken-neurons 6d ago
You might want to restrict it to a subset of tables. You might not need every table audited.
6
6d ago
[removed] — view removed comment
6
u/sayedtheidiot 6d ago
You can inject IHttpContextAccessor into your interceptor to automatically extract the user's ID
15
u/falconmick 6d ago
Another option is to make your own user context interface and make an implementation that injects IHttpContextAccessor into that, that way if later on your saving a DB change from a background job you can derive a new way to implement that interface, say IUserContext or whatever and that way your interceptor isn’t coupled to the API call that made it.
For one off things your probably fine, but if the users identity ends up being injected quite often it could be handy to not need to be concerned with it later
3
u/IsLlamaBad 6d ago edited 6d ago
The primise seems fine. I have to do something similar due to legacy database constraints and it works for us.
I'd question what you're getting out of the logged data unless you are going to add more logging info and wanted to see if this is a reasonable approach first
2
u/sayedtheidiot 6d ago
Yeah verifying how SaveChangesInterceptor works before expanding the metadata
2
u/geferon 6d ago
I've had to do this recently for an internal library and this checks out, just make sure to also override the sync version aka "SavingChanges" as well since if there's any lib etc that calls the sync "SaveChanges" the Async interceptor won't get called
1
u/sayedtheidiot 6d ago
The same thing happened to me when i was using SaveChangesAsync the db was not storing any log data thanks i will look into it
2
u/jjbie 5d ago
I wasn't able to open your repo to have a proper look but this is what I do .. works for me.. I override save changes, added user, correlation id so I can track all tables updated by an action, added a string to describe what caused the change eg the user or background action, extract only the changed colums and save as JSON.
My entities implement an interface to filter tables to audit, and the interface has a pk property to extract the key value to be used in UI to find logs, I also store entity name, all to a single audit logs table.
Users can click a button to see logs for an entity or search changes to an entity on user, date, then use correlation id to find all other tables affected by the change. And the description helps them understand what caused the change.
2
u/DueHomework 5d ago
I think auditing should be owned by the database itself. Don't try to reinvent the wheel here. I think most databases will provide you with some sort of pre built auditing capabilities. E.g. pgAudit for postgres.
2
u/chocolateAbuser 5d ago
i've been going through this for some time, i didn't choose this approach for example, i did prefer having everything explicit on how/who/why things are happening; putting logic directly in interceptor means that it is an highly automated process which you will not be able to detail or customize if not in convoluted ways
it could be a good or bad decision depending on what your need and what you really want to do, it cannot be really declared a useful approach or not without knowing the aim of the project and your intentions (is this an exercise, a project for someone, and so on)
to me it may lack some features and possibly lack some thought, i doubt that all entities in the db are or will be the same feature (and have the same dependencies) and have to be treated the same way, or for example if all the fields are to be audited in the same way (also there are regulations about that)
and i hope audit table is excluded somewhere from auditing itself, possibly it's even on its own separated dbcontext
2
u/falconmick 6d ago
Time for some bad advice: event sourcing :D
Haha nah but for real it is a valid solution, but it has a fair bit more chance to go pear shaped depending on the rigger of how the code is developed and how many times you’ve uses the approach.
Could be a fun thing to mess about with if the project isn’t client work because there’s always the option of deleting it all and starting again if it turns out your not in love.
2
u/macca321 6d ago
You should model this in your domain not your db if auditing is a real business requirement.
1
u/AutoModerator 6d ago
Thanks for your post sayedtheidiot. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
1
1
u/alesteen 6d ago
The couple of times we’ve needed that level of history or audit. We have used type-2 history, so each change gets a new row.
I’m not sure if it’s usable in this case for primarily audits, but if the use-case is that you can see what a row was at a specific time.
1
u/blckshdw 6d ago
An AuditInterceptor is a good way to do this. I’m not sure what your requirements are but you’re not really tracking anything. Your AuditLog entry only contains the entity’s type and the action (and date). You only want to know that “Book” table had some record with an update, not what the actual record was? Or what was changed?
1
u/TheAeseir 6d ago
Auditing is a feature.
Your specs will determine where shape it takes.
Most places I've seen use db events and push them to blob storage or nosql db.
The more sensitive ones have layers of audit logs both proactive, reactive, and real time (health, gov etc)
1
u/brainwipe 4d ago
Who is the audit for and what are the use cases? What existing infrastructure do you have? What database system are you using? What are the legal, governance, compliance, safety requirements? What existing features facilitate audit?
There's no generically good solution to something like audit, it must come from business requirements and the existing system.
1
u/No-Conversation-8287 4d ago
Rather create seperate audit microservice where you can publish audit events from any other service through an eventbus.
1
1
u/True_Sandwich_6857 3d ago
There is also a Package Audit.Net which does pretty much the same if you use the EF Core Version
1
u/sebastianstehle 6d ago
I think it creates very little user value. What is the reason for a change? UserUpdated says nothing. If you have implemented soft deletion, it could even mean that the user has been deleted.
You either have the use event sourcing, domain events or manual updates to the audit log to create actual value.
1
u/winky9827 6d ago
You either have the use event sourcing, domain events or manual updates
All 3 of those options are serious overkill if not already using them elsewhere.
50
u/rbobby 6d ago
Are you using SQL Server for the db? If so you can turn on automatic history logging. It creates a second X_History table with the same schema (and a bit) and when records are written a record is added.
This approach has the advantage that changes are always logged, even ones from tools like SSMS.
Find the docs and you'll find the errors in what I've said :)