Create a custom database format

This documentation is for legacy Kiwi Syslog Server versions 9.8.3 and older.

When you add an action to log messages to a database, you must specify the database format. If you do not want to use the standard formats available, you can create your own custom database format.

1. Choose File > Setup to open the Kiwi Syslog Server Setup dialog box.
2. Expand the Formatting node.
3. Right-click the Custom DB formats node and choose Add new custom DB Muformat.
4. Replace the default name with a descriptive name. (The name does not have to be unique.)

5. Specify the following options:

   Type	Select your database type from the Type dropdown menu. If your database type is not included, select Unknown format.
   Function	Drag and drop the gray Function cells to specify the order in which fields are created in the database table. This is also the order that data is inserted into the table.
   Field name	Select the fields to include as columns in the database table. 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 file. 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 fields. Example parsing scripts can be found in the \Scripts sub folder. If you select the Custom field checkbox, all 16 custom fields will be written to the log file. Each custom field is separated by the selected delimiter character. To edit a field name, double-click the name and replace it. The default 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.
   Size	For each field, 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.
   Type	Match each field type 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.
   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	Click this button to 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 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 Server')

6. Click Apply to save the format.

Examples of data formats

Field name	Type	Size	Data
MsgUnique	adInteger	4	1
MsgDate	adDBTimeStamp	16	28/01/2017
MsgTime	adDBTimeStamp	16	16:12:54
MsgDateTime	adDBTimeStamp	16	28/01/2017 16:12:54
MsgUTCDate	adDBTimeStamp	16	28/01/2017
MsgUTCTime	adDBTimeStamp	16	04:12:54
MsgUTCDateTime	adDBTimeStamp	16	28/01/2017 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 KSS