This feature is only available in the licensed version.
This will log the message to the table specified by the ODBC Data Source Name (DSN).
ODBC DSN connect string
Fill in the DSN connect string field with the database DSN and UserID and Password information.
Alternatively, press the Browse button to choose from a list of ODBC DSNs configured on your machine.
The DSN connect string consists of the following elements:
Data Source Name
This references an ODBC DSN that has been configured on the system. Press the Browse button to choose from a list of configured ODBC Data Source Names on the system.
UID=UserID; This is only required if the database is password protected. Enter a valid user name for the database here.
PWD=Password; This is only required if the database is password protected. Enter a valid password for the database here.
E.g. DSN=Syslogd;UID=Admin;PWD=Password;
Each element must be separated by a semicolon. If there is no UserID or Password required, then the connect string will only need to consist of the DSN.
The default DSN connect string used is DSN=Syslogd;
In most cases, you need to specify the text "DSN=" before the DSN name.
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 ODBC 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:
·
Access
·
SQL
·
MySQL
·
Oracle
The default database table design is as follows.
Microsoft Access database
FieldNameTypeSize
DateMSGDATEDate10
TimeMSGTIMETime8
PriorityMSGPRIORITYText30
HostnameMSGHOSTNAMEText255
Message textMSGTEXTMemo1024
Notes:
To log to an ODBC data source you will need to install the MDAC (Microsoft Data Access Components) drivers. Using version 2.50 or above is recommended. This package can be downloaded from Microsoft at the following URL: http://www.microsoft.com/data/
If the database file is opened exclusively by another process, Kiwi Syslog Daemon may not be able to write new records to the database.
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.
ODBC Control Panel button
This button will open the Control Panel ODBC applet so you can configure a System DSN or check available ODBC options.
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 Daemon
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 Daemon 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 Daemon')
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.