How to achieve generic Audit.NET json data processing?

I am using Audit.Net library to log EntityFramework actions into a database (currently everything into one AuditEventLogs table, where the JsonData column stores the data in the following Json format:

{
    "EventType":"MyDbContext:test_database",
    "StartDate":"2021-06-24T12:11:59.4578873Z",
    "EndDate":"2021-06-24T12:11:59.4862278Z",
    "Duration":28,
    "EntityFrameworkEvent":{
        "Database":"test_database",
        "Entries":[
            {
                "Table":"Offices",
                "Name":"Office",
                "Action":"Update",
                "PrimaryKey":{
                    "Id":"40b5egc7-46ca-429b-86cb-3b0781d360c8"
                },
                "Changes":[
                    {
                        "ColumnName":"Address",
                        "OriginalValue":"test_address",
                        "NewValue":"test_address"
                    },
                    {
                        "ColumnName":"Contact",
                        "OriginalValue":"test_contact",
                        "NewValue":"test_contact"
                    },
                    {
                        "ColumnName":"Email",
                        "OriginalValue":"test_email",
                        "NewValue":"test_email2"
                    },
                    {
                        "ColumnName":"Name",
                        "OriginalValue":"test_name",
                        "NewValue":"test_name"
                    },
                    {
                        "ColumnName":"OfficeSector",
                        "OriginalValue":1,
                        "NewValue":1
                    },
                    {
                        "ColumnName":"PhoneNumber",
                        "OriginalValue":"test_phoneNumber",
                        "NewValue":"test_phoneNumber"
                    }
                ],
                "ColumnValues":{
                    "Id":"40b5egc7-46ca-429b-86cb-3b0781d360c8",
                    "Address":"test_address",
                    "Contact":"test_contact",
                    "Email":"test_email2",
                    "Name":"test_name",
                    "OfficeSector":1,
                    "PhoneNumber":"test_phoneNumber"
                },
                "Valid":true
            }
        ],
        "Result":1,
        "Success":true
    }
}

Me and my team has a main aspect to achieve: Being able to create a search page where administrators are able to tell

who changed
what did they change
when did the change happen

They can give a time period, to reduce the number of audit records, and the interesting part comes here: There should be an input text field which should let them search in the values of the “ColumnValues” section.

The problems I encountered:

Even if I map the Json structure into relational rows, I am unable to search in every column, with keeping the genericity.
If I don’t map, I could search in the Json string with LIKE mssql function but on the order of a few 100,000 records it takes an eternity for the query to finish so it is probably not the way.

Keeping the genericity would be important, so we don’t need to modify the audit search page every time when we create or modify a new entity.

I only know MSSQL, but is it possible that storing the audit logs in a document oriented database like cosmosDB (or anything else, it was just an example) would solve my problem? Or can I reach the desired behaviour using relational database like MSSQL?

Answer

Looks like you’re asking for an opinion, in that case I would strongly recommend a document oriented DB.

CosmosDB could be a great option since it supports SQL queries.

There is an extension to log to CosmosDB from Audit.NET: Audit.AzureCosmos

A sample query:

SELECT c.EventType, e.Table, e.Action, ch.ColumnName, ch.OriginalValue, ch.NewValue
FROM c 
JOIN e IN c.EntityFrameworkEvent.Entries
JOIN ch IN e.Changes
WHERE ch.ColumnName = "Address" AND ch.OriginalValue = "test_address"

Here is a nice post with lot of examples of complex SQL queries on CosmosDB

Advertisement

Answer