Configure UDLA Log Collection

To add a Log Message Source record for a UDLA source, follow the steps to Add a Single Log Source with the following differences:

For Message Source Types, either:
- Use one of the provided UDLA Message Source Types.
- Create a new Message Source Type based on the LogRhythm Universal Database Log Adapter format in the Log Source Type Manager.

From the Log Message Source Properties dialog box, enter the required information on the UDLA Settings tab.

Cutting-and-pasting the following Settings into a UDLA configuration in the LogRhythm Client Console may produce characters that are not supported by UDLA. The Device Configuration Guide contains links to downloadable .xml files that you can import for many devices.

UDLA Setting

Description

ODBC / OLE DB

Select the type of connection to the database. The default value is ODBC.

Connection String*

Contains the information needed to establish a connection to the database. It is a series of keyword – value pairs separated by semicolons (;). The equal sign (=) connects each keyword and its value. The Connection String used will be dictated by the database type.

These are templates of possible Connection String values.

The strings for any particular database may be different.

Database Type	Connection String
ODBC Connection
MS SQL Server	Driver={SQL Server};Server=myHostname; Database=myDBName;Uid=myUsername;Pwd=myPassword;
Oracle	Driver={Oracle in OraDb10g_home1};Server=myHostname; Uid=myUsername;Pwd=myPassword;
DB2	Driver={IBM DB2 ODBC DRIVER};Database=myDBName;Hostname=myHostname; Port=myPort;Protocol=TCPIP;Uid=myUsername;Pwd=myPassword;
MySQL	Driver={MySQL ODBC 3.51 Driver};Server=myHostname;Port=myPort; Database=myDBName;User=myUsername;Password=myPassword;Option=3;
OLE Connection
MS SQL Server	Provider=[Provider Name];Server=myServerAddress; Database=myDataBase;Uid=myUsername; Pwd=myPassword;
Oracle	Provider=msdaora;Data Source=MyOracleDB; User Id=myUsername;Password=myPassword;
DB2	Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=xxx.xxx.xxx.xxx; Initial Catalog=MyCtlg;Package Collection=MyPkgCol;Default Schema=Schema; User ID=myUsername;Password=myPassword;
MySQL	Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword;

Query
Statement*

The Query Statement is a SELECT statement that identifies the columns that are included in the log message. The Query Statement MUST include the column names that are specified for the Unique Identifier Field and the State Field. The format of the Query Statement is dictated by the database type.

MS SQL Server: SELECT TOP <Max_Message_Count> Column1, Column2, Column3 from Table

MS SQL Server (for date range): SELECT TOP <Max_Message_Count> Column1, Column2, Column3 from Table where RecordDateColumn >= <Start Date> and RecordDateColumn <= <End Date>

The SQL statement must begin with ‘SELECT TOP <Max_Message_Count>’ to get data returned.

Oracle: SELECT * from (select Column1, Column2, Column3 FROM Table order by Column1) WHERE ROWNUM <= <Max_Message_Count>

Oracle (for date range): SELECT * from (select Column1, Column2, Column3 FROM Table where RecordDateColumn >= <Start Date> and RecordDateColumn <= <End Date> order by Column1) WHERE ROWNUM <= <Max_Message_Count>

Output
Format*

The Output Format is used to define the format of the log message. It can include text that appears as a label before the column names.

Using the data from the sample ‘Database Table 1,’ the Output String of

“ID: <Key> DATE: <Date> TIME: <Time> LOG: <log> will return a log message formatted like this,

“ID:2 DATE:01/01/2009 TIME:07:01:01 LOG:An event has occurred.”

Unique
Identifier Field*

The Unique Identifier Field value can be a single column name, when the table has a primary key, or multiple column names separated by a comma. For more information, see Examples of Unique Identifiers.

The column(s) entered in the Unique Identifier Field must be included in the Query Statement.

Message Date Field

When used, this determines the Message Date of the log message.

The column name entered in the Message Date Field must be included in the Query Statement.

State Field Type*

There are two choices for this field: Timestamp or increment. If the incorrect type is selected, you receive a warning message when saving the configuration.

The column name entered in the State Field Type must be included in the Query Statement.

State Field*

This field is used by the System Monitor Pro or Collector Agent to track its collection state. It must be an integer or a date/time stamp.

The column name entered in the State Field must be included in the Query Statement.

State Field Conversion

This field is disabled unless the State Field Type selected is Timestamp. The value converts the string data from the State Field into the specified format.

For example: TO_TIMESTAMP('DATE', 'DD-MON-YY HH24:MI:SSXFF')

Get UTC Date
Statement

When provided, this normalizes the log message date. It is recommended that a value be entered for the Get UTC Date Statement.

MS SQL Server: SELECT GetUTCDate()

Oracle: SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM dual

DB2: select current_timestamp - current timezone from sysibm.sysdummy1

MySQL: select UTC_TIMESTAMP()

In the Output Format field, specify the format of the logs.
Click the Test button to verify that a connection to the target database can be established. The following criteria apply for the test to work:
- You must do the test from the agent that has the ODBC connection configured (or configure the ODBC connection on the PM for the test to work).
- The test button won't work if you are using Windows authentication with a service account for the ODBC connection.
If the connection is made, the UDLA Test Results window opens and shows the format of the log messages based on the Output Format specified in the UDLA Settings tab. Make the set up and testing process as efficient as possible by gathering all required information before you begin.
- The UDLA Test Results window has a Show SQL button that displays the actual SQL used to retrieve the data. It is an interpretation of the value entered into the Query Statement field. You can copy the string to the system clipboard from the UDLA SQL window.
- The UDLA Test Results window has a Query button that retrieves additional rows of data if there are more rows than the Max_Message_Count value. You can use the Query button to retrieve all the rows of the specified table.