Logging to a database with nLog

Nlog is a great tool for logging with in VB.Net applications(http://nlog-project.org/).  This post will discuss setting up nLog to write to both a local log file and a a SQL database.

First, we need to configure the log file;

<?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" internalLogFile="Nlog.log">
    <targets>
        <target name="file" xsi:type="File"   layout="${longdate}|${level}|${callsite}|${logger}|${threadid}|${windows-identity:domain=false}--${message} ${exception:format=message,stacktrace:separator=*"  fileName="c:\psnet\myapplication.log" />
    <target name="database" type="Database">
      <connectionString>
        Data Source=databaseservername;Initial Catalog=databasename;User Id=username;Password=password;
      </connectionString>
      <commandText>
        insert into system_logging(log_date,log_level,log_logger,log_message,log_machine_name, log_user_name, log_call_site, log_thread, log_exception, log_stacktrace) values(@time_stamp, @level, @logger, @message,@machinename, @user_name, @call_site, @threadid, @log_exception, @stacktrace);
      </commandText>
      <parameter name="@time_stamp" layout="${longdate}"/>
      <parameter name="@level" layout="${level}"/>
      <parameter name="@logger" layout="${logger}"/>
      <parameter name="@message" layout="${message}"/>
      <parameter name="@machinename" layout="${machinename}"/>
      <parameter name="@user_name" layout="${windows-identity:domain=true}"/>
      <parameter name="@call_site" layout="${callsite:filename=true}"/>

      <parameter name="@threadid" layout="${threadid}"/>
      <parameter name="@log_exception" layout="${exception}"/>
      <parameter name="@stacktrace" layout="${stacktrace}"/>

    </target>
    </targets>
    <rules>
        <logger name="*" minlevel="Info" writeTo="file"/>
    <logger name="*" minlevel="Info" appendTo="database"/>
    </rules>
</nlog>

There are a couple fields that need to be configured (in bold).  One adjustment for testing is setting the (internalLogFile="Nlog.log").  This will assist us in debugging any database issues we have as this is nLog’s own log file.

Next, we need to created a matching database table;

/****** Object:  Table [dbo].[system_logging]    Script Date: 04/21/2010 17:05:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[system_logging](
    [system_logging_guid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [entered_date] [datetime] NULL,
    [log_application] [varchar](200) NULL,
    [log_date] [varchar](100) NULL,
    [log_level] [varchar](100) NULL,
    [log_logger] [varchar](8000) NULL,
    [log_message] [varchar](8000) NULL,
    [log_machine_name] [varchar](8000) NULL,
    [log_user_name] [varchar](8000) NULL,
    [log_call_site] [varchar](8000) NULL,
    [log_thread] [varchar](100) NULL,
    [log_exception] [varchar](8000) NULL,
    [log_stacktrace] [varchar](8000) NULL,
CONSTRAINT [PK_system_logging] PRIMARY KEY CLUSTERED
(
    [system_logging_guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[system_logging] ADD  CONSTRAINT [DF_system_logging_system_logging_guid]  DEFAULT (newid()) FOR [system_logging_guid]
GO

ALTER TABLE [dbo].[system_logging] ADD  CONSTRAINT [DF_system_logging_entered_date]  DEFAULT (getdate()) FOR [entered_date]
GO

You must ensure that you give access to this table to the username you provide in the nLog configuration file.

In order to get emailed with error entries simple add this trigger to the system_logging table;

/****** Object:  Trigger [dbo].[LogEmail]    Script Date: 04/21/2010 17:06:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Justin Davis
-- Create date: 4/1/2008
-- Description:    Send email of log message
-- =============================================
ALTER TRIGGER [dbo].[LogEmail]
   ON  [dbo].[system_logging]
   AFTER insert
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    Declare @ToEmail varchar(100)
    Declare @Title varchar(100)
    Declare @logmessage varchar(8000)
    declare    @loglevel as varchar(100)   
    set @ToEmail = 'youremailaddress'
    set @Title = 'Error'
    set @loglevel = (select log_level from inserted)
    set @logmessage = (select
    'User Date:' + char(9) + char(9) + log_date + char(13) + char(10) +
    'Computer:'+ char(9) + log_machine_name + char(13) + char(10) + 
    'User:' + char(9) + char(9) + log_user_name + char(13) + char(10) + 
    'Level:' + char(9)+ log_level + char(13) + char(10) + 
    'Logger:' + char(9)+ log_logger + char(13) + char(10) +
    'Thread:'+ char(9) + log_thread + char(13) + char(10) +   
    'StackTrace:'+ char(9) + log_stacktrace + char(13) + char(10) + 
    'CallSite:'+ char(9) + log_call_site + char(13) + char(10) +
    'Message:' + char(9) + log_message + char(13) + char(10) + 
    'Exception:'+ char(9) + log_exception as 'emailmessage'   
    from inserted)
    if @loglevel <>'Info'
    EXEC msdb.dbo.sp_send_dbmail @recipients=@ToEmail, @body= @logmessage,  @subject = @Title, @profile_name = 'default'

END

Make sure that you configure SQL mail first.  You might get an exception in the nlog.log file like this;

2010-04-21 13:53:02.0729 Error Target exception: System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at NLog.Targets.DatabaseTarget.DoAppend(LogEventInfo logEvent)
   at NLog.Targets.DatabaseTarget.Write(LogEventInfo logEvent)
   at NLog.LoggerImpl.Write(Type loggerType, TargetWithFilterChain targets, LogEventInfo logEvent, LogFactory factory)

If so you it is because the username in the nlog.config file does not have access rights to the send mail SP.  Run this script to fix.

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'logger';
GO

 

References

http://nlog-project.org/