Kiwi Enterprises - HOW TO: Script a Bulk Insert from a Kiwi Syslog Server Log file to Database


I would have paid the registration fee for the 'unregistered version'.

HOW TO: Script a Bulk Insert from a Kiwi Syslog Server Log file to Database

1) Create Custom Log File Format (separating date-time into separate date and time fields)

Log file fields:

Date
Time
Priority
Host name
Message text

Date format: YYYY-MM-DD
Time format: HH:MM:SS

Field delimiter: Tab

screenshot

2) Create Log to File action using Custom file format created in step (1)
Path and file name of log file:
\\server\share\logs\SyslogCatchAll.txt

Log file format:
Custom 1 - My New File Format

screenshot

3) Schedule: Add new Archive schedule task. (This will run the Bulk Import process every day at midnight).
Schedule frequency:
Day, Run-at 00:00 every 1 day.

screenshot

4) Source: Set the source location (this should be that same as the path specified in step (2)
Source location: \\server\share\logs\
File mask: *.*
Any size, any age.

screenshot

5) Destination: Specify the destination location for the daily archive task.
This will be the location that the database server will import the log file from (so you should ensure that it is accessible from the database server). Since the files will be generated every day (at midnight), use a dated file name format of YYYY-MM-DD, and adjust the date to that of the previous day. To avoid data duplication in the database, select "Move files" from source to destination.

Destination location:
\\server\share\archived-logs

Move files from source to destination.
Use dated file names: YYYY-MM-DD, insert formatted date before beginning of the file name.
Adjust file name to that of the previous day.

screenshot

6) Archive Options:

Run program after all files are moved/copied.

cmd.exe /c "wscript.exe C:KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.vbs"

(assumes that KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.vbs is located in C:)

Download KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.txt
NB. You will need to rename this file to KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.vbs

screenshot

7) Edit C:KiwiSyslogDaemon_BulkInsert_LogFileToDatabase.vbs

Edit the following 4 sections according to your requirements.

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

Log_FileLocation = "serversharearchived-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

8) Archive Notifications: Specify a report recipient if you wish to be notified of the tasks completion.

screenshot

9) Apply the changes

! Security Note !

If you are using Intergrated (SSPI) or Windows authentication as the database connection authentication method then you need to ensure that the Kiwi Syslog Server Service Account has the appropriate database priveleges in order to run the BULK INSERT. This may mean changing the default Service LogOn account (which is .LocalSystem by default) to a domain user account that has database insert privelege on the Kiwi Syslog Server database.

Alternatively, instead of using Windows integrated security you could use standard SQL authentication.

ie.

DB_ConnectionString = "Provider=sqloledb; Data Source=localhost; Initial Catalog=kiwisyslog; User Id=sa; Password=xxxxxx;"

100 % of 1 voters found this article helpful - Did you?

YES

Send this article by email

Leave a comment