AutoQuery Audit Log using existing table?

ServiceStack Auto Query
1

I would like to use the Crud Audit Events, but it is not appropriate for the application to create a table, because of DB rights. So, the following would fail:

container.Resolve<ICrudEvents>().InitSchema();

If I have a 100% equivalent table defined in one of my database schemas, is there a way to specify it as the data store for auditing crud events?

2

Yeah all InitSchema() APIs are effectively creating RDBMS schemas if they don’t exist, this is for CrudEvents:

I’d recommend capturing the Create Table SQL generated by OrmLite for the CrudEvent table it’s using, e.g:

OrmLiteUtils.PrintSql();
db.CreateTableIfNotExists<CrudEvent>();

So you can run the exact SQL Schema that OrmLite expects.

It expects to use the either the primary registered OrmLite connection or one of the registered named connections, i.e. in the same RDBMS as the Auto CRUD tables are stored. If you want a different behavior you’d need to implement a custom CrudEventsBase implementation.

3

Thank you for the fast response, following your advice, I captured the SQL for creating the table:

[12:42:46 DBG] SQL: SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'CrudEvent' AND TABLE_SCHEMA <> 'Security'
[12:42:46 DBG] SQL: CREATE TABLE "CrudEvent" 
(
  "Id" BIGINT PRIMARY KEY IDENTITY(1,1), 
  "EventType" VARCHAR(8000) NULL, 
  "Model" VARCHAR(8000) NULL, 
  "ModelId" VARCHAR(8000) NULL, 
  "EventDate" DATETIME NOT NULL, 
  "RowsUpdated" BIGINT NULL, 
  "RequestType" VARCHAR(8000) NULL, 
  "RequestBody" VARCHAR(8000) NULL, 
  "UserAuthId" VARCHAR(8000) NULL, 
  "UserAuthName" VARCHAR(8000) NULL, 
  "RemoteIp" VARCHAR(8000) NULL, 
  "Urn" VARCHAR(8000) NULL, 
  "RefId" INTEGER NULL, 
  "RefIdStr" VARCHAR(8000) NULL, 
  "Meta" VARCHAR(MAX) NULL 
);

I manually created the table above as Security.CrudEvent. All my data is Unicode based, so I took the liberty of using NVARCHAR instead of VARCHAR, with the corresponding length adjustments.

After calling a POST service to create an entity, I receive the error:

{
  "result": null,
  "responseStatus": {
    "errorCode": "SqlException",
    "message": "Invalid object name 'CrudEvent'.",
    "stackTrace": "[CreateBrand: 2020-12-14 15:28:11]:\n[REQUEST: {name:Gold,commodityId:34aa96581d3eeb11b514482ae316d296,brandOriginId:87ab96581d3eeb11b514482ae316d296,brandStatusTypeId:64ab96581d3eeb11b514482ae316d296,statusDate:\"2016-04-24T20:18:18Z\"}]\r\nSystem.Data.SqlClient.SqlException (0x80131904): Invalid object name 'CrudEvent'.\r\n   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)\r\n   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n   at ServiceStack.OrmLite.OrmLiteCommand.ExecuteNonQuery() in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteCommand.cs:line 43\r\n   at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ExecNonQuery(IDbCommand dbCmd) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteResultsFilterExtensions.cs:line 64\r\n   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.InsertInternal[T](IOrmLiteDialectProvider dialectProvider, IDbCommand dbCmd, Object obj, Action`1 commandFilter, Boolean selectIdentity) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteWriteCommandExtensions.cs:line 804\r\n   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Action`1 commandFilter, Boolean selectIdentity) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteWriteCommandExtensions.cs:line 734\r\n   at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass5_0`1.<Insert>b__0(IDbCommand dbCmd) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteWriteApi.cs:line 72\r\n   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteExecFilter.cs:line 64\r\n   at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T](IDbConnection dbConn, Func`2 filter) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteReadExpressionsApi.cs:line 16\r\n   at ServiceStack.OrmLite.OrmLiteWriteApi.Insert[T](IDbConnection dbConn, T obj, Boolean selectIdentity) in C:\\BuildAgent\\work\\27e4cc16641be8c0\\src\\ServiceStack.OrmLite\\OrmLiteWriteApi.cs:line 72\r\n   at ServiceStack.OrmLiteCrudEvents`1.Record(CrudContext context) in C:\\BuildAgent\\work\\3481147c480f4a2f\\src\\ServiceStack.Server\\CrudEvents.cs:line 213\r\n   at ServiceStack.AutoQuery.ExecAndReturnResponse[Table](CrudContext context, Func`2 fn) in C:\\BuildAgent\\work\\3481147c480f4a2f\\src\\ServiceStack.Server\\AutoQueryFeature.AutoCrud.cs:line 779\r\n   at ServiceStack.AutoQuery.Create[Table](ICreateDb`1 dto, IRequest req) in C:\\BuildAgent\\work\\3481147c480f4a2f\\src\\ServiceStack.Server\\AutoQueryFeature.AutoCrud.cs:line 401\r\n   at QQL.ServiceInterface.BrandNs.BrandService.Post(CreateBrand request) in C:\\Yoyo\\QqcDesk\\QQL\\code\\backend\\QQL.ServiceInterface\\BrandNs\\BrandService.cs:line 46\r\n   at ServiceStack.Host.ServiceRunner`1.ExecuteAsync(IRequest req, Object instance, TRequest requestDto) in C:\\BuildAgent\\work\\3481147c480f4a2f\\src\\ServiceStack\\Host\\ServiceRunner.cs:line 134\r\nClientConnectionId:4e48b28d-6647-470d-b943-698349521788\r\nError Number:208,State:1,Class:16\r\n",
    "errors": [],
    "meta": null
  }
}

As the exception message is “Invalid object name ‘CrudEvent’.”, I suspect my table is not being used as I did not indicate it when configuring the audit. Should it be auto-discovered via the naming I saw in the generated SQL for creating the table?

4

It expects the CrudEvent table to exist where ever the Auto CRUD tables are being used.

But you should have an opportunity to modify the OrmLite table Metadata for the CrudEvent model by customizing it on Startup. If it’s in a different table name specify the Alias, if it’s in a specific schema use Schema instead:

var meta = typeof(CrudEvent).GetModelMetadata();
meta.Alias = "...";      // if in different named table
meta.Schema = "...";     // if in different schema
5

Absolutely brilliant, that worked like a charm.

Thank you very much for the support and all of the thought that has, obviously, been poured into ServiceStack.

1 Like
6

One final, minor question, is it possible to remove (ignore) certain columns from the Audit (without writing a custom version)?

For example, I do not need to capture RemoteIp or the Urn values.

I am also unsure what the RefId, RefIdStr and Meta values represent.

7

You can add “more columns” by inheriting CrudEvent and using the generic OrmLiteCrudEvents<MyCrudEvent> constructor but ignoring existing columns is more difficult as by the time you access the metadata table array caches have already been created so the specific field definitions would need to be removed from both meta.FieldDefinitions and meta.FieldDefinitionsArray as such it’s likely easier to dynamically Add Attributes which needs to be added before the types are used anywhere, e.g. in the AppHost constructor:

public class AppHost : AppHostBase 
{
    public AppHost() 
    {
        typeof(CrudEvent)
            .GetProperty(nameof(CrudEvent.RemoteIp))
            .AddAttributes(new IgnoreAttribute());
        typeof(CrudEvent)
            .GetProperty(nameof(CrudEvent.Urn))
            .AddAttributes(new IgnoreAttribute());
    }
}

They’re effectively unused extension place holders if you wanted to attach your own metadata to these audit rows later. RefId/RefIdStr is also used in User Auth tables for adding a reference to your own row whilst Meta is a string dictionary for custom metadata.

8

Using the dynamic approached mentioned to remove the fields worked great. Thanks again.

1 Like