Serilog configuration

Question

_{Tuesday, April 9, 2019 10:12 AM}

Before starting, I will let you know that I have been through the documentation on github for Serilog and for the SQL Sink. It isn't helping, which is why I am here. In my ASP.NET Core web API, I have this in the startup.cs class

    public class Startup
    {
        private static string sStartupErrors = "";

        public Startup(IConfiguration configuration)
        {
            // Init Serilog configuration
            Log.Logger = new LoggerConfiguration().ReadFrom.Configuration(configuration).CreateLogger();

            Serilog.Debugging.SelfLog.Enable(msg =>
            {
                sStartupErrors = msg;
            });

            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            var mvcBuilder = services.AddMvc();
            mvcBuilder.AddXmlSerializerFormatters();
            mvcBuilder.SetCompatibilityVersion(Microsoft.AspNetCore.Mvc.CompatibilityVersion.Version_2_1);

            services.Configure<IISOptions>(options =>
            {
                options.AutomaticAuthentication = false;
                options.ForwardClientCertificate = false;
            });

            services.Configure<ACLSettings>(Configuration.GetSection("ACLSettings"));
            services.AddMemoryCache();
        }

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

            Dictionary<string, string> glMappings = new Dictionary<string, string>();

            using (StreamReader file = File.OpenText(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Includes", "GLMapping.json")))
            {
                JsonSerializer serialiser = new JsonSerializer();
                glMappings = (Dictionary<string, string>)serialiser.Deserialize(file, typeof(Dictionary<string, string>));
            }

            cache.Set("GLMappings", glMappings);

            loggerFactory.AddSerilog();
            app.UseMvc();

            app.Run(async (context) =>
            {
                await context.Response.WriteAsync(sStartupErrors);
            });
        }
    }

Serilog is configured by reading a section in appsettings.json. The section in question is:

    "Serilog": {
        "Using": [ "Serilog.Sinks.MSSqlServer" ],
        "MinimumLevel": {
            "Default": "Information",
            "Override": {
                "Microsoft": "Warning"
            }
        },
        "WriteTo": [
            {
                "Name": "MSSqlServer",
                "Args": {
                    "connectionString": "Server=(local);Database=ACLWebAPILogs;trusted_connection=true",
                    "tableName": "ACLWebAPILog",
                    "restrictedToMinimumLevel": "Information",
                    "autoCreateSqlTable": true
                }
            }
        ]
    }

Now, in a busy application this database gets big, quickly. It's also slow to perform queries on. Those I can deal with for now. When logging in code I put lines like:

aclLogger.Log(LogLevel.Information, "Begin transaction in Bills Controller");

So in my controller, I have this at the start so I can see when a run on the bills controller is started. This is great, it dumps the message to the log database and all is good. However, using the above log entry as an example, I would like to have a new column in the logs database called ControllerName, so that when I send a log entry to the database, I can specify the ControllerName as "Bills". This means I don't need to put in lines like the above, and one at the end saying "End of transaction". By default Serilog has a number of columns.

Id
Message
MessageTemplate
Level
Timestamp
Exception
Properties

I'd like to configure Serilog so that it doesn't have the MessageTemplate and Properties columns. I'd like to have ControllerName as a custom column in the database, so that when I log something I get the name of the controller it came from. This will make searching the database easier as I can filter by the controller name.

I've tried reading so many pages on the web about this. I get told to use Enrichers, but from what I can see, they are additional packages that log specific information rather than custom information. I often get told that using the SQL sink isn't a great idea and to use something else. I get redirected to the github for Serilog or the Gitter chat. Neither help. I then get shown sites on how to configure it in code rather than appsettings. So, keeping things to this post only, can anyone help me here?

All replies (17)

_{Wednesday, April 10, 2019 8:51 AM ✅Answered}

Hi AnyUserNameThatLetsMeIn ,

Try the following

Make the following configuration in appsettings.json to removeStandardColumns and customColumns :

 "Serilog": {
    "Using": [ "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Data Source=XX",
          "tableName": "ControllerLogs",
          "autoCreateSqlTable": true,
          "columnOptionsSection": {
            "removeStandardColumns": [ "MessageTemplate ", "Properties" ],
            "customColumns": [
              {
                "ColumnName": "ControllerName",
                "DataType": "varchar",
                "DataLength": 50
              }            
            ]
          }
        }
      }
    ]
  }

Installed Packages

Create a ControllerNameEnricher middleware , Serilog.Context.LogContext can be used to dynamically add and remove properties from the ambient "execution context":

 public class ControllerNameEnricher
    {
        private readonly RequestDelegate next;

        public ControllerNameEnricher(RequestDelegate next)
        {
            this.next = next;
        }

        public Task Invoke(HttpContext context)
        {
            if(context.Request.Path.ToString().Contains("api"))
            {
                string[] results = context.Request.Path.ToString().Split('/');
                var controllerName = results[2];
                LogContext.PushProperty("ControllerName", controllerName);
            }
            return next(context);
        }
    }

In Configure method of Startup.cs , use .FromLogContext() to add LogContext to the logger at configuration-time :

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            Log.Logger = new LoggerConfiguration()
                              .ReadFrom.Configuration(Configuration)
                              .Enrich.FromLogContext()
                              .CreateLogger();
            Serilog.Debugging.SelfLog.Enable(msg =>
            {
                Debug.Print(msg);
                Debugger.Break();
            });

            loggerFactory.AddSerilog();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseHsts();
            }

            app.UseHttpsRedirection();

           app.UseMiddleware<ControllerNameEnricher>();
            app.UseMvc();
        }

Best Regards ,

Sherry

_{Wednesday, April 10, 2019 8:59 AM}

I will certainly look at this. I had read about Enrich.FromLogContext but then I lost it completely when it started talking about adding middleware enrichers. I read your code example and it seems to be straightforward when you put the explanation with it, so I will try and give it a go.

A side question. If I configure the appsettings to remove the columns from the database, what if the database is already in situ with those columns in it? Will I need to drop the table and let it recreate?

Thanks

Lee

_{Wednesday, April 10, 2019 9:18 AM}

Hi AnyUserNameThatLetsMeIn ,

Yes , you need .If not , the columns you want to remove still exist in the original database table, but their values are all null. Or you could modify the tableName in appsettings.json.

Best Regards ,

Sherry

_{Wednesday, April 10, 2019 12:51 PM}

I see the concept of what you're saying. Basically configure the table in appsetings. I did that.

{
    "Serilog": {
        "Using": [ "Serilog.Sinks.MSSqlServer" ],
        "MinimumLevel": {
            "Default": "Information",
            "Override": {
                "Microsoft": "Warning"
            }
        },
        "WriteTo": [
            {
                "Name": "MSSqlServer",
                "Args": {
                    "connectionString": "Server=(local);Database=ACLWebAPILogs;trusted_connection=true",
                    "tableName": "ACLWebAPILog2",
                    "restrictedToMinimumLevel": "Information",
                    "autoCreateSqlTable": true,
                    "columnOptionsSection": {
                        "disableTriggers": true,
                        "primaryKeyColumnName": "Message", // Was previously Id
                        "removeStandardColumns": [ "MessageTemplate", "Properties" ],
                        "additionalColumns": [
                            {
                                "ColumnName": "ControllerName",
                                "DataType": "nvarchar",
                                "DataLength": 30,
                                "AllowNull": false
                            }
                        ]
                    }
                }
            }
        ]
    }
}

I altered the name so it created a new table. It did create a new table with the correct name but it did not remove the MessageTemplate and Properties columns. I added the middleware as a standard class as I assumed that is what I should do. In Startup.cs it wanted me to add a using directive to the code to point to the ControllerNameEnricher so I did. No additional column was created in the new log table. It is standard out of the box.

Here is Startup.cs:

    public class Startup
    {
        private static string sStartupMessage = "<html><head><title></title></head><body><br>No function called.<br><br>Startup messages: <font color=red>xxxerrorxxx</font></body></html>";
        private static string sStartupErrors = "";

        public Startup(IConfiguration configuration)
        {
            // Init Serilog configuration
            Log.Logger = new LoggerConfiguration()
                .Enrich.FromLogContext()
                .ReadFrom.Configuration(configuration)
                .CreateLogger();

            Serilog.Debugging.SelfLog.Enable(msg =>
            {
                sStartupErrors = msg;
                sStartupMessage = sStartupMessage.Replace("xxxerrorxxx", sStartupErrors);
            });

            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            var mvcBuilder = services.AddMvc();
            mvcBuilder.AddXmlSerializerFormatters();
            mvcBuilder.SetCompatibilityVersion(Microsoft.AspNetCore.Mvc.CompatibilityVersion.Version_2_1);

            services.Configure<IISOptions>(options =>
            {
                options.AutomaticAuthentication = false;
                options.ForwardClientCertificate = false;
            });

            services.Configure<ACLSettings>(Configuration.GetSection("ACLSettings"));
            services.AddMemoryCache();
        }

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

            Dictionary<string, string> glMappings = new Dictionary<string, string>();

            using (StreamReader file = File.OpenText(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "Includes", "GLMapping.json")))
            {
                JsonSerializer serialiser = new JsonSerializer();
                glMappings = (Dictionary<string, string>)serialiser.Deserialize(file, typeof(Dictionary<string, string>));
            }

            cache.Set("GLMappings", glMappings);

            loggerFactory.AddSerilog();
            app.UseMiddleware<ControllerNameEnricher>();
            app.UseMvc();

            app.Run(async (context) =>
            {
                await context.Response.WriteAsync(sStartupMessage.Replace("xxxerrorxxx", ""));
            });
        }
    }

The enricher code is:

using Microsoft.AspNetCore.Http;
using Serilog.Context;
using System.Threading.Tasks;

namespace ACLWebApi.CustomClasses.ControllerNameEnricher
{
    public class ControllerNameEnricher
    {
        private readonly RequestDelegate next;

        public ControllerNameEnricher(RequestDelegate next)
        {
            this.next = next;
        }

        public Task Invoke(HttpContext context)
        {
            if (context.Request.Path.ToString().Contains("api"))
            {
                string[] results = context.Request.Path.ToString().Split('/');
                var controllerName = results[2];
                LogContext.PushProperty("ControllerName", controllerName);
            }

            return next(context);
        }
    }
}

When I log to it, I get no errors. Do I need to add anything in the individual controllers to make sure that the logcontext is added in anywhere? The code compiles and runs but none of the changes I expected are happening. Any thoughts?

Thanks

_{Thursday, April 11, 2019 2:27 AM}

Hi AnyUserNameThatLetsMeIn ,

Didn't you originally want to get the controller name where the the log information comes from when you log something in the controller ?

In my sample ,I created a SerilogController that logs some information to test :

[Route("api/[controller]")]
    [ApiController]
    public class SerilogController : ControllerBase
    {
        private readonly ILogger<SerilogController> _logger;
        public SerilogController(ILogger<SerilogController> logger)
        {
            _logger = logger;
        }
        [HttpGet]
        public IEnumerable<string> Get()
        {
            try
            {

                _logger.LogInformation("Could break here :(");
                throw new Exception("bohhhh very bad error");
            }
            catch (Exception e)
            {
                _logger.LogError(e, "It broke :(");
            }
            return new string[] { "value1", "value2","value3" };
        }
    }

The screenshot of the log database

Here is the sample I made and it worked well , you could download it and check the difference .

You could also take aside time to refer to the below links:

https://github.com/serilog/serilog-sinks-mssqlserver

https://www.carlrippon.com/adding-useful-information-to-asp-net-core-web-api-serilog-logs/

https://dzone.com/articles/serilog-tutorial-for-net-logging-16-best-practices

Best Regards ,

Sherry

_{Thursday, April 11, 2019 8:09 AM}

Thanks. I will look at those to see where mine is different. Does my config look ok to you?

_{Thursday, April 11, 2019 9:07 AM}

Serilog automatically created the table based on the config file I have previously posted. In it are the following columns:

Id
Message
MessageTemplate
Level
TimeStamp
Exception
Properties

It has named the table correctly and it writes to the log but no matter what I do, it is not removing the specified columns, nor adding the new ones. I tried downloading the example code you posted but it wouldn't let me get it.

Also curious, in your controller you define your logger like this:

private readonly ILogger<SerilogController> _logger;

If I make mine read only all references to the variable (in mine is called aclLogger) suddently generate an error. It says "An object reference is required for the non-static field, method, or property 'BillingsController.aclLogger'"

I'm clearly doing something wrong here.

_{Thursday, April 11, 2019 9:37 AM}

Hi AnyUserNameThatLetsMeIn ,

What is your environment and the version of your project ?

The demo that I posted tested in my computer , and there is no error thrown . Try to create a new simple project to test if it works?

Could you share a demo of your project that reproduce the issue ?

Best Regards ,

Sherry

_{Thursday, April 11, 2019 9:43 AM}

I'm using Visual Studio 2017. It is an ASP.NET Core 2.1 web API that uses MVC. I'll see if I can create another project from scratch and test this later. I really appreciate your help on this. It is so strange why things aren't working at all for me. I just cannot get my head around it at all. When I get something I will post the project for you.

The example you showed that used the middleware component works. It grabs the controller name perfectly as I can step through and see that happening. It's just that Serilog doesn't seem to read my config file properly. It gets some bits but it is as if it sees the columOptionsSection and just ignores it. The SQL database is SQL 2016.

_{Thursday, May 2, 2019 3:52 PM}

I'm still not getting this to work properly. I've posted on SO and tagged Serilog in it as I have also posted the question on github for the MSSqlServerSink and the only response was to upgrade from the latest to the dev version which I'm not happy to do in a live environment.

_{Tuesday, May 14, 2019 6:47 AM}

Hi AnyUserNameThatLetsMeIn ,

The difference in the version of package could affect the results. Please check the Installed packages provided in my first reply.

Best Regards ,

Sherry

_{Tuesday, May 14, 2019 7:51 AM}

Thanks for your input. It is most appreciated. I will be able to try the newer package later this week once I have got the last change request out of the way.

_{Thursday, May 30, 2019 11:41 AM}

Excellent. I upgraded Serilog's SQL Server Sink to the prerelease and it works as you said it would. I do have a related question but I will post another. It shouldn't be hard. I will try it out first on my own. If I get nowhere I will post on here. I am sure you'll know the answer.

Many thanks again.

_{Thursday, June 6, 2019 10:03 AM}

Hi! Whic is the last upgrad Serilog's SQL ?

Thanks

_{Thursday, June 6, 2019 11:07 AM}

I went into the Nuget package manager and selected prereleases and installed the latest prerelease of Serilog.Sinks.MSSqlServer which in my case is/was 5.133-dev-00236. This solved the issue for me.

_{Friday, June 7, 2019 9:15 AM}

It was hard... but I get It. Thank yuo very much!

_{Friday, June 7, 2019 9:38 AM}

Any time. Sherry Chen has helped me enormously on this part of my project. I wouldn't have got where I am with it without their help. Credit due must go there, rather than to me.

Last updated on 2019-04-09

Share via

Serilog configuration

Question

All replies (17)

Additional resources