QRadar: JDBC connection troubleshooting and enabling debug logs

Troubleshooting

Problem

JDBC and its variances are used to connect to a Database and retrieve the records from a table or view. Functionally, the process can be divided into three steps when a new log source is created and enabled. This article helps administrators understand the steps the JDBC protocol takes to collect events.

Diagnosing The Problem

These diagnosis steps require the Log Source Management app with the Show Debug Messages option enabled.

Before you begin

To use these steps, administrators need to enable "Show Debug Messages when they configure a Log Source to use JDBC.

Open the Log Source Management app.
Select a JDBC protocol log source.
On the Log Source Summary pane, click the Test tab.
Click the Settings gear icon.
Enable Show Debug Messages in the user interface.
Click Start Test.
To download the Debug Logs, click the download arrow next to the settings gear icon.

Results
As the log source tests run, the logs contain extra debug logging information that can be reviewed or provided to QRadar Support to assist with troubleshooting.

Resolving The Problem

The JDBC is divided into three steps once the Log Source is created and enabled.

Establishing connections
Initial query
Records query

1 Establishing connections

In the connection step JDBC tries to make a connection to a URL. In /var/log/qradar.java.debug, administrators can find the connection URL message "attempting connection with url [URL]" and verify whether the connection URL is correct.

Procedure

Using SSH, log in to the QRadar Console as the root user.
Open an SSH session to the QRadar appliance making a connection to your database. This information can be found by confirming the Target Event Collector in your log source.
Type the following command to search for connection messages:
```
grep -i jdbceventconnector /var/log/qradar.java.debug
```
Review the output to confirm the address displayed in the connection string matches the expected host configured in the log source. For example:
Oct 22 13:03:19 ::ffff:QRadarHost [ecs-ec-ingress.ecs-ec-ingress] [Thread-165] com.q1labs.semsources.sources.jdbc.JdbcEventConnector: [DEBUG] attempting connection with url [jdbc:jtds:sqlserver://XX.XX.XX.XX:1433/GMC_PROCESNIDB;namedPipe=false;useNTLMv2=true;maxStatements=10;socketTimeout=300]

Results
If the connection returns a hostname or an IP address, review the JDBC log source configuration to confirm the log source parameters are correct.

2 Initial query

In the initial query step, JDBC makes the first query when it connects to the DB successfully. The first query is to select the max value from the comparable filed in the targeted table or view. The value is written into the property file under /store/ec/jdbc/ as a marker when the query is successful.

Results
An administrator is able to see a message similar to "connected. using sql: [sql statement]" and "connection url: [URL]" in /var/log/qradar.log.

3 Records query step

After the comparable marker is set, the log source sleeps until the next query time. The records that come after the marker in the table or view are retrieved. If the retrieval is successful, the marker gets updated for the next query and events are viewed in the Log Activity tab. The marker value remains the same if there are no new records returned. The records query step is performed periodically to retrieve any new records that get inserted in that table or view after the last query. It would be helpful to understand what the problem could be if one can first identify in which step the problem occurred. In the JDBC troubleshooting section, common problems and their solution are posted for diagnosis of any existing scenarios.

JDBC troubleshooting recommendations

These queries are executed in JDBC protocol:

select max(compare_field) from [table/view];
select * from [table/view] where [compare_field] > [value from 1 or from marker] order by [compare_field].

Some JDBC variation protocols might have their specific queries, but they all work in a similar way. Get the marker from the compare field first, then query for records based on the marker value.

Problem	Solution
Look in /var/log/qradar.error for driver errors or JAR path-related errors.	The Log Source Management app V7.0 or later indicates if your protocol is not on the latest version. As an additional step, administrators can verify the following files are in the /opt/qradar/jars directory on the appliance making the JDBC database connection: orai18n.jar ojdbc7.jar ojdbc8.jar ifxjdbc.jar ifxjdbcx.jar ifxlang.jar ifxlsupp.jar ifxsqlj.jar ifxtools.jar db2jcc.jar jtds.jar mssql-jdbc.jar postgresql.jar
An SQL connection error is seen in /var/log/qradar.java.debug: java.sql.SQLException: Network error IOException: Connection timed out (Connection timed out)	A timeout is set to 300 seconds in order to prevent DB connections from hanging. Administrators can run select max(compare_field) from [table/view] and select * from [table/view] where compare_field is less than [value from the first query] in any DBMS. If the query takes more than 5 minutes to return, an error occurs. The optimal solution would be cleanup the database table so the query can complete within 5 minutes.
The log source is in success state, but events are not displayed in the Log Activity tab.	Usually, it is because no events are returned from the database and no errors are returned from JDBC or ecs-ec-ingress. Administrators can try the following steps to resolve issues. Disable the log source. Modify the marker to set the value to be a smaller (ID) or an earlier (timestamp) value in the compare field column. Confirm there are records returned by executing the second query in a DBMS. Enable the log source.

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwt0AAA","label":"Log Source"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips