I use Serilog to log to a SQL database in most of my web applications and although it’s pretty well documented the details relevant to my projects are spread out over a few sites so I figured I’d gather them here together for future reference.

Packages

Install these packages from NuGet, though obviously you can adjust the sinks depending on where you prefer to log your events.

  • Serilog.AspNetCore
  • Serilog.Sinks.MSSqlServer
  • Serilog.Sinks.Console
  • Serilog.Sinks.ApplicationInsights

Code

Logging is best initialized in the Program class of the application as then if the application fails at startup details of the failure will logged, the downside of this is that configuration options aren’t available through dependency injection in Program so the configuration properties need to be retrieved explicitly.

This is based on the sample project from the Serilog.AspNetCore repository. If you’re adding a custom column as with the “VerificationCode” column below then you need to add it your logging table manually as it won’t be auto-created.

public class Program
{
	public static IConfiguration Configuration { get; } = new ConfigurationBuilder()
		.SetBasePath(Directory.GetCurrentDirectory())
		.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
		.AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)
		.AddEnvironmentVariables()
		.Build();

	public static int Main(string[] args)
	{
		ColumnOptions columnOptions = new ColumnOptions();
		// Don't include the Properties XML column.
		columnOptions.Store.Remove(StandardColumn.Properties);
		// Do include the log event data as JSON.
		columnOptions.Store.Add(StandardColumn.LogEvent);
		// Add additional VerificationCode column
		columnOptions.AdditionalDataColumns = new Collection
		{
			new DataColumn {DataType = typeof (string), ColumnName = "VerificationCode"},
		};
	
		Log.Logger = new LoggerConfiguration()
			.MinimumLevel.Debug()
			.MinimumLevel.Override("Microsoft", LogEventLevel.Information)
			.WriteTo.MSSqlServer(Configuration.GetConnectionString("LogConnection"), "_logs", columnOptions: columnOptions)
			.Enrich.FromLogContext()
			.WriteTo.Console()
			.WriteTo.ApplicationInsightsEvents("<YOUR_APPLICATION_INSIGHTS_KEY>")
			.CreateLogger();

		try
		{
			Log.Information("Starting web host");

			CreateWebHostBuilder(args).Build().Run();

			return 0;
		}
		catch (Exception ex)
		{
			Log.Fatal(ex, "Host terminated unexpectedly");
			return 1;
		}
		finally
		{
			Log.CloseAndFlush();
		}
	}

	public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
		WebHost.CreateDefaultBuilder(args)
			.UseConfiguration(Configuration)
			.UseStartup<Startup>()
			.UseSerilog();
}

The Serilog logger is also available in your controllers without needing to be specifically injected, this is available project wide because we’ve set the logger static property of the log in the Program.cs file.

public class HomeController : Controller
{
	[HttpGet]
	public async Task<IActionResult> Index()
	{
		Log.Information("In the controller!"); return View();
	}
}

The logs here go to the console (this can be viewed in debug by going to the output window and choosing “ASP.NET Core Web Server” from the “Show output list”), Application Insights and to a SQL database. Happily the SQL sink has now been updated for .NET Core and details can be found here. In the above example the additional column “LogEvent” is being captured as JSON in the logging SQL table. If creating a separate SQL user for logging it should be granted insert, update and select rights on the table.

grant insert, update, select on object::_logs to sql_user_resource

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *