Sign in
 
HomeWindows Server 2012Windows Server 2008 R2Windows Server 2003LibraryForums
Windows Server TechCenter >
> 
> 
using log4net to log to databse

Answered using log4net to log to databse

  • Wednesday, May 19, 2010 5:36 AM
     
     
    Sign In to Vote
    0

    Hi,

    I am trying to use log4net to log every thing to a mssql 2008 database. Here is what i have

    DatabaseLogging.ps1 file:

    $ErrorActionPreference="Stop"
    [void][Reflection.Assembly]::LoadFile("C:\Log4net\log4net.dll")

    [log4net.LogManager]::ResetConfiguration()

    $configFileInfo = gi "C:\Log4net\log4net.xml"

    [log4net.GlobalContext]::Properties["timestamp"] = Get-Date -Format "yyyy-MMM-dd_hh-mm-ss"

    [log4net.Config.XmlConfigurator]::ConfigureAndWatch($configFileInfo)

    $logger = [log4net.LogManager]::GetLogger("Test")

    $logger.info("Build=$latestFullBuild")

    $logger = [log4net.LogManager]::GetLogger("PowerShell")

    try
    {
        $logger.Info("Started the main script...$i");
        $logger.Debug("This is the debugging information");
        $logger.Warn("The warning is that if you try to create a file with the same name it will fail");

        New-Item -ItemType file freespace.txt
        New-Item -ItemType file freespace.txt        
    }
    catch
    {                 
        $logger.Error("I told ya!  You have an error");
        $logger.Fatal($_.Exception.message);
        $logger.Fatal($_.Exception.Message,$_.Exception);
    }
    finally
    {
        $logger.Info("Exiting Test-Excp() method")
    }

    Log4net.xml File

    <log4net>
      <appender name="AdoNetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
        <bufferSize value="1" />
        <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
        <connectionString value="data source=.;initial catalog=DBLog;integrated security=false;persist security info=True;User ID=sa;Password=password" />
        <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)" />
        - <parameter>
          <parameterName value="@log_date" />
          <dbType value="DateTime" />
          <layout type="log4net.Layout.PatternLayout" value="%date{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}" />
        </parameter>
        - <parameter>
          <parameterName value="@thread" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout" value="%thread" />
        </parameter>
        - <parameter>
          <parameterName value="@log_level" />
          <dbType value="String" />
          <size value="50" />
          <layout type="log4net.Layout.PatternLayout" value="%level" />
        </parameter>
        - <parameter>
          <parameterName value="@logger" />
          <dbType value="String" />
          <size value="255" />
          <layout type="log4net.Layout.PatternLayout" value="%logger" />
        </parameter>
        - <parameter>
          <parameterName value="@message" />
          <dbType value="String" />
          <size value="4000" />
          <layout type="log4net.Layout.PatternLayout" value="%message" />
        </parameter>
      </appender>

      <root>
        <level value="All" />
        <appender-ref ref="AdoNetAppender_SqlServer" />   
      </root>
      <logger name="Test" additivity="true">
      </logger>
    </log4net>

    So now when i execute the ps1 file it shows no error but when i check the database there is nothing in it. I really dont understand what went wrong but nothing is logged to the database. I tried the "log4net.Appender.FileAppender " it works fine, logs eveything.

    Can someone please help me out with this ?

    Thanks in advance.

     

All Replies

  • Wednesday, May 19, 2010 9:10 AM
     
     
    Sign In to Vote
    0

    Hi, I'm not an expert in log4net... reading the situation it felt like perhaps the AdoNetAppender might be batching up log messages. Rather than write each individual log to the database (a complete waste of resources) it might group them up and send them in batches; a quick google and yip this is what happens. The default is 100 log messages, called the buffer size. So your database isn't getting updated because there is never 100 log messages.

    http://logging.apache.org/log4net/release/config-examples.html#HC-11150143

    There must be a method to call on the logger to flush the buffer.

    http://stackoverflow.com/questions/2045935/is-there-anyway-to-programmably-flush-the-buffer-in-log4net

     

     

     
  • Wednesday, May 19, 2010 10:34 AM
     
     
    Sign In to Vote
    0

    But I have set the Buffer to 1 in the xml file. Doesn't it mean that it will write to the database as the buffer is already 1 ?

    <bufferSize value="1" />
     

     

     
  • Wednesday, May 19, 2010 11:28 AM
     
     
    Sign In to Vote
    0

    Emmm that's a DUH for me!!!Did you edit your post?  :)

    I'm not sure then; I would still flush the buffer just to be sure... the only other thing I would look at is the connection to the database.

     

    don't know what else to suggest, sorry! If I have time later today I will recreate the problem and see if I can find a solution.

     

     

     

     
  • Thursday, May 20, 2010 9:29 AM
     
     
    Sign In to Vote
    0

    Hi again, gave it a go but like you itworked for file\console but not database. The interesting thing I noticed was when running the script through PowerGUI it caused no exception but running the script on the command line I get an exception.

     

    log4net:ERROR XmlHierarchyConfigurator: Could not create Appender [AdoNetAppender_Access] of type [log4net.Appender.AdoNetAppe
    nder]. Reported error follows.
    System.ArgumentNullException: CreateConverterInstance cannot create instance, converterType is null
    Parameter name: converterType
       at log4net.Util.TypeConverters.ConverterRegistry.CreateConverterInstance(Type converterType)
       at log4net.Util.TypeConverters.ConverterRegistry.GetConverterFromAttribute(Type destinationType)
       at log4net.Util.TypeConverters.ConverterRegistry.GetConvertFrom(Type destinationType)
       at log4net.Util.OptionConverter.CanConvertTypeTo(Type sourceType, Type targetType)
       at log4net.Repository.Hierarchy.XmlHierarchyConfigurator.CreateObjectFromXml(XmlElement element, Type defaultTargetType, Ty
    pe typeConstraint)
       at log4net.Repository.Hierarchy.XmlHierarchyConfigurator.SetParameter(XmlElement element, Object target)
       at log4net.Repository.Hierarchy.XmlHierarchyConfigurator.CreateObjectFromXml(XmlElement element, Type defaultTargetType, Ty
    pe typeConstraint)
       at log4net.Repository.Hierarchy.XmlHierarchyConfigurator.SetParameter(XmlElement element, Object target)
       at log4net.Repository.Hierarchy.XmlHierarchyConfigurator.ParseAppender(XmlElement appenderElement)
    log4net:ERROR XmlHierarchyConfigurator: Appender named [AdoNetAppender_Access] not found.

     

    Odd... so I changed the configuration file trying to fix it; no luck... so I then decided to manually create and configure the logger and something doesn't add up. Here is the code....

    $appender = New-Object log4net.Appender.AdoNetAppender
    $appender.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='log4net.mdb';User Id=;Password=;"
    $appender.CommandText = "INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)"

    $log_date = New-Object log4net.Appender.AdoNetAppenderParameter
    $log_date.ParameterName = "@log_date"
    $log_date.DbType = "String"
    $log_date.Size = "250"
    $log_date.Layout = New-Object log4net.Layout.RawPropertyLayout
    $log_date.Layout.Key = "%date"

    $appender.AddParameter($log_date)

    $logger = [log4net.LogManager]::GetLogger("PowerShell")
    $logger.Logger.AddAppender($appender)

     

    This runs with no errors but still doesn't work... Look at the $log_date.Layout and how it's a RawPropertyLayout object but in the configuration file it's a log4net.Layout.PatternLayout object.... problem is the log4net.Layout.PatternLayout is not a valid type for $log_date.Layout.

    That's where I stopped. Maybe this information will help.

     
  • Friday, May 21, 2010 11:27 AM
     
     
    Sign In to Vote
    0

    I changed the config file a bit now here it is,

      <!-- This section contains the log4net configuration settings -->
      <log4net>   
        <!-- Example of how to configure the AdoNetAppender to connect to MS SQL Server -->
        <appender name="ADONetAppender_SqlServer" type="log4net.Appender.AdoNetAppender">
          <bufferSize value="1" />
          <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
          <connectionString value="data source=MySQLServer;initial catalog=test;integrated security=false;persist security info=True;User ID=sa;Password=sa" />
          <!--<commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)" />-->
          <commandText value="INSERT INTO Log ([Date]) VALUES (@log_date)" />
          <parameter>
            <parameterName value="@log_date" />
            <dbType value="DateTime" />
            <layout type="log4net.Layout.RawTimeStampLayout" />        
          </parameter>
          <!--<parameter>
            <parameterName value="@thread" />
            <dbType value="String" />
            <size value="255" />
            <layout type="log4net.Layout.PatternLayout">
              <conversionPattern value="%thread" />
            </layout>
          </parameter>
          <parameter>
            <parameterName value="@log_level" />
            <dbType value="String" />
            <size value="50" />
            <layout type="log4net.Layout.PatternLayout">
              <conversionPattern value="%level" />
            </layout>
          </parameter>
          <parameter>
            <parameterName value="@logger" />
            <dbType value="String" />
            <size value="255" />
            <layout type="log4net.Layout.PatternLayout">
              <conversionPattern value="%logger" />
            </layout>
          </parameter>
          <parameter>
            <parameterName value="@message" />
            <dbType value="String" />
            <size value="4000" />
            <layout type="log4net.Layout.PatternLayout">
              <conversionPattern value="%message" />
            </layout>
          </parameter>-->
        </appender>
        <!-- Setup the root category, add the appenders and set the default level -->
        <root>
          <level value="ALL" />
           <appender-ref ref="ADONetAppender_SqlServer" />      
        </root>
        <!-- Specify the level for some specific categories -->
        <logger name="PSRS">
          <level value="ALL" />
          <appender-ref ref="ADONetAppender_SqlServer" />
        </logger>
      </log4net>

    This does log ony the date information into the database. But once i include any other parameter i get the same error on the powershell console as you got. I think it has got some thing to do with the datatypes we are using for the parameters. By the way here is my table

    CREATE TABLE [dbo].[Log](

          [Id] [int] IDENTITY(1,1) NOT NULL,

          [Date] [datetime] NOT NULL,

          [Thread] [varchar](255) NOT NULL,

          [Machine] [varchar](255) NULL,

          [Level] [varchar](50) NOT NULL,

          [Logger] [varchar](255) NOT NULL,

          [Message] [varchar](4000) NOT NULL,

          [Exception] [varchar](2000) NULL

    ) ON [PRIMARY]

    The date thing works fine thats what surprises me !

     
  • Friday, May 21, 2010 12:03 PM
     
     
    Sign In to Vote
    0

    Hi, I believe the problem lies in the Parameter layout type.

    See when the configuration is done with code then the config above would be like this ...

    $log_date = New-Object log4net.Appender.AdoNetAppenderParameter
    $log_date.ParameterName = "@log_date"
    $log_date.DbType = "String"
    $log_date.Size = "250"
    $log_date.Layout = New-Object log4net.Layout.PatternLayout

    But that causes an error...

    Exception setting "Layout": "Cannot convert the "log4net.Layout.PatternLayout" value of type "log4net.Layout.PatternLayout" to type "log4net.Layout.IRawLayout"."

    The log4net.Layout.PatternLayout isn't a valid type for the parameter layout.

     

    This runs though and I wonder if this is how the configuration should be....

    $log_date = New-Object log4net.Appender.AdoNetAppenderParameter
    $log_date.ParameterName = "@log_date"
    $log_date.DbType = "String"
    $log_date.Size = "250"
    $log_date.Layout = New-Object log4net.Layout.RawPropertyLayout
    $log_date.Layout.Key = "%date"

     

    When I ran the code I never got an error but the database wasn't updated however I never set the date layout type correctly.

     

     

     

     
  • Wednesday, May 26, 2010 9:31 AM
     
     
    Sign In to Vote
    0

    Hi Derek,

    Here is what i have now,

    $ErrorActionPreference="Stop"

    # load the log4net library
    [void][Reflection.Assembly]::LoadFile("C:\Log4netTest\log4net.dll")

    $appender = New-Object log4net.Appender.AdoNetAppender
    $appender.Name = "ADONetAppender_SqlServer"
    $appender.BufferSize=1
    $appender.ConnectionType = "System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
    $appender.ConnectionString = "data source=[SQLServerName];initial catalog=[DatabaseName];integrated security=false;persist security info=True;User ID=sa;Password=[Password]"
    $appender.CommandText = "INSERT INTO Log ([Date]) VALUES (@log_date)"

    $log_date = New-Object log4net.Appender.AdoNetAppenderParameter
    $log_date.ParameterName = "@log_date"
    $log_date.DbType = "DateTime"
    $log_date.Layout = New-Object log4net.Layout.RawTimeStampLayout


    $appender.AddParameter($log_date)

    [log4net.ILog] $logger = [log4net.LogManager]::GetLogger("MyLogs")
    [log4net.Repository.Hierarchy.Logger] $l =[log4net.Repository.Hierarchy.Logger]$logger.Logger
    $l.Level = $l.Hierarchy.LevelMap["All"]

    $logger.Logger.AddAppender($appender)

    $logger.info("Started")
    try
    {
        $logger.info("Trying to create a file in the current directory ...")
        New-Item -ItemType file test.txt
        $logger.info("Trying to create the same file again in the current directory ...")
        New-Item -ItemType file test.txt
    }
    catch
    {
        $logger.Error("You have an error")
        $logger.Fatal($_.Exception.message)
        $logger.Fatal($_.Exception.Message,$_.Exception)

    }
    finally
    {
        $logger.Info("Ended")
    }

    If you run it from a powershell console it works perfectly fine. and adds all the date logs into the database. What i did is i changed the column types to allow null values and one thing is that if we run it from ISE or any other ediror for that matterit doesnt log any thing to database. it has to be run from the console.

    So now when i try to add the rest of the parameters i.e.

    $log_level = New-Object log4net.Appender.AdoNetAppenderParameter
    $log_level.ParameterName = "@log_level"
    $log_level.DbType = "String"
    $log_level.Layout = New-Object log4net.Layout.PatternLayout
    $log_level.conversionPattern = "%level"

    i get the following error

    Exception setting "Layout": "Cannot convert the "log4net.Layout.PatternLayout" value of type "log4net.Layout.PatternLayout" to type "log4net.Layout.IRawLayout"."
    At C:\Log4netTest\Log4NetTest.ps1:21 char:12
    + $log_level. <<<< Layout = New-Object log4net.Layout.PatternLayout
        + CategoryInfo          : InvalidOperation: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : PropertyAssignmentException

    Is there a work arround for this ?

     
  • Friday, May 28, 2010 12:51 PM
     
     Answered
    Sign In to Vote
    0

    Finally the answer :)

    $ErrorActionPreference = "Stop"
    # load the log4net library
    [void][Reflection.Assembly]::LoadFile("C:\MyLogTest\log4net.dll")

    function ConfigureLog4Net([string] $LOGGING_CONNECTIONSTRING)
    {
        $sqlAppender = New-Object log4net.Appender.AdoNetAppender   
        $sqlAppender.ConnectionType = "System.Data.SqlClient.SqlConnection, System.Data,Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
        $sqlAppender.ConnectionString = $LOGGING_CONNECTIONSTRING
        $sqlAppender.CommandText = "INSERT INTO MyLogs([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES(@log_date, @thread, @log_level, @logger, @message,@exception)"

        $param1 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param1.ParameterName = "@log_date"
        $param1.Layout = New-Object log4net.Layout.RawTimeStampLayout
        $param1.DbType = "DateTime"
        $sqlAppender.AddParameter($param1)

        $param2 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param2.ParameterName = "@log_level"
        $parm2Layout=New-Object log4net.Layout.PatternLayout("%level")
        $param2.Layout = New-Object log4net.Layout.Layout2RawLayoutAdapter($parm2Layout)
        $param2.DbType = "String"
        $param2.Size = 50
        $sqlAppender.AddParameter($param2)

        $param3 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param3.ParameterName = "@thread"
        $param3Layout= New-Object log4net.Layout.PatternLayout("%thread")
        $param3.Layout = New-Object log4net.Layout.Layout2RawLayoutAdapter($param3Layout)
        $param3.DbType = "String"
        $param3.Size = 255
        $sqlAppender.AddParameter($param3)    

        $param4 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param4.ParameterName = "@logger"
        $param4Layout = New-Object log4net.Layout.PatternLayout("%logger")
        $param4.Layout = New-Object log4net.Layout.Layout2RawLayoutAdapter($param4Layout)
        $param4.DbType = "String"
        $param4.Size = 255
        $sqlAppender.AddParameter($param4)

        $param5 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param5.ParameterName = "@message"
        $param5.DbType = "String"
        $param5Layout = New-Object log4net.Layout.PatternLayout("%message")
        $param5.Layout = New-Object log4net.Layout.Layout2RawLayoutAdapter($param5Layout)
        $param5.Size = 4000
        $sqlAppender.AddParameter($param5)

        $param6 = New-Object log4net.Appender.AdoNetAppenderParameter
        $param6.ParameterName = "@exception"
        $param6.DbType = "String"
        $param6Layout = New-Object log4net.Layout.ExceptionLayout
        $param6.Layout = New-Object log4net.Layout.Layout2RawLayoutAdapter($param6Layout)
        $param6.Size = 4000
        $sqlAppender.AddParameter($param6)

        $sqlAppender.BufferSize = 1
        $sqlAppender.ActivateOptions()

        [log4net.Config.BasicConfigurator]::Configure($sqlAppender)
    }
       
    $constr="data source=[SQLServer];initial catalog=[Database];integrated security=false;persist security info=True;User ID=sa;Password=[Password]"
    ConfigureLog4Net "$constr"
    [log4net.ILog] $logger = [log4net.LogManager]::GetLogger("MyLogs")
    [log4net.Repository.Hierarchy.Logger] $l =[log4net.Repository.Hierarchy.Logger]$logger.Logger
    $l.Level = $l.Hierarchy.LevelMap["All"]
    $logger.Info("Info")
    $logger.Debug("Debug")
    $logger.Error("Error")
    $logger.Warn("Warn")
    $logger.Fatal("Fatal")

    Thanks to http://www.williamkent.net/archive/2007/09/04/using-log4net-inside-of-sharepoint.aspx and

    http://www.schittkowski.de/?q=node/25

    The only thing remaining here is why does the pattern layout fail when in a config file. In the code above we can see that we have to use the adapter to convert the layout to appropriate format.

    Really wondering howto do this in the xml file.

    Thank you guys for your concerns

    • Marked As Answer by SachinNair Friday, May 28, 2010 12:51 PM
    •  
     
  • Friday, May 28, 2010 6:01 PM
     
     
    Sign In to Vote
    0

    Ahhaaa nice one !!!!!thanks for posting back the solution.

    if I get some time next week I'll give it a go. I'd really like to see this working from the config file !!

    Now that your awesome persistence has got it working it shouldn't be too bad, famous last words, reverse engineering the code back to the config file.