Export to DB (ODBC)

Export to DB (ODBC) runs SQL against an ODBC data source for each matching message (usually INSERT).

The feature reads messages from syslog storage, not from the collector as they arrive. The collector keeps writing to storage even if this feature is slow, suspended, or failing — storage acts as a large buffer so collection is not blocked.
Syslog Watcher — Export to DB (ODBC) feature
Edit Feature: Export to DB (ODBC)

Common Feature, Data Fields, and Filter settings are described in Managing Features. This page covers ODBC Configuration.

ODBC Configuration

Setting Description

ODBC connection string

Connection string used by the Syslog Watcher Server service, not by Manager’s desktop session. Click (…​) to open the connection string builder (lists ODBC drivers and data sources on this computer; only when managing the local server).

Messages per transaction

Messages grouped in one SQL transaction (BEGIN / COMMIT). 0 disables transactions (each command auto-commits).

System DSN vs User DSN

The server service typically runs as Local System. Use a System Data Source (System DSN), not a User DSN bound to an interactive login.

If the System DSN option is missing in ODBC Data Source Administrator, start Syslog Watcher Manager as Administrator, or configure the DSN on the server with the 64-bit ODBC administrator (odbcad32.exe from %windir%\System32).

When Manager is connected to a remote server, build or paste a connection string that that machine can use. The builder’s driver list is local to Manager.

SQL Commands

Simple mode uses a single Commands template (usually one INSERT).

Example:

INSERT INTO syslog (message) VALUES ('{MESSAGE|EscSQL}');

EscSQL doubles single quotes so the value is safe inside SQL string literals. See Escape Modifiers.

Click Switch to Advanced Mode to define several Commands groups. Each group has:

  • a Group filter (blank = all messages that already passed the feature filter)

  • one or more SQL commands

Every matching group’s commands run, in listed order, for that message. If no group matches, no SQL runs for that message.

Use several groups when messages need different SQL — a different table per originator, or INSERT versus UPDATE. A group can also run extra SQL only for some messages, for example a second insert into an audit table for Error and above.

The destination table must already exist; see Destination Table.

Destination Table

The feature does not create tables, columns, or indexes. Create those objects in the DBMS first, then write SQL Commands whose column names match.

Do not put CREATE TABLE in the feature commands — that statement would run for every matching message. Create the table once in the database tool; use INSERT (or UPDATE) in the feature.

There is no required schema. Pick columns for the fields that are needed, with types the DBMS can store. Add indexes on the columns that will be searched (often received time and originator).

Example table and a matching insert (SQL Server types; adjust for the engine):

CREATE TABLE syslog (
  received        DATETIME      NOT NULL,
  originator_id   VARCHAR(64)   NOT NULL,
  originator_name NVARCHAR(256) NULL,
  severity        SMALLINT      NULL,
  facility        VARCHAR(32)   NULL,
  message         NVARCHAR(MAX) NULL
);
INSERT INTO syslog (received, originator_id, originator_name, severity, facility, message)
VALUES (
  '{RECEIVED|%Y-%m-%d %H:%M:%S}',
  '{ORIGINATOR_ID|EscSQL}',
  '{ORIGINATOR_NAME|EscSQL}',
  {SEVERITY_NUM},
  '{FACILITY}',
  '{MESSAGE|EscSQL}'
);

{RECEIVED|%Y-%m-%d %H:%M:%S} is a timestamp in the storage time zone, formatted for a datetime column. See Date/Time Modifiers if the DBMS needs a different pattern. Use a Unicode large-text type for message bodies (NVARCHAR(MAX) on SQL Server, or the equivalent on the engine).

Notes

  • Large Messages per transaction values improve throughput but delay commit. If the server stops before a commit, those messages are exported again after it restarts.

  • The ODBC driver bitness must match the Syslog Watcher Server process (64-bit).

Related Topics