ASP.NET Core logging with NLog and SQL Server

NLog is a free logging platform for .NET, Xamarin, Silverlight and Windows Phone with rich log routing and management capabilities. NLog makes it easy to produce and manage high-quality logs for your application regardless of its size or complexity. NLog can dynamically write to one of multiple targets for each log message, including text files, Event Logs, email, and SQL Server.

In this article by Damien Bod shows how to use NLog to write your log events to SQL Server.

The NLog.Extensions.Logging is required to add NLog to a ASP.NET Core application. This package as well as the System.Data.SqlClient are added to the dependencies in the project.json file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
"dependencies": {
       "Microsoft.NETCore.App": {
           "version": "1.0.0",
           "type": "platform"
       },
       "Microsoft.AspNetCore.Mvc": "1.0.0",
       "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
       "Microsoft.AspNetCore.Diagnostics": "1.0.0",
       "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
       "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
       "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
       "Microsoft.Extensions.Configuration.Json": "1.0.0",
       "Microsoft.Extensions.Logging": "1.0.0",
       "Microsoft.Extensions.Logging.Console": "1.0.0",
       "Microsoft.Extensions.Logging.Debug": "1.0.0",
       "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
       "NLog.Extensions.Logging": "1.0.0-rtm-alpha4",
       "System.Data.SqlClient": "4.1.0"
 },

Now a nlog.config file is created and added to the project. This file contains the configuration for NLog. In the file, the targets for the logs are defined as well as the rules. An internal log file is also defined, so that if something is wrong with the logging configuration, you can find out why.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
<?xml version="1.0" encoding="utf-8" ?>
      autoReload="true"
      internalLogLevel="Warn"
      internalLogFile="C:\git\damienbod\AspNetCoreNlog\Logs\internal-nlog.txt">
    
  <targets>
    <target xsi:type="File" name="allfile" fileName="nlog-all.log"
                layout="${longdate}|${event-properties:item=EventId.Id}|${logger}|${uppercase:${level}}|${message} ${exception}" />
    <target xsi:type="File" name="ownFile-web" fileName="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" >
    <connectionString>
        Data Source=N275\MSSQLSERVER2014;Initial Catalog=Nlogs;Integrated Security=True;
    </connectionString>
<!--
  Remarks:
    The appsetting layouts require the NLog.Extended assembly.
    The aspnet-* layouts require the NLog.Web assembly.
    The Application value is determined by an AppName appSetting in Web.config.
    The "NLogDb" connection string determines the database that NLog write to.
    The create dbo.Log script in the comment below must be manually executed.
  Script for creating the dbo.Log table.
  SET ANSI_NULLS ON
  SET QUOTED_IDENTIFIER ON
  CREATE TABLE [dbo].[Log] (
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Application] [nvarchar](50) NOT NULL,
      [Logged] [datetime] NOT NULL,
      [Level] [nvarchar](50) NOT NULL,
      [Message] [nvarchar](max) NOT NULL,
      [Logger] [nvarchar](250) NULL,
      [Callsite] [nvarchar](max) NULL,
      [Exception] [nvarchar](max) NULL,
    CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
      WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
-->
          <commandText>
              insert into dbo.Log (
              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>

The nlog.config also needs to be added to the publishOptions in the project.json file.

1
2
3
4
5
6
7
8
9
10
"publishOptions": {
   "include": [
       "wwwroot",
       "Views",
       "Areas/**/Views",
       "appsettings.json",
       "web.config",
       "nlog.config"
   ]
 },

Now the database can be setup. You can create a new database, or use and existing one and add the dbo.Log table to it using the script below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Application] [nvarchar](50) NOT NULL,
    [Logged] [datetime] NOT NULL,
    [Level] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [Logger] [nvarchar](250) NULL,
    [Callsite] [nvarchar](max) NULL,
    [Exception] [nvarchar](max) NULL,
  CONSTRAINT [PK_dbo.Log] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The table in the database must match the configuration defined in the nlog.config file. The database target defines the connection string, the command used to add a log and also the parameters required.

You can change this as required. As yet, most of the NLog parameters, do not work with ASP.NET Core, but this will certainly change as it is in early development. The NLog.Web Nuget package, when completed will contain the ASP.NET Core parameters.

Advertisements

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