Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

Home > Success Center > Kiwi Syslog Server > Kiwi Syslog Server Administrator Guide > Log file and database formats > Create a custom database format

Create a custom database format

Table of contents

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
    1. 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.

    2. 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
Last modified
13:07, 2 Mar 2017

Tags

Classifications

Public