Creating a new custom format
To create a new custom database format, select the "Custom DB formats" option then right click to choose "add new custom DB format". Alternatively, you can use the "New" tool bar button at the top of the form. Initially the database type will show "Access database", choose your database type from the drop down list. If your database type is not shown, choose "Unknown format" and modify the fields to suit your database type.
Changing the order of fields
The order in which the fields are created in the database can be changed by simply dragging the field function cell and dropping it above or below other cells. When the mouse is moved over the dark grey "Function" cells, the mouse cursor will change to a drag-drop cursor. Just click, drag and then drop to change the order as you want. The order shown will be used when creating the database table and also when inserting data into the table.
Field function
The database field functions are listed in the first column. The next column containing the checkboxes allows you to enable or disable a particular field. If the field is not checked, it will not be included in the database INSERT statement or used when creating the database table.
Field names
The Field name column is editable so you can choose a suitable name for your field. The default field names are known to work on all databases. If you change the date field to a name of "DATE" for example, this may cause a problem with some database types because "DATE" is a reserved word. By using MSG at the beginning of the field name, you can avoid using reserved words.
Field size
When creating a database it is important to specify the field size so that the largest data element can fit into the field. Some field types do not need a size specified since it is implied by the field type. For example, a field type of Time is always assumed to be a size of 8 bytes. The size value is also needed by the program when it comes time to log data to the database. As the data is passed to the database via an INSERT statement, the data is trimmed to the specified field size. This avoids any errors caused by data that is too large for the field. For example, if you have specified the message text field to be 255 bytes, but a message arrives that is 300 bytes, the data will be trimmed back to 255 bytes before being logged.
Field type
Each field type must be matched to the type of data being logged. If you are not sure of the correct data type to use it is safe to use "VarChar" in most cases. When the data type cell is edited, a drop down combo will show allowing you to choose from a list of known data types. You can choose your own type instead of one from the list, by simply typing the value into the cell. The data types shown in the list are specific to the database format selected. For example, "Text" in Access becomes "VarChar" in SQL.
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.
Example of data format being logged:
Field name Type Size Data
---------------+---------------+-----+----->
MsgUnique adInteger 4 1
MsgDate adDBTimeStamp 16 28/01/2005
MsgTime adDBTimeStamp 16 16:12:54
MsgDateTime adDBTimeStamp 16 28/01/2005 16:12:54
MsgUTCDate adDBTimeStamp 16 28/01/2005
MsgUTCTime adDBTimeStamp 16 04:12:54
MsgUTCDateTime adDBTimeStamp 16 28/01/2005 04:12:54
MsgTimeMS adInteger 4 0
MsgPriorityNum adInteger 4 191
MsgFacilityNum adInteger 4 23
MsgLevelNum adInteger 4 7
MsgPriority adVarWChar 30 Local7.Debug
MsgFacility adVarWChar 15 Local7
MsgLevel adVarWChar 15 Debug
MsgHostAddress adVarWChar 15 192.168.0.1
MsgHostname adVarWChar 255 host.company.com
MsgInputSource adVarWChar 10 UDP
MsgText adLongVarWChar 1024 This is a test message from Kiwi Syslog Daemon
Field format
The data format can be specified for each data field. In most cases no formatting is needed. For date and time fields, the database will accept data in many formats and convert it to its own internal format. When it is queried, the data may actually appear to be in a different format to which it was logged.
The HostAddress field formatting allows you to zero pad the address so that it appears with leading zeros. This ensures the address is always 15 bytes long and allows for easy sorting by IP address.
Leaving the format cell blank will leave the data unmodified and it will be added as it is received.
Show SQL commands button
Pressing this button will display a list of commands used to create and insert data into a table. You can use these commands to create your own table within your database application. A default table name of "Syslogd" is assumed when generating the commands.
Example of SQL commands:
Database type: MySQL database
Database name: New Format
SQL command to create the table:
CREATE TABLE Syslogd (MsgDate DATE,MsgTime TIME,MsgPriority VARCHAR(30),MsgHostname VARCHAR(255),MsgText TEXT)
SQL INSERT command example:
INSERT INTO Syslogd (MsgDate,MsgTime,MsgPriority,MsgHostname,MsgText) VALUES ('2005-01-28','16:22:44','Local7.Debug','host.company.com','This is a test message from Kiwi Syslog Daemon')