' Kiwi Syslog Daemon Database Bulk Insert from Log file script
'=============================================================
'
' FILE: KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.txt
' CREATED: 2008-07-23 - Kiwi Enterprises
'
' DESCRIPTION:
' This script imports all log files from a specified log file
' location that match a given file mask.  Matching log files 
' will be imported according to the field and line delimitors 
' specified (tab and newline by default).
' 
' Imported log files are optionally deleted upon successful 
' importation.  See [Delete_after_import] setting (4).
' 
' RESTRICTIONS: 
' Currently only supports MySQL and MSSQL databases.
'
' INFO: http://www.kiwisyslog.com/kb/how-to:-script-a-bulk-insert-from-a-kiwi-syslog-daemon-log-file-to-database/
'
'-------------------------------------------------------------

' Edit the following 4 sections according to your requirements.

'-------------------------------------------------------------
' (1) Set log file location, file mask and file format here:


Log_FileLocation = "\\server\share\archived-logs"
Log_FileMask = "*.txt, *.log"
Log_FieldTerminator = "\t"
Log_LineTerminator = "\n"


'-------------------------------------------------------------
' (2) Set database connection string, and database table here:  
'     For more information on which connection string to use, please see: http://www.connectionstrings.com


DB_ConnectionString = "Provider=sqloledb; Data Source=localhost; Initial Catalog=kiwisyslog; Integrated Security=SSPI;"
DB_TableName = "Syslogd"


'-------------------------------------------------------------
' (3) Specify database type:
'     0 = MSSQL
'     1 = MySQL


DB_Type = 0


'-------------------------------------------------------------
' (4) Delete file(s) after successful import
'     true/false


Delete_after_import = true

	

'=========== Do not edit beyond this point ==========='

Set oDBConn = CreateObject("ADODB.Connection")
oDBConn.Open(DB_ConnectionString)

Set oRegEx = CreateObject("VBScript.RegExp")
oRegEx.Global = true
oRegEx.IgnoreCase = true
oRegEx.Pattern = RegExFileMask(Log_FileMask)

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oImportFolder = oFSO.GetFolder(Log_FileLocation)
For Each oImportFile In oImportFolder.Files
	If oRegEx.Test(oImportFile.Name) Then
		
		oDBConn.Errors.Clear

		Select Case DB_Type

			Case 0	'MSSQL Server
				sql = "BULK INSERT " & DB_TableName & " FROM '" & oImportFile.Path & "' WITH ( FIELDTERMINATOR = '" & Log_FieldTerminator & "', ROWTERMINATOR = '" & Log_LineTerminator & "');"

			Case 1	'MySQL Server
				sql = "LOAD DATA INFILE '" & oImportFile.Path & "' INTO TABLE " & DB_TableName & " FIELDS TERMINATED BY '" & Log_FieldTerminator & "' LINES TERMINATED BY '" & Log_LineTerminator & "';"

		End Select

		oDBConn.Execute(sql)
		If oDBConn.Errors.Count = 0 and Delete_after_import Then
			oFSO.DeleteFile oImportFile.Path, true
		End If	
	End If
Next
Set oImportFile = Nothing
Set oImportFolder = Nothing
Set oFSO = Nothing
Set oRegEx = Nothing
oDBConn.Close
Set oDBConn = Nothing


Function RegExFileMask( sMask )
          
	sRegExMask = Replace(sMask, ",", ";")
	sRegExMask = Replace(sRegExMask, " ", "")
	sRegExMask = Replace(sRegExMask, ".", "\.")
	sRegExMask = Replace(sRegExMask, "*", ".*")
	sRegExMask = Replace(sRegExMask, "?", ".")
	sRegExMask = Replace(sRegExMask, ";", "|")
          
	arrMasks = Split(sRegExMask, "|")
	retMasks = vbNullString
	For i = 0 To UBound(arrMasks)
		If Right(arrMasks(i), 1) <> "*" Then arrMasks(i) = arrMasks(i) & "$"
		If Left(arrMasks(i), 1) <> "*" Then arrMasks(i) = "^" & arrMasks(i)
		If Len(retMasks) > 0 Then retMasks = retMasks & "|"
		retMasks = retMasks & arrMasks(i)
	Next
          
	RegExFileMask = retMasks

End Function