Oracle Table Schema for NLog Integration

Create a data base table to store structured log entries:

CREATE TABLE APPLICATION_LOGS (
  log_id          VARCHAR2(60)  NOT NULL,
  application     VARCHAR2(20),
  component       VARCHAR2(30),
  function_name   VARCHAR2(30),
  action_type     VARCHAR2(20),
  source_class    VARCHAR2(500),
  message         VARCHAR2(3000) NOT NULL,
  client_ip       VARCHAR2(32),
  timestamp       VARCHAR2(36),
  user_id         VARCHAR2(36),
  create_date     DATE DEFAULT SYSDATE,
  severity        VARCHAR2(12)
);

NLog Configuration for Oracle Target

Configure NLog to write to Oracle using this XML configurasion:

<?xml version="1.0" encoding="utf-8"?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      throwExceptions="false"
      internalLogLevel="Info" internalLogFile="c:/temp/nlog-internal.txt">
  
  <targets>
    <target name="oracleDb" xsi:type="Database" keepConnection="false"
            dbProvider="Oracle.ManagedDataAccess.Client.OracleConnection, Oracle.ManagedDataAccess"
            connectionString="Data Source=ORCL;User Id=logger;Password=Secr3tP@ss;"
            commandText="INSERT INTO APPLICATION_LOGS(log_id,application,component,function_name,action_type,source_class,message,client_ip,timestamp,user_id,severity) 
                         VALUES(:log_id,:app,:component,:func,:action,:source,:msg,:ip,:ts,:user,:lvl)">
      
      <parameter name="log_id" layout="${event-context:item=log_id}" />
      <parameter name="app" layout="${event-context:item=application}" />
      <parameter name="component" layout="${event-context:item=component}" />
      <parameter name="func" layout="${event-context:item=function}" />
      <parameter name="action" layout="${event-context:item=action}" />
      <parameter name="source" layout="${event-context:item=source_class}" />
      <parameter name="msg" layout="${event-context:item=message}" />
      <parameter name="ip" layout="${event-context:item=client_ip}" />
      <parameter name="ts" layout="${longdate}" />
      <parameter name="user" layout="${event-context:item=user_id}" />
      <parameter name="lvl" layout="${level:uppercase=true}" />
    </target>
  </targets>

  <rules>
    <logger name="*" minlevel="Trace" writeTo="oracleDb" />
  </rules>
</nlog>

C# Logger Implementation

Custom logger class for structured event loggging:

using NLog;
using System;

public class StructuredLogger
{
    public const string SystemUser = "system";
    public const string Localhost = "127.0.0.1";
    
    private readonly Logger _nlogLogger;
    
    public StructuredLogger(Logger logger) => _nlogLogger = logger;
    public StructuredLogger(string name) : this(LogManager.GetLogger(name)) { }
    
    public static StructuredLogger Current { get; } = new StructuredLogger(LogManager.GetCurrentClassLogger());

    public void LogEvent(string app, string module, string function, LogAction action, 
                         string message, string ip = Localhost, string user = SystemUser, 
                         string source = null, LogLevel level = null)
    {
        if (message.Length > 3000) message = message[..3000];
        
        var logEvent = new LogEventInfo(level ?? LogLevel.Info) {
            Message = message
        };

        logEvent.Properties["log_id"] = Guid.NewGuid().ToString("D");
        logEvent.Properties["application"] = app;
        logEvent.Properties["component"] = module;
        logEvent.Properties["function"] = function;
        logEvent.Properties["action"] = action;
        logEvent.Properties["source_class"] = source ?? string.Empty;
        logEvent.Properties["client_ip"] = ip;
        logEvent.Properties["user_id"] = user;
        
        _nlogLogger.Log(logEvent);
    }
}

public enum LogAction
{
    Create,
    Modify,
    Approve,
    Remove,
    Query,
    Authenticate,
    Display
}

ASP.NET Core Usage Example

Log custom events in application code:

StructuredLogger.Current.LogEvent("Admin", "Security", "UserMgmt", 
    LogAction.Query, "Authorization check", user: "admin");

StructuredLogger.Current.LogEvent("Reporting", "Export", "PDF", 
    LogAction.Create, "Generated report", level: LogLevel.Warn);