RamblingRoss
The blog of Ross Fruen, a .NET consultant

Add log4net files to a SQL database

PowerShell provides the ability to easily import log4net files into a SQL database for analysis.

Step one

Create an appropriate table to store the log entries, e.g.

CREATE TABLE [dbo].[Entries](
[LogEntry] [int] IDENTITY(1,1) NOT NULL,
[LogFile] [varchar](max) NULL,
[LogTime] [datetime] NULL,
[LogThread] [varchar](50) NULL,
[LogLevel] [varchar](10) NULL,
[LogMessage] [nvarchar](max) NULL
) ON [PRIMARY]

Step two

Then run the following PowerShell script to populate the table:

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "data source=ramblingross;initial catalog=Log;integrated security=sspi"
$Connection.Open()

$Command = New-Object System.Data.SQLClient.SQLCommand

$Command.Connection = $Connection

$Command.CommandText = "Insert INTO Entries(LogFile, LogTime, LogThread, LogLevel, LogMessage) Values (@LogFile, @LogTime, @LogThread, @LogLevel, @LogMessage)"

$Command.Parameters.Add("@LogFile",[System.Data.SqlDbType]"varchar", 4000)
$Command.Parameters.Add("@LogTime", [System.Data.SqlDbType]"DateTime")
$Command.Parameters.Add("@LogThread",[System.Data.SqlDbType]"varchar", 50)
$Command.Parameters.Add("@LogLevel",[System.Data.SqlDbType]"varchar", 10)
$Command.Parameters.Add("@LogMessage",[System.Data.SqlDbType]"nvarchar", 4000)

foreach($item in Get-ChildItem ".") {
[RegEx]::Matches([System.IO.File]::ReadAllText($item.FullName), "(?m)(?<Date>\d{4}-\d{2}-\d{2})\s(?<Time>\d{2}:\d{2}:\d{2},\d{3})\s(?<Thread>\^\*\])\s(?<Level>[^\s]*)\s(?<Message>(?:[\s\S](?!^\d\d\d\d-\d\d-\d\d))+)") &#124; % {
$Command.Parameters["@LogFile"].Value = $item.Name
$Command.Parameters["@LogTime"].Value = "{0}T{1}" -f $_.Groups["Date"].Value, $_.Groups["Time"].Value.Replace(',', '.')
$Command.Parameters["@LogThread"].Value = $_.Groups["Thread"].Value
$Command.Parameters["@LogLevel"].Value = $_.Groups["Level"].Value
$Command.Parameters["@LogMessage"].Value = $_.Groups["Message"].Value
$Command.ExecuteNonQuery() &#124; out-null
}
}

$Connection.Close()

Note, the connection string should be changed to match the database where the table in step one was created.

Comments

  1. Thank you, i just change de ConnectionString and it was ok

    - Written on 15th December 2011 by sentimancho

Add a comment

If you want your comment to appear on this page please complete the form below. Your name and email address are optional, although the latter will be required if you want a response. Your email address will not appear against your comment and will only be used to correspond with yourself (where appropriate).

Thanks!

Thank you for submitting your comment, it will appear here after moderation is complete.

Sorry

There was a problem sending your comment, please try again.