Action - Log to Database

This feature is only available in the licensed version.

This will log the message to the table specified by the Data Link connection string.

Data link connection string

Press the Browse (...) button to create or edit Data link properties.

The Data Link Properties dialog box opens, displaying the following tabs: Provider, Connection, Advanced, and All.

Click "OK" when done.
 

Database Table name

A valid database table name must be specified. The table specified must contain field names that match the selected database format. If the field sizes are too small, the data could be truncated when written to the database.

The default table name used is Syslogd.

To test the Log to Database action, press the Test button. A message will indicate if the action was successful, or details of any error that occurred will be displayed.

Database type/field format

Choose from the list of default database types or create your own format by clicking on the Edit custom format button.

The default database types are:

The default database table design is as follows.

Microsoft Access database

Field                Name                Type                Size

Date                MSGDATE        Date                10

Time                MSGTIME        Time                8

Priority        MSGPRIORITY        Text                30

Hostname        MSGHOSTNAME        Text                255

Message text        MSGTEXT        Memo                1024

SQL database (Microsoft SQL and generic SQL)

Field                Name                Type                Size

Date                MSGDATE        DateTime        10

Time                MSGTIME        DateTime        8

Priority        MSGPRIORITY        VarChar        30

Hostname        MSGHOSTNAME        VarChar        255

Message text        MSGTEXT        VarChar        1024

MySQL database

Field                Name                Type                Size

Date                MSGDATE        Date                10

Time                MSGTIME        Time                8

Priority        MSGPRIORITY        VarChar        30

Hostname        MSGHOSTNAME        VarChar        255

Message text        MSGTEXT        Text                1024

Oracle database

Field                Name                Type                Size

Date                MSGDATE        Date                10

Time                MSGTIME        Time                8

Priority        MSGPRIORITY        VarChar2        30

Hostname        MSGHOSTNAME        VarChar2        255

Message text        MSGTEXT        VarChar2        1024

Notes:

If the database file is opened exclusively by another process, Kiwi Syslog Server may not be able to write new records to the database.

Some example ODBC databases are available for download from: http://www.kiwitools.com/downloads/Syslog_ODBC_Samples.zip

The zip file contains information and sample databases that you can use as a guide to help you setup ODBC logging on your own system.

Create table button

This button will attempt to create the specified table in the database referenced by the DSN. Any existing table will be deleted and the contents lost. The new table will be created with the field names and types specified by the database type you have selected. If all goes well and the new table is created, you will see a confirmation message. If there is a problem creating the table, an error message will be displayed so you can work on correcting the problem.

Query table button

This button will attempt to retrieve the last 5 entries in the table specified. The DSN type must be set to allow dynamic access. Forward only databases can't be read correctly since the "Move previous" command is issued to the database.

The data returned will be displayed in notepad. You can then get some information on the table structure and the data contained in the last 5 fields.

Example of information returned by the query:

Field name      Type            Size  Data

---------------+---------------+-----+----->

MsgDate         adDBTimeStamp      16 28/03/2005

MsgTime         adDBTimeStamp      16 14:45:16

MsgPriority     adVarWChar         30 Local7.Debug

MsgHostname     adVarWChar        255 host.company.com

MsgText         adLongVarWChar   1024 This is a test message from Kiwi Syslog Server

Edit custom format button

If a custom format is selected from the database type drop down list, then pressing this button will take you to the custom format selected. If no custom format is selected, then you will be taken to the "Custom DB formats" option where you can create a new format of your choice.

Show SQL commands button:

This button will generate the SQL commands used to create and insert data into the selected table. The commands generated are dependent on which database format is selected. You can use these commands to generate the database table schema in your database application. Alternatively, you can have Kiwi Syslog Server create the table for you by pressing the "Create table" button.

Example of SQL commands generated:

Database type: Access database

Database name: Kiwi Access format ISO yyyy-mm-dd

SQL command to create the table:

CREATE TABLE Syslogd (MsgDate DATE,MsgTime TIME,MsgPriority TEXT(30),MsgHostname TEXT(255),MsgText MEMO)

SQL INSERT command example:

INSERT INTO Syslogd (MsgDate,MsgTime,MsgPriority,MsgHostname,MsgText) VALUES ('2005-03-28','14:58:04','Local7.Debug','host.company.com','This is a test message from Kiwi Syslog Server')

Connection Inactivity timeout:

This value controls how long the database connection is kept open after the last message has been sent. Because opening and closing the connection can be the slowest part of logging to a database, the connection is kept open while data is actively being logged. If no more messages have been logged before the timeout value expires, the database connection will be closed. As soon as a new message arrives, the connection will be reopened. The default for this setting is 600 seconds (10 minutes). Using a value of 0 will ensure that the connection will never time out. The maximum value is 86400 seconds (1 day).

Run debug command button:

If there is a problem logging to the database, you can use this button to diagnose the problem. A separate window is displayed where you can enter the SQL command to be executed on the database. If the command fails, a detailed error message will be displayed in the results field. By default, the current INSERT statement that is used for the database type selected will be displayed in the query field. This statement can be modified to test particular variations of the statement.

This option may not be used to run a query on the database. Only error information is returned to the results field. It is not possible to run a Select From statement for example and obtain the results back. All that will be returned is an indication if the statement executed correctly or not.

By using the Show SQL commands button, you can obtain the correct syntax to use in the debug test.

Custom fields

Custom fields are for use by the run script action. By writing a parsing script, the syslog message text can be broken down into various sub fields. The values can then be assigned to the 16 custom fields and then logged to a database. Because each device manufacturer creates syslog messages in a different format, it is not possible to create a generic parser that will break up the message text into separate fields. A custom script must be written to parse the message text and then place it in the custom database fields. Example parsing scripts can be found in the \Scripts sub folder.

It is also possible to use the scripting function ActionLogToODBC to send SQL statements and raw data to a database connection.