|
Action - Log to Database |
Top Previous Next |
|
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.
|