For a more flexible SQL source configuration, you can customize your message fetching preferences (including customizing your fetch query and fetch history) for your SQL type message source.
Figure 120: Log > Sources > <your-new-source> > SQL > Fetching messages — Customizing fetching messages for your SQL type message source
For more information about creating your customized fetch query and customizing your fetch history, see the following subsections:
Under Log > Sources > <your-new-source> > SQL > Fetching messages, you can create a customized fetch query for your SQL type message source.
To create a customized fetch query for your SQL type message source
Navigate to Log > Sources > <your-new-source> > SQL > Fetching messages.
Figure 121: Log > Sources > <your-new-source> > SQL > Fetching messages — Customizing the fetch query for your SQL type message source
Enter your customized fetch query in the suitable format.
For more information, see Limitations, considerations, and best practices.
Select the Timezone of your choice.
Select the Facility level of your choice.
Select the Severity level of your choice.
(Optional) Enable Put all columns to SDATA.
Before you configure your customized fetch query, consider the limitations, considerations, and best practices in the current version (version 6.9.0 ) of syslog-ng Store Box (SSB):
Required column names
The custom fetch query has the following required column names it must return:
uid:
The uid column must contain a unique number. This number must increase monotonously. SSB will store the last read uid.
NOTE: SSB automatically substitutes the last read uid after the initial fetch.
datetime or date and time:
SSB will use the content of the datetime column as the time stamp of the log message. The following column types are supported:
Oracle: timestamp, int
MSSQL: datetime, int
If the type is int, SSB will assume that it contains a UNIX time stamp.
When using separate date and time columns, the date column must be date type, the time column must be time type.
message:
The message field must contain the message to be logged.
For example:
SELECT "uniq_id_number" AS "uid", "bsd_datetime" AS "datetime", "message" AS "message" FROM "test_table"
Optional column names
The custom fetch query may contain the following optional column names:
host (optional):
Optional column name. When in use, host overwrites the host field of the incoming message.
program (optional):
Optional column name. When in use, program overwrites the program field of the incoming message.
Considerations and best practices
NOTE: The query must not contain any comments.
NOTE: The host, program, and timezone parameters can be selected from columns or set as a fix value. The timezone must contain time-shifting value and not the name of the time zone. For example:
SELECT "myhost" AS "host", "myprogram" AS "program", "+01:00" AS "timezone", <further-parts-of-the-query>
|
|
Caution:
The syslog-ng Store Box (SSB) appliance does not validate or limit the contents of customized queries. Consequently, queries performed with a user with write-access can potentially modify or even harm the database. Use customized queries with care, and only for your own responsibility. |
The following custom fetch query queries records that are older than the last read record:
SELECT * FROM <table_name>
Or a more detailed example:
SELECT "uniq_id_number" AS "uid", "date_string" AS "datetime", "message" AS "message" FROM "test_mysql"
After customizing your fetch query settings for your SQL type message source, you can customize your fetch history settings as well.
NOTE: Customizing your fetch history has relevance when you are configuring your SQL type message source for the first time. After the initial message history fetching, the message source does not use these settings, but fetches the messages from the last fetched record instead.
To customize the fetch history settings for your SQL type message source
Navigate to Log > Source > <your-new-source> > SQL > Fetch history.
Figure 122: Log > Source > <your-new-source> > SQL > Fetch history — Customizing the fetch history settings for your SQL type message source
Select how extensive you want your fetch history.
To skip the initial message fetching from the database, select No history.
To specify from which particular UID you want the message source to fetch messages from your database, select Partial history, and specify the Start UID of your preference.
|
|
Caution:
Consider that the message source only fetches messages with a UID that is strictly larger than the specified Start UID. As a result, the message with the UID specified as the Start UID will not be fetched from the database. |
To quickly check your fetch history settings before committing your message source configuration, click Test message fetching. The message source will fetch a sample of the database, and you can check if your settings are right and if the fetched data match your database and your desired configuration and settings.
Under Log > Sources > <your-new-source> > SQL > Fetching frequency, you can customize your fetching frequency preferences for your SQL type message source.
To customize your fetching frequency preferences for your SQL type message source
Navigate to Log > Sources > <your-new-source> > SQL > Fetching frequency.
Figure 123: Log > Sources > <your-new-source> > SQL > Fetching frequency — Customizing your fetching frequency preferences for your SQL type message source
Specify how often you want syslog-ng Store Box (SSB) to periodically fetch data by entering the number of your choice (in seconds) in Fetch data every: <number> seconds.
(Optional) To allow SSB to read the database as fast as possible, enable Fast follow mode.
NOTE: SSB reads the database periodically, performing one query (that fetches multiple records) at a time. With Fast follow mode enabled, SSB continuously keeps querying the database until it fetches all records available at the time.
© 2024 One Identity LLC. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
