serilog-mssql/serilog-sinks-mssqlserver

A Serilog sink that writes events to Microsoft SQL Server and Azure SQL

serilog-sinks-mssqlserver's Issues

Configuration via Serilog.Settings.Configuration

With Serilog.Settings.AppSettings, I can do this:

In Web.config - MSSqlServerSettingsSection:

<MSSqlServerSettingsSection>
    <Columns>
      <add ColumnName="Entrypoint" DataType="varchar"/>
      <add ColumnName="Username" DataType="varchar"/>
    </Columns>
  </MSSqlServerSettingsSection>

In Web.config - appSettings:

<appSettings>
  <add key="serilog:using:MSSqlSever" value="Serilog.Sinks.MSSqlServer" />
  <add key="serilog:write-to:MSSqlServer.restrictedToMinimumLevel" value="Information" />
  <add key="serilog:write-to:MSSqlServer.connectionString" value="Data Source=server;Initial Catalog=db;Integrated Security=True;MultipleActiveResultSets=True"/>
  <add key="serilog:write-to:MSSqlServer.tableName" value="Log"/>
</appSettings>

In code:

var logger = new LoggerConfiguration()
	.Enrich.WithProperty("Entrypoint", "MyEntryPoint")
	.MinimumLevel.Debug()
	.WriteTo.Trace(outputTemplate: outputTemplate)
	.ReadFrom.AppSettings() //read additional sinks from .config
	.CreateLogger();

Can I achieve the same thing by replacing Serilog.Settings.AppSettings with Serilog.Settings.Configuration in an ASP.NET Core project targetting .NET 4.6.2?

I tried this, but it doesn't seem to work:

In appsettings.json:

"Serilog": {
  "WriteTo": [
    {
  	"Name": "MSSqlSever",
  	"Args": {
  	  "restrictedToMinimumLevel": "Information",
  	  "connectionString": "Data Source=server;Initial Catalog=db;Integrated Security=True;MultipleActiveResultSets=True",
  	  "tableName":  "Log" 
  	}
    }
  ]
}

In code - Startup.cs:

var logger = new LoggerConfiguration()
	.Enrich.WithProperty("Entrypoint", "MyEntryPoint")
	.MinimumLevel.Debug()
	.WriteTo.Trace(outputTemplate: outputTemplate)
	.ReadFrom.Configuration(Configuration) //read additional sinks from appsettings.json
	.CreateLogger();

Thanks in advance!

logging only first item

I am using .net core 2.0 web application and I have a logging middleware it works fine for the first request when application starts and then it some times writing the other requests but most of the requests getting lost. I always see first request in db table and then sometime some other but most of the requests I do not see anything. I have debugging to console enabled and do not see any error in console as well. Can someone please help me figuring out the issue ?

Thanks

Writing Information to SQL table

Hi,

I have following code:

class Program
    {
        static void Main(string[] args)
        {

            var connectionString = "conn";
            var tableName = "Logs";
            var columnOptions = new ColumnOptions();  // optional

            //Serilog.Debugging.SelfLog.Enable(Console.Error);

            var log = new LoggerConfiguration()
                .WriteTo.LiterateConsole()
                .MinimumLevel.Debug()
                .WriteTo.MSSqlServer(connectionString: connectionString, tableName: tableName, columnOptions: columnOptions, restrictedToMinimumLevel: Serilog.Events.LogEventLevel.Verbose)
                .WriteTo.RollingFile("log-{Date}.txt")
                .CreateLogger();

            log.Information("Hello, Serilog!");
            var position = new { Latitude = 25, Longitude = 134 };
            var elapsedMs = 34;

            log.Information("Processed {@Position} in {Elapsed} ms.", position, elapsedMs);
            for (int i = 0; i < 100; i++)
            {
                log.Error("error!");

            }
            //log.Verbose("Verbose test");
            //log.Verbose("test");
            //log.Information(log.IsEnabled(Serilog.Events.LogEventLevel.Verbose).ToString());
            System.Threading.Thread.Sleep(1000);
        }
    }

Console and File sink have all logs while SQL table contains only first log "Hello Serilog" without other logs. If I comment out that line, all other logs are written. Looks like like there is an issue with log.Information("Hello, Serilog!"); when writing to MSSQL.

I'm using latest version of all components taken from NuGet. Console App, .NET 4.5.1.

Support Clustered Columnstore Indexes (CCI)

Your sample schema for Log table is a classic B-Tree, which is not optimal for storing large amount of data such as logs. CLUSTERED COLUMNSTORE index should be better choice for storing logs when JSON format is used (not applicable to XML).

• CCI are available in all editions of SQL Server (even in Express) since SQL-2016 SP1.
• CCI support nvarchar(max) type in the current CTP and Azure SQL Database, so we can store LogEvent column in CCI
• JSON support is available since SQL-2016, which enables us to query LogEvent column using the functions like JSON_VALUE(LogEvent, '$.Propertes.RequestPath')

Unfortunately XML is not yet supported in CCI, but if someone uses JSON as log format, CCI would be much better option than B-tree (JSON is stored in nvarchar(max)). Could you update your docs and propose something like the following table schema if JSON logging is used with the latest SQL Server:

CREATE TABLE [Logs] (

   [Message] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,
   [LogEvent] nvarchar(max) NULL,
   [Exception] nvarchar(max) NULL,
   INDEX cci CLUSTERED COLUMNSTORE
)

If property matching AdditionalDataColumn does not exist in Serilog dictionary, nothing gets logged

I have an AdditionalDataColumn set up to record ASP.Net Core RequestId. This is a nullable field in the DB and is set to AllowDBNull = true in the column configuration. Some of my logs do not have a RequestId as they are initiated outside of a request scope (configuring/starting the web host for example). These logs are never recorded as Serilog throws the following error:

Unable to write 1 log events to the database due to following error: The given key was not present in the dictionary.

Obviously this is due to RequestId not being present. However, given the nullable config for the AdditionalDataColumn surely this shouldn't fail, it should still insert the rest of the log data, with a null RequestId...

Properties not assigning the correct values to AdditionalDataColumns in logging table

I'm attempting to use the AdditionalDataColumns in ColumnOptions. I've added four new columns to store ProcessName, MachineName, Source, and Version, and have used the following code to configure logging.

var columnOptions = new ColumnOptions
{
    AdditionalDataColumns = new Collection<DataColumn>
    {
        new DataColumn { DataType = typeof(string), ColumnName = "ProcessName" },
        new DataColumn { DataType = typeof(string), ColumnName = "MachineName" },
        new DataColumn { DataType = typeof(string), ColumnName = "Source" },
        new DataColumn { DataType = typeof(string), ColumnName = "Version" },
    },
};
columnOptions.Store.Add(StandardColumn.LogEvent);

Log = new LoggerConfiguration()
    .WriteTo.MSSqlServer(ConnectionString, TableName, columnOptions: columnOptions)
    .CreateLogger();

When I make calls to Log.Write, I get database records in SQL Server, but instead of populating each of the 4 additional columns, it's serializing the whole collection of 4 property values into the MachineName column. The rest (ProcessName, Source, and Version) remain null.

MachineName looks like this:

["DEVWIN10", "MyApp.vshost", "MyApp", "0.9.0.34305"]

It should be "DEVWIN10".

Expose methods/properties to change datatype for Id and Level columns

Hi,

Is there a possibility to provide a way to change datatype of Id column to certain datatypes that supports AutoIncrement such as int, bigint, etc. Also, same with the Level column. It is hard-coded to use byte if storeAsEnum is set to true (e.g. int). Or expose an event/callback to modify the columnoptions before/after calling CreateDataTable()? The reason being is we have an existing legacy log table and we cannot change the schema of that table. I was able to mapped the column names to our existing columns, however during logging sqlbulkcopy is generating an error that columnmapping does not match. This is because the datatype mismatch for Id and Level columns (in our case).

Is Dynamic Log Level Change supported?

Hi,

According to Serilog's documentation, you can change the log level at runtime and that will be the current minimum level during logging. I was using two sinks -> trace and MsSQLServer. When I change the log level at run time, I was able to see the logs in the trace log but not in the MsSQLServer sink. Is that feature supported?

Write EventId to SQL column

I'm using the MSSQLServerCore package and successfully managed to write log messages to my database. Now I want to write the EventId property as well that can be specified during logging (developing in ASP.NET Core MVC so using Microsoft.Extensions.Logging framework). Just by adding the the column name by passing it in the ColumnOptions doesn't work.

Do I have to write a custom enricher for this that fetch the EventId from the "Microsoft LogMessage-object" and attach it the the Log.Context-dictionary (can't recall exact details about that now ;)?

Update:

var columnOptions = new ColumnOptions()
{
	AdditionalDataColumns = new Collection<DataColumn>
	{
		new DataColumn {
			DataType = typeof (int?),
			ColumnName = "EventId",
			AllowDBNull = true
		}
	}
};

CREATE TABLE [dbo].[Log](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[EventId] [int] NULL,
	[Exception] [nvarchar](max) NULL,
	[Level] [nvarchar](20) NOT NULL,
	[Message] [nvarchar](max) NOT NULL,
	[MessageTemplate] [nvarchar](max) NULL,
	[Properties] [nvarchar](max) NULL,
	[Timestamp] [datetime2](7) NOT NULL
)
// Indexes and other omitted for brevity

If I pass the above object to MSSqlServer then logging stops working,

Thanks

not working with "Integrated Security=SSPI;"

the sink wont write when using Integrated Security=SSPI;

Fails silently in trying to write logs to database

When this extension fails to write logs to the SQL Server database, it doesn't throw an exception (which is understandable for a logger), but how do I find out why those logs weren't written?

Support the JSON column in SQL server 2016 or newer

Any plans to add support for the new json column in sqlserver 2016 or newer?

.NET Core support

Hello Team,

I can see that now serilog supports .NET Core platform. Is this package serilog-sinks-mssqlserver pacakge also support .NET Core?

Using MSSQLServer sink with dnx451: Could not load file or assembly Serilog.FullNetFx

I'm trying to get the MSSQLServer sink up and running in the asp.net 5 web application but this is the exeception I'm getting during the logger creation:

An exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll but was not handled in user code
Additional information: Could not load file or assembly 'Serilog.FullNetFx, Version=1.5.0.0, Culture=neutral, PublicKeyToken=24c2f752a8e58a10'
With the code:

Log.Logger = new LoggerConfiguration() .ReadFrom.Configuration(configuration) .CreateLogger();

Please not I'm also using the Serilog.Settings.Configuration
Here are parts of my project.json
"Serilog.Extensions.Logging": "1.0.0-rc1-final-10092", "Serilog.Settings.Configuration": "2.0.0-beta-6", "frameworks": { "dnx451": { "dependencies": { "Serilog.Sinks.MSSqlServer": "3.0.48" } }
Is that a problem in my configuration or it is simply not working with dnx core yet?
Cheers!

Require MaxLength property to be set for DataColumns of type string

The auto create process will throw an error if you have a datacolumn of type string and the max length is not set. Specfically, SelfLog just shows that it blew up during table auto creation but doesn't say why. I had to run a sql trace to see what it was blowing up. I had to run a sql trace to see the actual error:

"Line 2: Length or precision specification 0 is invalid."

Once you set the MaxLength property for the DataColumn the issue no longer happens. Would be nice if MaxLength property was required when column type is of type string.

provide table schema

There should be a parameter that one can provide the table schema. Default is dbo. One should have an option to provide another value, so that the table belongs to this schema.

Handling Transactions

Currently it is impossible to determine or control whether the sink will enlist in an already open transaction scope or not .. and there is no way to control this, since writing the logs happen asynchronously in batches.

I would appreciate adding an option to configure the transaction enlisting behaviour of the used connection?

Source code for 3.0.98?

I do not see any labels or branches for older versions of the source code. Is there a way I can get the 3.0.98 version?

Thanks,
Kurt

Using a custom name for the Id column will ignore AutoIncrement property

In SqlTableCreator if the Id column is not named Id it will not be given the IDENTITY(1,1) property, regardless of whether or not the AutoIncrement property is set.

Automatic table creation?

I'm new to serilog and managed to get it working with the database sink but I can't find where I should set the 'autoCreateSqlTable option to true'?
Here is my Startup.cs

Log.Logger = new LoggerConfiguration()
                .ReadFrom.AppSettings()                
                .CreateLogger();

And my web.config

<add key="serilog:using:MSSqlSever" value="Serilog.Sinks.MSSqlServer" />
    <add key="serilog:write-to:MSSqlServer.connectionString" value="..."/>
    <add key="serilog:write-to:MSSqlServer.tableName" value="Logs"/>

Thanks

error when querying sql

SELECT  top 10
      [Message]
      ,[MessageTemplate]
      ,[Level]
      ,[TimeStamp]
      ,[Exception]
      ,[Properties].value('(//property[@key="SomeProperty"]/node())[1]', 'nvarchar(max)') as Action
  FROM [Logs]
 order by TimeStamp desc

Cannot find either column "Properties" or the user-defined function or aggregate "Properties.value", or the name is ambiguous.

Problem with threads using HostingEnvironment.QueueBackgroundWorkItem

Hi,
I'm developing on Web API 2 project with .NET Full Framework (4.6) hosted on Windows Azure. I'm using HostingEnvironment.QueueBackgroundWorkItem for asyncronous operations.

I'm using the following packages:

<package id="Serilog" version="2.3.0" targetFramework="net46" />
<package id="Serilog.Sinks.MSSqlServer" version="4.2.0" targetFramework="net46" />
<package id="Serilog.Sinks.PeriodicBatching" version="2.1.0" targetFramework="net46" />
<package id="Serilog.Sinks.Trace" version="2.1.0" targetFramework="net46" />

This is the Serilog configuration:

<add key="serilog:minimum-level" value="Warning" />
<add key="serilog:using:Trace" value="Serilog.Sinks.Trace" />
<add key="serilog:write-to:Trace" />
<add key="serilog:using:MSSqlSever" value="Serilog.Sinks.MSSqlServer" />
<add key="serilog:write-to:MSSqlServer.connectionString" value="**REMOVED**" />
<add key="serilog:write-to:MSSqlServer.tableName" value="core.Log" />
<add key="serilog:write-to:MSSqlServer.autoCreateSqlTable" value="false" />

Below one example of my code:

HostingEnvironment.QueueBackgroundWorkItem(cancellationToken => 
{
    Log.Logger.Information("Init");
    Thread.CurrentPrincipal = Principal;
    _helper.MyLongOperation(companyId);
    Log.Logger.Information("End");
});

In Kudu "Process Explorer" I see thread_count raising to several hundreds until the Azure App Service blocks. If I modify the configuration as following:

<add key="serilog:minimum-level" value="Warning" />
<add key="serilog:using:Trace" value="Serilog.Sinks.Trace" />
<add key="serilog:write-to:Trace" />

the thread_count remains stable and App Service continues to work.

Any ideas?

Thanks

Remove dependency on id

Currently, id IDENTITY column is required i Logs table; however, there are scenarios where this column is not needed. As an example if we want to analyze logs we will not use row lookup by id or update row by id. Usually we will scan entire table or some range of values by timestamp, aggregate some values, etc. In that case identity/primary key column is useless. In addition, identity column with primary key might slow downs logs because even a heap table is faster for ingestion compared to the clustered index. Also, primary key will slow down operations if we use clustered column store index, which is perfect for logging due to the high compression.

Having that in mind, could you remove dependency on primary key/clustered index?

Issues with SQL Server Sink .NET framework 4.5.2 & v5.0.0

From @el1jaC (serilog/serilog#1017)

I've installed Serilog version 2.5.0 in my ASP.NET MVC project from nuget package manager, with two sinks:

RollingFile version 3.3.0
MSSqlServer version 5.0.0
Logging on file is ok, but doesn't work on Sql Server, and no error or exception is raised.
I also tried to downgrade Sql Server sink version to 4.2.0 and 4.0.0, but nothing is changed.

To configure logging on db i followed the tutorial on github page project.

Can you help me pls?

Thanks in advance!

4.0.0-beta-102 depends on Serilog (>= 1.4.204 && < 2.0.0)

Should this depend on (>= 2.0.0) instead? I am using it successfully in multiple RTM apps but I have to add the dependency on Serilog 2.0.0 to the project.json files manually to prevent conflict between Serilog.Extensions.Logging and Serilog.Sinks.MSSqlServer. Also, is this still a beta or can it be 'official' yet?

Does serilog-sinks-mssqlserver provide batched events writing?

Hello,
I've tried to figure out myself if mssqlserver logging supports batch writing or not but have found no answer till the fact that Serilog.Sinks.PeriodicBatching is referenced.

Have I to set a particular parameter to enable it or is it so by default?
Thanks

Log entries with new parameters

Hi,

I'm new to Serilog so forgive me if this isn't a bug.

My environment is this...

• Windows 10
• .NET 4.6.1
• SQL Server 2016

My NuGet packages are...

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="Serilog" version="2.3.0" targetFramework="net461" />
  <package id="Serilog.Settings.AppSettings" version="2.0.0" targetFramework="net461" />
  <package id="Serilog.Sinks.MSSqlServer" version="4.2.0" targetFramework="net461" />
  <package id="Serilog.Sinks.PeriodicBatching" version="2.1.0" targetFramework="net461" />
</packages>

My app.config file is...

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="serilog:using:MSSqlSever" value="Serilog.Sinks.MSSqlServer" />
    <add key="serilog:write-to:MSSqlServer.connectionString" value="server=dev-david;database=Serilog;integrated security=true;"/>
    <add key="serilog:write-to:MSSqlServer.tableName" value="Log"/>
    <add key="serilog:write-to:MSSqlServer.autoCreateSqlTable" value="true"/>
    <add key="serilog:minimum-level" value="Verbose" />
  </appSettings>
  <startup> 
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>
</configuration>

I have a console app with this code...

using System;
using Serilog.Debugging;

namespace Serilog.SqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            SelfLog.Enable(msg => { throw new Exception(msg); });

            var log = new LoggerConfiguration()
                .ReadFrom.AppSettings()
                .CreateLogger();

            log.Verbose("Verbose");
            log.Verbose("Verbose {x}", 1);

            Console.ReadLine();
        }
    }
}

When I run that code, it only logs a single row to my database...

If I delete the rows from the database and change the code to this, however...

using System;
using Serilog.Debugging;

namespace Serilog.SqlServer
{
    class Program
    {
        static void Main(string[] args)
        {
            SelfLog.Enable(msg => { throw new Exception(msg); });

            var log = new LoggerConfiguration()
                .ReadFrom.AppSettings()
                .CreateLogger();

            log.Verbose("Verbose {x}", 1);
            log.Verbose("Verbose {x}", 2);

            Console.ReadLine();
        }
    }
}

...it will write two rows to my database.

It seems like templates with parameters don't get logged unless all of them are used "up-front".

Option to auto create index on ColumnOptions.AdditionalDataColumns

Is there a way to automatically create index's for specific additional columns when using the autoCreateSqlTable option?

For example:

var columnOptions = new ColumnOptions
{
	AdditionalDataColumns = new Collection<DataColumn>
	{
		new DataColumn("EventType", typeof(long)) // Is there a way to auto index this column?
	}
};

Log.Logger = new LoggerConfiguration()
	.WriteTo.MSSqlServer(
		connectionString: "MyConnectionString",
		tableName: "MyApplicationLogs",
		autoCreateSqlTable: true,
		restrictedToMinimumLevel: LogEventLevel.Information,
		columnOptions: columnOptions)
	.Enrich.FromLogContext()
	.Enrich.With<EventTypeEnricher>() // custom enricher to add additional property (see above additional column)
	.CreateLogger();

If not, this may be a good feature to add. We will likely auto-create the table once in development, add the indexes manually, and then script the table schema's for a production deploy.

Cannot exclude additional properties on JSON column (NULL exception)

I have excluded XML column and included JSON column, and then I wanted to remove additional properties from JSON column:

            var 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);
            columnOptions.LogEvent.ExcludeAdditionalProperties = true;

This configuration throws an exception and nothing is logged:

System.NullReferenceException occurred
Message: Exception thrown: 'System.NullReferenceException' in Serilog.Sinks.MSSqlServer.dll
Additional information: Object reference not set to an instance of an object.

I have found that ExcludeAdditionalProperties works only with XML column, but there should not be an NULL exception. In the ideal case ExcludeAdditionalProperties should be applied both to XML and JSON column.

FillDataTable() doesn't take the customize column name into consideration

I am using version 4.1.1 of the package and it is throwing an error right now -> ```
Exception while emitting periodic batch from Serilog.Sinks.MSSqlServer.MSSqlServerSink: System.AggregateException: One or more errors occurred. ---> System.ArgumentException: Column 'TimeStamp' does not belong to table XXXX.
at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.set_Item(String columnName, Object value)
at Serilog.Sinks.MSSqlServer.MSSqlServerSink.FillDataTable(IEnumerable1 events) at Serilog.Sinks.MSSqlServer.MSSqlServerSink.<EmitBatchAsync>d__10.MoveNext() --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait() at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.EmitBatch(IEnumerable1 events)
at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.OnTick()
---> (Inner Exception #0) System.ArgumentException: Column 'TimeStamp' does not belong to table XXXX.
at System.Data.DataRow.GetDataColumn(String columnName)
at System.Data.DataRow.set_Item(String columnName, Object value)
at Serilog.Sinks.MSSqlServer.MSSqlServerSink.FillDataTable(IEnumerable`1 events)
at Serilog.Sinks.MSSqlServer.MSSqlServerSink.d__10.MoveNext()<---

The problem is with the FillDataTable method when assigning the value to each datacolumn. Right now, the column names are hard-coded but I see that in the "dev" version, it is already fixed. When will this fix be released? Also, can we configure the datatype for the Id column so that we can change it from int to bigint? Thanks.

Log.CloseAndFlush / Dispose issue

In testing the forked version of this sink for .net core (https://github.com/aaxelm/serilog-sinks-mssqlservercore), it seems like when Log.CloseAndFlush is called, it results in the MssqlServerSink::_eventsTable being disposed while the queue is still being processed / flushed. Once the _eventsTable is disposed, the queue processing now blows up because the _eventsTable.Columns is now 0 instead of > 0. Shouldn't the queue be processed / flushed before disposing of _eventsTable?

 var config = builder.Build();

LoggingConfigurator.ConfigureLogging(config);

Log.Information("USER AUTHENTICATION Success (login = {login}, ipAddress = {ipAddress})", "johnny", "172.17.0.1");

Log.CloseAndFlush();  //Previous log entry encounters internal logging error because of early dispose

Named connection string error

Moved from serilog/serilog#748

@GusBeare wrote:

I am using SImple.Data locally and have this code:

var db = Database.OpenNamedConnection("q2_admin");
//var db = Database.Open();

It works fine in my dev environment where I am using a developer version of SQL server 2012.

However when my colleague runs the code on his box with SQL Express 2012 he gets this error:

[Error] Specified argument was out of the range of valid values.
Parameter name: connectionName

If I switch to using the default connection it works fine.

Is SQL Express supported? Or is this a bug?

Appsettings.json Configuration

I am trying to configure via appsettings.json and it works if I use full connection string. However the documentation says that you can specify the name of the connection string used in connection strings settings, but it doesn't seem to work or I have something wrong.

This works...

"ConnectionStrings": {
"MYDB": "Data Source=XXX;Initial Catalog=XXXX;Integrated Security=True",
},
"Serilog": {
"MinimumLevel": {
"Default": "Verbose",
"Override": {
"Microsoft": "Warning",
"System": "Warning"
}
},
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"restrictedToMinimumLevel": "Verbose",
"connectionString": "Data Source=XXX;Initial Catalog=XXXX;Integrated Security=True",
"tableName": "Logs"
}
}
]
}
}

However based on my understanding, this is supposed to work...
"To use a connection string from the element of your application config file, specify its name as the value of the connection string."

"ConnectionStrings": {
"MYDB": "Data Source=XXX;Initial Catalog=XXXX;Integrated Security=True",
},
"Serilog": {
"MinimumLevel": {
"Default": "Verbose",
"Override": {
"Microsoft": "Warning",
"System": "Warning"
}
},
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"restrictedToMinimumLevel": "Verbose",
"connectionString": "MYDB",
"tableName": "Logs"
}
}
]
}
}

What am I not understanding or is this not supported?

Support automatic table creation on SQL Azure

It looks like support AzureSQL well if we create log table manually. Could we adjust script to create log table to support AzureSQL too?

https://i.gyazo.com/fdd7e79b7dd210b255d163126b85044d.png

TimeStamp ConvertToUtc not working

Hi,

I'm struggling with setting my timestamp column to UTC time. Can you guys see any issue with this code?

private static ColumnOptions GetColumnOptions()
{
    var columnOptions = new ColumnOptions
    {
        Store = new Collection<StandardColumn>(),
        TimeStamp = 
        {
            ConvertToUtc = true,
            ColumnName = "Created"
        },
        AdditionalDataColumns = new Collection<DataColumn>
        {
            new DataColumn
            {
                DataType = Type.GetType("System.DateTime"),
                ColumnName = "Created",
                AllowDBNull = false
            },
            .............
        }
    };
    return columnOptions;
}

Cannot seem to get entries written to my logging table.....

Hi,

My environment is:

• .NET 4.61
• Serilog 1.5.14
• Serilog.Sinks.MSSqlServer 3.0.52
• SQL Server Microsoft SQL Server 2012 - 11.0.5343.0 (X64)

Apologies for the complex example, but I am dropping SQL server logging into an already configured system so need to get it to work as it is already configured.

The current Serilog configuration is working to both Email as well as UDP, so I am pretty confident that the issue is not with the serilog side of things, but rather with my (mis)configuration of the SQL server sink. I scaffold things as follows:

CREATE TABLE [DonoLog] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,  -- use datetime for SQL Server pre-2008
   [Exception] nvarchar(max) NULL,
   [Properties] xml NULL,
   [LogEvent] nvarchar(max) NULL,
   [ApplicationHostName] nvarchar(max) NULL,
   [ApplicationName] nvarchar(max) NULL,
   [ApplicationVersion] nvarchar(max) NULL,
   [LogVersion] nvarchar(max) NULL,
   [DateTimeLocal] datetime NULL,
   [DateTimeUtc] datetime NULL,
   [ExceptionPayload] nvarchar(max) NULL,
   [LevelRange] int NULL,
   [Location] nvarchar(max) NULL,
   [StackTrace] nvarchar(max) NULL,
   [UserDataKind] nvarchar(max) NULL,
   [UserData] nvarchar(max) NULL

   CONSTRAINT [PK_Logs] 
     PRIMARY KEY CLUSTERED ([Id] ASC) 
     WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
           ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
     ON [PRIMARY]

) ON [PRIMARY];

                LoggerConfiguration loggerConfiguration = new LoggerConfiguration()
                    .Enrich.WithProperty("ApplicationHostName", aParams.ApplicationHostName)
                    .Enrich.WithProperty("ApplicationName", aParams.ApplicationName)
                    .Enrich.WithProperty("ApplicationVersion", aParams.ApplicationVersion)
                    .Enrich.WithProperty("LogVersion", aParams.LogVersion)
                    .MinimumLevel.Verbose();

                    ColumnOptions columnOptions = new ColumnOptions
                                                  {
                                                      AdditionalDataColumns = new Collection<DataColumn>
                                                                              {
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "ApplicationHostName"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "ApplicationName"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "ApplicationVersion"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "LogVersion"},
                                                                                  new DataColumn {DataType = typeof(DateTime), ColumnName = "DateTimeLocal"},
                                                                                  new DataColumn {DataType = typeof(DateTime), ColumnName = "DateTimeUtc"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "ExceptionPayload"},
                                                                                  new DataColumn {DataType = typeof(int), ColumnName = "LevelRange"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "Location"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "StackTrace"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "UserDataKind"},
                                                                                  new DataColumn {DataType = typeof(string), ColumnName = "UserData"}
                                                                              }
                                                  };

                    loggerConfiguration.WriteTo.MSSqlServer("Data Source=ozdevsvr003;Initial Catalog=Logging;Integrated Security=True", "DonoLog", LogEventLevel.Verbose, 50, null, null, false,
                        columnOptions);

and perform a write with:

                ILogger ctx = aParams.Logger
                                     .ForContext("DateTimeLocal", aParams.Entry.DateTimeUtc.ToLocalTime())
                                     .ForContext("DateTimeUtc", aParams.Entry.DateTimeUtc)
                                     .ForContext("ExceptionPayload",
                                         JsonConvert.SerializeObject(aParams.Entry.Exception, Formatting.None, new JsonSerializerSettings {ReferenceLoopHandling = ReferenceLoopHandling.Ignore}))
                                     .ForContext("LevelRange", aParams.Entry.LevelRange)
                                     .ForContext("Location", aParams.Entry.Location)
                                     .ForContext("StackTrace", aParams.Entry.StackTrace)
                                     .ForContext("UserDataKind", aParams.Entry.UserDataKind)
                                     .ForContext("UserData", aParams.Entry.UserData);

     //Do the write
    ctx.Verbose(aParams.Entry.Message);

I have SeriLog logging any issues via SelfLog.Out but that reports nothing. When tracing the SQL being sent to SQL Server I see the following SQL:

insert bulk DonoLog ([Message] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [MessageTemplate] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [Level] NVarChar(128) COLLATE SQL_Latin1_General_CP1_CI_AS, [TimeStamp] DateTimeOffset(7), [Exception] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [Properties] Xml, [ApplicationHostName] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [ApplicationName] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [ApplicationVersion] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [LogVersion] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [DateTimeLocal] DateTime, [DateTimeUtc] DateTime, [ExceptionPayload] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [LevelRange] Int, [Location] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [StackTrace] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [UserDataKind] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS, [UserData] NVarChar(max) COLLATE SQL_Latin1_General_CP1_CI_AS)

But nothing appears in the table. So if I am reading this correctly there is potentially no data being sent to SQL Server, or perhaps the Trace Tool does not show the binary data? As I said at the start the other sinks I have configured (email & UDP) do indeed show the logged item.

How do I proceed from here to determine why my data is not being written into the database?

Allow to work with triggers

In MSSqlServerSink => EmitBatchAsync I'd like to have the option of writing to a table that uses triggers.

Instead of just:

using (var copy = new SqlBulkCopy(cn))

I'd like to also be able to call the following overload with the following parameters (probably should be open to any SqlBulkCopyOptions):

using (var copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers, null))

Our use case is that the log is written to a table that replaces the insert with a trigger to encrpyt data and write out encrypted logs to the table.

Since it isn't open for extension my current workaround is having to pull down the entire source to modify the one line.

Custom DB schema names are ignored and "dbo" is always used

If a custom schema name is specified, either using the code or XML configuration, said schema is ignored and "dbo" is used instead.
E.g.

1. Add to the XML configuration.
   -If the autoCreateSqlTable setting is set to "false" then it won't find the table and fail (I've double checked and the db user rights are correct)
   -If autoCreateSqlTable is set to "true" then a table with name "dbo.logging.Logs" is created, and serilog is able to log correctly.

Dictionary item keys are doubly-quoted

See:

• SimplifyScalar is called on the ScalarValue itself rather than its Value, which results in double quotes being added to the item's key:
  https://github.com/serilog/serilog-sinks-mssqlserver/blob/master/src/Serilog.Sinks.MSSqlServer/Sinks/MSSqlServer/XmlPropertyFormatter.cs#L68

Timestamp DateTimeOffset issue

While working with this sink, noticed the following. The Timestamp column not containing the offset, but saving the universal time if the
columnOptions: new ColumnOptions { TimeStamp = { ConvertToUtc = true } }
setting is applied.
In the messages I've used DateTimeOffset.Now to display the actual time.

As you can see in the first three entries, the flag is true and the time is UTC in the timestamp column, but without the offset.
Is there any settings am I missing to save the offset too?

Unable to add LogType to ColumnOptions.Store from config appSettings

Currenlty there is no way to add/remove the Standard Columns from ColumnOption.Store using the config appSettings.
example settings as below:

<add key="serilog:using:MSSqlServer" value="Serilog.Sinks.MSSqlServer"     />
    <add key="serilog:write-to:MSSqlServer" />
    <add key="serilog:write-to:MSSqlServer.connectionString" value="conncetionstring" />
    <add key="serilog:write-to:MSSqlServer.tableName" value="ApplicationSerilog" />
    <add key="serilog:write-to:MSSqlServer.autoCreateSqlTable" value="true" />
    **<add key="serilog:write-to:MSSqlServer.columnOptions.Store" value="[0,1,2,3,4,5,6]" />**

Is there any workaround?

Error message : The given key was not present in the dictionary when adding new column

Hi,
I migrated this issue to https://github.com/aaxelm/serilog-sinks-mssqlservercore/issues
Regards

Potential Bug: Queue not flushed to DB on Dispose

I think this is probably related to this issue: serilog/serilog-sinks-periodicbatching#4 since the MS SQL Server Sink uses Periodic Batching. If there are any log entries in the queue when the sink is disposed, it should call CloseAndFlush which should write the remaining entries in the queue to the DB table, but it does not. I would rather not do this, but as a work around I have a Thread.Sleep(5000) before the application closes just so I now everything gets written.

Not sure if I should have posted this issue for the PeriodicBatching sink or not but I decided just to stick with this since this is where I experiencing the issue.

Nuget Project Url is incorrect

If you click on the Project Information link in NuGet, it provides the following link, which is missing the .com in github.com.

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

Level.StoreAsEnum is broken

In the recent changes to support custom column names, it seems the support for storing Level as an enum was broken. (See commit cdd7999 changes to /src/Serilog.Sinks.MSSqlServer/Sinks/MSSqlServer/MSSqlServerSink.cs)

I believe this can be fixed by changing the value of the MaxLength property from 0 to -1 when StoreAsEnum is true, as a value of 0 causes ADO.Net to barf with an exception about non-string datatypes and non-negative MaxLength not being supported. (How's that for triple-negation!)

The functionality works in version 4.0.0 of the Nuget package, but not in version 4.1.0

Thanks in advance!

Column Option 'ExcludeAdditionalProperties' for LogEvent is misleading

Use the columnOptions.LogEvent.ExcludeAdditionalProperties parameter to exclude redundant properties from the JSON. This is analogue to excluding redundant items from XML in the Properties column.

When turning this option on, I was expecting to not see the following columns in my JSON:

• Timestamp
• Level
• MessageTemplate

Is there another way of removing these?

serilog write-to MSSqlServer not working unless autoCreateSqlTable set to true

Please excuse me copying my SO post almost verbatim...

I'm trying to get Serilog (2.4.0) to write to an SQL Server (2016) log table using WriteTo.MSSqlServer (4.2.0) and cannot get any success unless the autoCreateSqlTable parameter is set to true. I've tried this using both code and XML/AppSettings and the results are the same.

If I set autoCreateSqlTable to true then the table is created (if it doesn't already exist) and the error is logged - so it can't be a permission issue for the user. If I set autoCreateSqlTable to false or omit it, no message is logged even though the table is the one it just created in a previous run.

The problem is that, in production, the user won't have the rights to create the table so I need to to be able to use a pre-existent one.

I have finally managed to find that an error is being raised when autoCreateSqlTable is false or omitted...

2017-03-01T08:37:08.9335745Z Exception while emitting periodic batch from Serilog.Sinks.MSSqlServer.MSSqlServerSink: System.Threading.ThreadAbortException: Thread was being aborted. at System.Threading.Monitor.ObjWait(Boolean exitContext, Int32 millisecondsTimeout, Object obj) at System.Threading.Monitor.Wait(Object obj, Int32 millisecondsTimeout, Boolean exitContext) at System.Threading.Monitor.Wait(Object obj, Int32 millisecondsTimeout) at System.Threading.ManualResetEventSlim.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.SpinThenBlockingWait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.InternalWait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.EmitBatch(IEnumerable1 events)
at Serilog.Sinks.PeriodicBatching.PeriodicBatchingSink.OnTick()`

Any thoughts?

Message value of SQL log record doesn't contain the message template

Given this setup:

var columnOptions = new ColumnOptions
	{
		AdditionalDataColumns = new Collection<DataColumn>
		{
			new DataColumn {DataType = typeof(string), ColumnName = "Entrypoint"},
			new DataColumn {DataType = typeof(string), ColumnName = "Username"}
		}
	};

var logger = new LoggerConfiguration()
	.Enrich.WithProperty("Entrypoint", "MyEntryPoint")
	.Enrich.With(new UsernameEnricher(context))
	.MinimumLevel.Debug()
	.WriteTo.Trace(outputTemplate: outputTemplate)
	.WriteTo.MSSqlServer(myConnectionString, myTableName, columnOptions: columnOptions)
	.CreateLogger();

logging an information record via:

var messageTemplate = "Your message was {Message}";
_logger.Info(messageTemplate, "testing");

inserts a SQL record with Message = 'testing'

If I change the setup, ignoring columnOptions:

var logger = new LoggerConfiguration()
	.Enrich.WithProperty("Entrypoint", "MyEntryPoint")
	.Enrich.With(new UsernameEnricher(context))
	.MinimumLevel.Debug()
	.WriteTo.Trace(outputTemplate: outputTemplate) //write all msgs to trace
	.WriteTo.MSSqlServer(myConnectionString, myTableName)
	.CreateLogger();

that same logging statement inserts a SQL record with Message = 'Your message was "testing"'. This is what I expect.

Any ideas why it behaves differently with columnOptions?

Thanks in advance!

Does this sink work with SQL Azure?

• Serilog v2.4.0
• Serilog.Sinks.MSSqlServer v5.0.0
• Serilog.Sinks.PeriodicBatching v2.1.0

Can this sink work with Microsoft SQL Azure? My servers version is the following: (Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 30 2017 01:30:03 Copyright (C) 2016 Microsoft Corporation. All rights reserved.)?

I am getting no errors. Table has been manually created in my Azure Database.

Here is a quick sample code I am using:

class Program
    {
        static void Main(string[] args)
        {
            const string connectionString =
                "Server=tcp:RandomIdentifier.database.windows.net,1433;Database=OurDatabaseName;User ID=PrivateUser@RandomIdentifier;Password=Hidden;Encrypt=True;TrustServerCertificate=False;Connection Timeout=90;";

            var log = new LoggerConfiguration()
                .MinimumLevel.Debug()
                .WriteTo.MSSqlServer(connectionString, "ServiceLog", LogEventLevel.Debug, 1, TimeSpan.FromSeconds(1))
                .CreateLogger();

            var test = new
            {
                UserId = 1,
                UserName = "Bob"
            };

            log.Information("Testing");
            log.Information("{@Test}", test);
        }
    }

Serilog MSSQLSERVER got an threading issue

Hi, I am using this nuget to write log to my database table and for demo purposed I created the console app. In this console app, I am trying to insert three logs, in the following order

1. Infromation Log
2. Information Log
3. Error Log
   to my database but it always inserts two 1 and 3 always missing the second one. But when I put Thread.Sleep(5000) at the end of my program then it is inserting all the three logs which make me thinks that serilog internally not waiting for tasks to complete. My program code is below

class Program
    {
        public static IConfigurationRoot Configuration { get; private set; }
        public static IServiceProvider ServiceProvider { get; private set; }

        //https://github.com/serilog/serilog-sinks-mssqlserver
        static void Main(string[] args)
        {
            var mm = new LoggerBuilderClass();

            var taskList = new List<Action> { mm.SecondMessage, mm.FirstMessage, mm.ExceptionMessage };

            taskList.ForEach(x => x.Invoke());
        }

        public class LoggerBuilderClass
        {
            public LoggerBuilderClass()
            {
                var builder = new ConfigurationBuilder()
                               .SetBasePath(Directory.GetCurrentDirectory())
                               .AddJsonFile("appsettings.json");
                Configuration = builder.Build();
                Console.WriteLine(Configuration["ConnectionStrings:DefaultConnection"]);


                var connectionString = Configuration["ConnectionStrings:DefaultConnection"].ToString();  // or the name of a connection string in your .config file
                var tableName = "Logs";
                var columnOptions = new ColumnOptions
                {
                    AdditionalDataColumns = new Collection<DataColumn>
                    {
                        new DataColumn{DataType = typeof(string), ColumnName="ScreenName"},
                        new DataColumn{DataType = typeof(string), ColumnName="TabName"}
                    }
                };


                //https://andrewlock.net/using-dependency-injection-in-a-net-core-console-application/
                //setup our DI
                ServiceProvider = new ServiceCollection()
                    .AddOptions()
                    //.Configure<ViperFreightOptions>(Configuration.GetSection("ViperFreightOptions"))
                    //.Configure<RunProcessOptions>(Configuration.GetSection("RunProcessOptions"))
                    .AddLogging()
                    .AddDbContext<MyDbContext>(o => o.UseSqlServer(Configuration["ConnectionStrings:DefaultConnection"]))
                    .AddSingleton<Serilog.ILogger>(o =>
                     {
                         return new LoggerConfiguration()
                                 .WriteTo.Logger(x => x
                                 .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions, period: TimeSpan.FromSeconds(5))
                                 .Filter.ByIncludingOnly(e => e.Level == LogEventLevel.Error)
                                 .Filter.With())

                                  .WriteTo.Logger(x => x
                                 .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions, period: TimeSpan.FromSeconds(5))
                                 .Filter.ByIncludingOnly(e => e.Level == LogEventLevel.Debug)
                                 .Filter.With())

                                 .WriteTo.Logger(x => x
                                 .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions, period: TimeSpan.FromSeconds(5))
                                 .Filter.ByIncludingOnly(e => e.Level == LogEventLevel.Information)
                                 .Filter.With())
                                 .CreateLogger();
                     })
                    .BuildServiceProvider();




                //log = new LoggerConfiguration()
                //                .WriteTo.Logger(x => x
                //               .MinimumLevel.Verbose()
                //               .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions, period: TimeSpan.FromSeconds(1))
                //               .Enrich.FromLogContext()
                //               .Filter.ByIncludingOnly(e => e.Level == LogEventLevel.Error))

                //               .WriteTo.Logger(x => x
                //               .MinimumLevel.Verbose()
                //               .Enrich.FromLogContext()
                //               .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOptions, period: TimeSpan.FromSeconds(1))
                //               .Filter.ByIncludingOnly(e => e.Level == LogEventLevel.Information))
                //               .CreateLogger();


            }
            public void FirstMessage()
            {
                var exampleUser = new { Id = 1, Name = "Adam", Created = DateTime.Now };
                ServiceProvider.GetService<Serilog.ILogger>().Write(LogEventLevel.Information, "Created Created {@Data}, {@ScreenName}, {@TabName},{@Exception}", new object[] { exampleUser, "Program", "Connection Builder", null });
            }

            public void SecondMessage()
            {
                var exampleUser = new { Id = 2, Name = "Adam", Created = DateTime.Now };
                ServiceProvider.GetService<Serilog.ILogger>().Write(LogEventLevel.Debug, "Created Created {@Data}, {@ScreenName}, {@TabName},{@Exception}",
                new object[] { exampleUser, "Program", "Connection Builder", null });
            }
            public void ExceptionMessage()
            {
                if (1 == 1)
                {
                    try
                    {

                        throw new ArgumentException("Test Argument exception");
                    }
                    catch (Exception ex)
                    {
                        //log.Information("Created {@Exception}", new object[] { ex });
                        ServiceProvider.GetService<Serilog.ILogger>().Write(LogEventLevel.Error, "Created {@Data}, {@ScreenName}, {@TabName},{@Exception}", new object[] { new { Id = 3 }, "Program", "Connection Builder", ex });

                    }
                }
            }
        }
    }