.NET Core, ASP.NET Core logging with NLog and PostgreSQL

This article shows how .NET Core or ASP.NET Core applications can log to a PostgreSQL database using NLog.

Code: https://github.com/damienbod/AspNetCoreNlog

Other posts in this series:

  1. ASP.NET Core logging with NLog and Microsoft SQL Server
  2. ASP.NET Core logging with NLog and Elasticsearch
  3. Settings the NLog database connection string in the ASP.NET Core appsettings.json
  4. .NET Core logging to MySQL using NLog
  5. .NET Core logging with NLog and PostgreSQL

Setting up PostgreSQL

pgAdmin can be used to setup the PostgreSQL database which is used to save the logs. A log database was created for this demo, which matches the connection string in the nlog.config file.

Using the pgAdmin, open a query edit view and execute the following script to create a table in the log database.

CREATE TABLE logs
( 
    Id serial primary key,
    Application character varying(100) NULL,
    Logged text,
    Level character varying(100) NULL,
    Message character varying(8000) NULL,
    Logger character varying(8000) NULL, 
    Callsite character varying(8000) NULL, 
    Exception character varying(8000) NULL
)

At present it is not possible to log a date property to PostgreSQL using NLog, only text fields are supported. A github issue exists for this here. Due to this, the Logged field is defined as a text, and uses the DateTime value when the log is created.

.NET or ASP.NET Core Application

The required packages need to be added to the csproj file. For an ASP.NET Core aplication, add NLog.Web.AspNetCore and Npgsql, for a .NET Core application add NLog and Npgsql.

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp1.1</TargetFramework>
    <AssemblyName>ConsoleNLogPostgreSQL</AssemblyName>
    <OutputType>Exe</OutputType>
    <PackageId>ConsoleNLog</PackageId>
    <PackageTargetFallback>$(PackageTargetFallback);dotnet5.6;portable-net45+win8</PackageTargetFallback>
    <GenerateAssemblyConfigurationAttribute>false</GenerateAssemblyConfigurationAttribute>
    <GenerateAssemblyCompanyAttribute>false</GenerateAssemblyCompanyAttribute>
    <GenerateAssemblyProductAttribute>false</GenerateAssemblyProductAttribute>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration.FileExtensions" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Logging" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.1" />
    <PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="1.1.1" />
    <PackageReference Include="NLog.Web.AspNetCore" Version="4.3.1" />
    <PackageReference Include="Npgsql" Version="3.2.2" />
    <PackageReference Include="System.Data.SqlClient" Version="4.3.0" />
  </ItemGroup>
</Project>

Or use the NuGet package manager in Visual Studio 2017.

The nlog.config file is then setup to log to PostgreSQL using the database target with the dbProvider configured for Npgsql and the connectionString for the required instance of PostgreSQL. The commandText must match the database setup in the TSQL script. If you add, for example extra properties from the NLog.Web.AspNetCore package to the logs, these also need to be added here.

<?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"
      internalLogLevel="Warn"
      internalLogFile="C:\git\damienbod\AspNetCoreNlog\Logs\internal-nlog.txt">
  
  <targets>
    <target xsi:type="File" name="allfile" fileName="${var:configDir}\nlog-all.log"
                layout="${longdate}|${event-properties:item=EventId.Id}|${logger}|${uppercase:${level}}|${message} ${exception}" />

    <target xsi:type="File" name="ownFile-web" fileName="${var:configDir}\nlog-own.log"
             layout="${longdate}|${event-properties:item=EventId.Id}|${logger}|${uppercase:${level}}|  ${message} ${exception}" />

    <target xsi:type="Null" name="blackhole" />

    <target name="database" xsi:type="Database"
              dbProvider="Npgsql.NpgsqlConnection, Npgsql"
              connectionString="User ID=damienbod;Password=damienbod;Host=localhost;Port=5432;Database=log;Pooling=true;"
             >

          <commandText>
              insert into logs (
              Application, Logged, Level, Message,
              Logger, CallSite, Exception
              ) values (
              @Application, @Logged, @Level, @Message,
              @Logger, @Callsite, @Exception
              );
          </commandText>

          <parameter name="@application" layout="AspNetCoreNlog" />
          <parameter name="@logged" layout="${date}" />
          <parameter name="@level" layout="${level}" />
          <parameter name="@message" layout="${message}" />

          <parameter name="@logger" layout="${logger}" />
          <parameter name="@callSite" layout="${callsite:filename=true}" />
          <parameter name="@exception" layout="${exception:tostring}" />
      </target>
      
  </targets>

  <rules>
    <!--All logs, including from Microsoft-->
    <logger name="*" minlevel="Trace" writeTo="allfile" />
      
    <logger name="*" minlevel="Trace" writeTo="database" />
      
    <!--Skip Microsoft logs and so log only own logs-->
    <logger name="Microsoft.*" minlevel="Trace" writeTo="blackhole" final="true" />
    <logger name="*" minlevel="Trace" writeTo="ownFile-web" />
  </rules>
</nlog>

When using ASP.NET Core, the NLog.Web.AspNetCore can be added to the nlog.config file to use the extra properties provided here.

<extensions>
     <add assembly="NLog.Web.AspNetCore"/>
</extensions>
            

Using the log

The logger can be used using the LogManager or added to the NLog log configuration in the Startup class in an ASP.NET Core application.

Basic example:

LogManager.Configuration.Variables["configDir"] = "C:\\git\\damienbod\\AspNetCoreNlog\\Logs";

var logger = LogManager.GetLogger("console");
logger.Warn("console logging is great");
logger.Error(new ArgumentException("oh no"));

Startup configuration in an ASP.NET Core application:

public void ConfigureServices(IServiceCollection services)
{
	services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();
	// Add framework services.
	services.AddMvc();

	services.AddScoped<LogFilter>();
}

// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
	loggerFactory.AddNLog();

	//add NLog.Web
	app.AddNLogWeb();

	////foreach (DatabaseTarget target in LogManager.Configuration.AllTargets.Where(t => t is DatabaseTarget))
	////{
	////	target.ConnectionString = Configuration.GetConnectionString("NLogDb");
	////}
	
	////LogManager.ReconfigExistingLoggers();

	LogManager.Configuration.Variables["connectionString"] = Configuration.GetConnectionString("NLogDb");
	LogManager.Configuration.Variables["configDir"] = "C:\\git\\damienbod\\AspNetCoreNlog\\Logs";

	app.UseMvc();
}

When the application is run, the logs are added to the database.

Links

https://www.postgresql.org/

https://www.pgadmin.org/

https://github.com/nlog/NLog/wiki/Database-target

https://github.com/NLog/NLog.Extensions.Logging

https://github.com/NLog

https://docs.asp.net/en/latest/fundamentals/logging.html

https://msdn.microsoft.com/en-us/magazine/mt694089.aspx

https://docs.asp.net/en/latest/fundamentals/configuration.html

Advertisements

4 comments

  1. […] .NET Core, ASP.NET Core logging with NLog and PostgreSQL (Damien Bowden) […]

  2. Nice article. But it would be better to use NoSql part of Postgres (using JSONB). You can simply analyze text part of log e.g. Stacktrace methods ….. Just note : Don’t mix production and application data (like a logs) in one database.

    1. makes sense, thanks for the comment

  3. Richard de Zwart · · Reply

    Helped a lot. To complete it for me, since I need logging in a Console App and I do not get a loggerFactory passed in anywhere, I added the following:

    ILoggerFactory loggerFactory = new LoggerFactory();
    loggerFactory.AddNLog();

    var serviceProvider = new ServiceCollection()
    .AddSingleton(loggerFactory)
    .AddLogging();

    This will assure that every class that has an ILogger argument, will get a NLog logger injected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: