Navigation

Wednesday, April 21, 2010

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/

9 comments:

Justin Davis said...

I have been asked to add the additional steps in configuring the sql mail and will update the post as soon as possiable to include each step in the process.

Anonymous said...

Thanks for the nice post. It really helped me with getting my NLog logging into a database. Funny that you are involved with public safety software. I work for Intergraph, although not in that area.

com dot ingr at wageoghe

cshandler said...

Yeah this is really a nice post I like it and it saved my time for creating log table :)

Thanks for sharing.

darichkid said...

This is even better than NLog:
http://www.kellermansoftware.com/p-14-net-logging-library.aspx

Aram Ahmad said...

Hi
very useful post, but could you please explain how i can configure my configuration file if i want to save my logging on remote database ??

Unknown said...

Hi,
Thanks for the nice post. i am having one query.When logging in the database using stored procedure with nlog how can we get the out parameter value.

Unknown said...

Hi,
Great post! I almost feel like I'm cheating because of all the work you've saved me!

Anonymous said...

Excellent post!
Works the first time!

Jukka Aakula said...

Thank you!