Setting up Serilog to write logs to SQL Server db table

Question

I'm trying to set up Serilog to write logs to my db table.

Here's the breakdown of what I did to try and set up logging to SQL Server.

1) Install the Nuget Packages, Serilog and Serilog.Sinks.MSSqlServer

2) I set up a class called Logger.cs that houses all the configuration for Serilog:

public static class Logger
{
    private static readonly ILogger _Logger;

    static Logger()
    {

        var connStr = "data source=DESKTOP-BLAH;Initial Catalog=DBNAME;Trusted_Connection=True;";

        _Logger = new LoggerConfiguration()
            .WriteTo.MSSqlServer(connStr, "ApplicationLogs", 
            columnOptions: GetSqlColumnOptions(), restrictedToMinimumLevel: LogEventLevel.Debug, batchPostingLimit: 1)
            .CreateLogger();

        Serilog.Debugging.SelfLog.Enable(msg => Debug.WriteLine(msg));

    }

    public static ColumnOptions GetSqlColumnOptions()
    {
        var colOptions = new ColumnOptions();

        colOptions.Store.Remove(StandardColumn.Properties);
        colOptions.Store.Remove(StandardColumn.MessageTemplate);
        colOptions.Store.Remove(StandardColumn.Message);
        colOptions.Store.Remove(StandardColumn.Exception);
        colOptions.Store.Remove(StandardColumn.TimeStamp);
        colOptions.Store.Remove(StandardColumn.Level);

        colOptions.AdditionalDataColumns = new Collection<DataColumn>
        {
            new DataColumn{DataType = typeof(DateTime), ColumnName = "LogTimeStamp"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "RecordNum"},
            new DataColumn{DataType = typeof(string), ColumnName = "ComputerName"},
            new DataColumn{DataType = typeof(DateTime), ColumnName = "ProcessTimeStamp"},
            new DataColumn{DataType = typeof(string), ColumnName = "LogGroup"},
            new DataColumn{DataType = typeof(string), ColumnName = "Type"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "EventId"},
            new DataColumn{DataType = typeof(string), ColumnName = "UserId"},
            new DataColumn{DataType = typeof(Int32), ColumnName = "Line"},
            new DataColumn{DataType = typeof(string), ColumnName = "Description"},
            new DataColumn{DataType = typeof(string), ColumnName = "Source"},
            new DataColumn{DataType = typeof(string), ColumnName = "Data"},
            new DataColumn{DataType = typeof(DateTime), ColumnName = "AddTimestamp"},
            new DataColumn{DataType = typeof(string), ColumnName = "DeviceID"}

        };
        return colOptions;
    }


    public static void WriteError(ApplicationLog infoToLog)
    {
        try
        {        
            _Logger.Error(LogEventLevel.Error,
                    "{LogTimestamp}{RecordNum}{ComputerName}{ProcessTimeStamp}{LogGroup}" +
                    "{Type}{EventId}{UserId}{Line}" +
                    "{Description}{Source}{Data}{AddTimestamp}{DeviceID}",
                    infoToLog.LogTimestamp, infoToLog.RecordNum,
                    infoToLog.ComputerName, infoToLog.ProcessTimestamp,
                    infoToLog.LogGroup, infoToLog.Type,
                    infoToLog.EventId, infoToLog.UserId,
                    infoToLog.Line, infoToLog.Description,
                    infoToLog.Source, infoToLog.Data,
                    infoToLog.AddTimestamp, infoToLog.DeviceId);
        }
        catch(Exception ex)
        {

        }
    }
}

3) Then I believe I should be good to go and I set it up like this:

ApplicationLog appLog = new ApplicationLog()
{
    LogTimestamp = DateTime.Now,
    RecordNum = 1,
    ComputerName = Environment.MachineName,
    ProcessTimestamp = DateTime.Now,
    LogGroup = "10",
    Type = "T",
    EventId = 23,
    UserId = Environment.UserName,
    Line = 0,
    Description = "Duplicate Failed to Upload",
    Source = "FooController.cs",
    Data = "DBNAME",
    AddTimestamp = DateTime.Now,
    DeviceId = "15"

};

Logger.WriteError(appLog);

4) Created table in my DB already and just waiting for the data from logging to populate.

However, it seems like it still doesn't get saved in my SQL Server db table.

I'm wondering if I missed any steps or what I did wrong for the setup here?

You didn't mention [creating the necessary table or using `autoCreateSqlTable`.](https://github.com/serilog/serilog-sinks-mssqlserver) — Crowcoder, Oct 25 '17 at 20:26
Do I still need to create the table if I already have the table inside my db and I setup the connection string or no? I saw that in the tutorial but I wasn't sure if that applied to me @Crowcoder. Thanks! — Euridice01, Oct 25 '17 at 20:38
You should be good if you already have the table, I just wasn't sure since you went over all your steps except that one. — Crowcoder, Oct 25 '17 at 20:40
ah ok, just edited my post to include that part. Any other ideas what could be wrong? Thanks :) — Euridice01, Oct 25 '17 at 20:41
I see you don't set `MinimumLevel` on `LoggerConfiguration` - unless `restrictedToMinimumLevel: LogEventLevel.Debug` takes care of that. I don't have much experience using that particular sink. — Crowcoder, Oct 25 '17 at 20:45
@Crowcoder yeah that takes care of that. Through the MSSQLServer sink. It uses a restrictedToMinimumLevel instead. — Euridice01, Oct 25 '17 at 20:56
This Link Just Sample Serilog Sink To Sql server Launch on Asp.Net Mvc and Asp.net WebApi GitHub. https://github.com/amingolmahalle/Serilog_Sink_To_SqlServer/tree/master — Amin Golmahalleh, Feb 19 '19 at 06:04

score 4 · Answer 1 · answered Jan 31 '19 at 09:56

4

Can you try using the self logging of serilog to see what is happening:

If you are using .net core, put following in Program.main()

Serilog.Debugging.SelfLog.Enable(msg =>
{
Debug.Print(msg);
Debugger.Break();
});

answered Jan 31 '19 at 09:56

mukesh joshi

584
5
19

score 1 · Answer 2 · answered Nov 23 '22 at 03:16

1

Try adding the parameters TrustServerCertificate and Encrypt to true on your connection string.

Data Source=[server];Initial Catalog=[database];User Id=[user];Password=[password];TrustServerCertificate=True;Encrypt=True;

answered Nov 23 '22 at 03:16

MathSee

11
1

score 0 · Answer 3 · answered Apr 13 '20 at 07:33

You should write

_Logger.Dispose();

after

_Logger.Error(LogEventLevel.Error,
                "{LogTimestamp}{RecordNum}{ComputerName}{ProcessTimeStamp}{LogGroup}" +
                "{Type}{EventId}{UserId}{Line}" +
                "{Description}{Source}{Data}{AddTimestamp}{DeviceID}",
                infoToLog.LogTimestamp, infoToLog.RecordNum,
                infoToLog.ComputerName, infoToLog.ProcessTimestamp,
                infoToLog.LogGroup, infoToLog.Type,
                infoToLog.EventId, infoToLog.UserId,
                infoToLog.Line, infoToLog.Description,
                infoToLog.Source, infoToLog.Data,
                infoToLog.AddTimestamp, infoToLog.DeviceId);

Setting up Serilog to write logs to SQL Server db table

3 Answers3