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.