OBIEE Logging: Capturing IP Addresses and Failed Login Usernames

Introduction

Oracle Business Intelligence Enterprise Edition (OBIEE) is a powerful tool for business analytics, but its default logging configuration often falls short when it comes to security auditing. Many organizations deploying OBIEE (versions 10g, 11g, and 12c) face a common challenge: collecting crucial information such as client IP addresses, usernames, and event times for login failures. This blog post will guide you through the process of enhancing OBIEE logging to capture this vital information across different versions.

Tested OBIEE Versions

•

Oracle Business Intelligence Enterprise Edition 11.1.7.1

•

Oracle Business Intelligence Enterprise Edition 12.2.1.4

The Challenge

The default OBIEE configuration doesn't provide all the necessary information for comprehensive security auditing. Specifically, we need to collect:

Client IP address

Username (especially for failed login attempts)

Event time for login failures

While not supported out-of-the-box, we can achieve this with some additional configuration.

Solution Overview

Our approach involves two main components:

Collecting client IP addresses by logging HTTP in WebLogic server (access.log)

Logging usernames for login failure events in audit provider logs (DefaultAuditRecorder.log)

Let's break down what information we can collect in each version:

access.log

Information	OBIEE 11g	OBIEE 12c
Client IP Address	Yes	Yes
Event Message (Login Failure)	No	Yes
ECID (Execution Context ID)	No	Yes
Username	No	No
Event Time	Yes	Yes

•

In BIEE 12c, when login-failure event happens, we can get client IP address associated with "failed login" event from access.log. But please note there is no username information here.

•

In 11g, Only ip address and event time information can be gotten from the logs.

DefaultAuditRecorder.log

nformation	OBIEE 11g	OBIEE 12c
IP Address	No	No
Event Message (FailedLogin)	Yes	Yes
ECID	No	No
Username	Yes	Yes
Event Time	Yes	Yes

•

In both of 11g and 12c, username for failed-login event is logged to Default Audit Provider logs but it is not possible to get the relevant client ip address as there is no information can link with access.log

•

Additional Logs on Login Failure

bi_server1-diagnostic.log

This file also contains similar logs on the event as like the following.

	11g	12c
IP Address	No	No
Event Message (FailedLogin)	Yes	Yes
ECID	Yes	Yes
Username Information	No	No
Event Time	Yes	Yes

•

You can also find some related logs from bi_server1 (WebLogic Managed Server) logs. However, there is a lack of username.

Detailed Configuration Steps

1. Enable Extended Logging Format Fields for HTTP Logging

(Reference: https://docs.oracle.com/middleware/1212/wls/CNFGD/web_server.htm#CNFGD207)

Open WebLogic AdminServer console

Navigate to Environment > Servers > bi_server1

Lock & Edit > Logging > HTTP > Advanced

Change Format from "Common" to "Extended"

Add the following fields in Extended Logging Format Fields:

date time cs-authuser cs-username c-ip s-ip cs-method ctx-ecid ctx-rid cs-uri sc-status bytes
Shell
복사

For OBIEE 12c, you might want to use:

date time cs-method ctx-ecid ctx-rid cs-uri sc-status bytes x-GWXFF c-ip s-ip
Shell
복사

Save and restart the service

Note: For more information about these fields, refer to the "DNS Related Fields" section in the WebLogic Server documentation.

2. Enable Default Audit Provider

In WebLogic Server Administration Console, go to Security Realms > myrealm > Providers > Auditing

Lock & Edit and create a new audit provider

Click the newly created provider to edit details

Go to the Provider Specific page

Set severity level as desired (e.g., INFORMATION or CUSTOM)

Save, Activate Changes, and restart the service

For more details, refer to the WebLogic Server Auditing Provider documentation.

Analyzing the Logs

1. OBIEE 11g:

a. access.log

After setting extended logging format fields, you can see IP addresses in access.log. However, it's still challenging to determine what the status code relates to:

[11/Mar/2019:00:22:23 -0400] 192.168.1.100 - - GET /analytics/saw.dll?Dashboard HTTP/1.1 302 401

We can find client IP address but no additional information on the HTTP request status.

b. bi_server1-diagnostics.log

This log contains event information but lacks username and IP address:

[2019-03-11T00:22:23.000-04:00] [OBIPS] [ERROR:31] [] [saw.connectionPool.getConnection] [ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1] [tid: 296834816] Authentication Failure.
Odbc driver returned an error (SQLDriverConnectW).
State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43113] Message returned from OBIS.
[nQSError: 43126] Authentication failed: invalid user/password. (08004)

c. DefaultAuditProvider Log

This log includes the username for failed logins but no IP address:

Audit Log
Date: Tue Mar 12 14:30:45 EDT 2019
Formatted Date: 2019-03-12 14:30:45.928
Severity: FAILURE
Subsystem: Security
Event Type: Authorization
Event Status: FAILURE
Resource:
Message ID: BEA-090902
Message: FAILED Authentication
User ID: weblogic
Roles:
Authentication Method:
Contextual Data:
ServerName=bi_server1

d. saw0.log

We can find error messages on authentication failure but still there is no USERNAME information.

[2019-03-11T00:22:23.000-04:00] [OBIPS] [ERROR:31] [] [saw.connectionPool.getConnection] [ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1] [tid: 296834816] Authentication Failure.

Odbc driver returned an error (SQLDriverConnectW).

State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

[nQSError: 43113] Message returned from OBIS.

[nQSError: 43126] Authentication failed: invalid user/password. (08004)[[

File:connection.cpp

Line:436

Location:

saw.connectionPool.getConnection

saw.securitysubsystem.checkauthentication.runimpl

saw.threadpool.asynclogon

saw.threads

ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1

ThreadID: 296834816

]]

[2019-03-11T00:22:23.000-04:00] [OBIPS] [NOTIFICATION:1] [] [saw.securitysubsystem.checkauthentication.runimpl] [ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1] [tid: 296834816] Authentication Failure.

Odbc driver returned an error (SQLDriverConnectW).

State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.

[nQSError: 43113] Message returned from OBIS.

[nQSError: 43126] Authentication failed: invalid user/password. (08004)[[

File:checkauthentication.cpp

Line:1331

Location:

saw.securitysubsystem.checkauthentication.runimpl

saw.threadpool.asynclogon

saw.threads

ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1

ThreadID: 296834816

]]

e. nqserver.log

[2019-03-11T00:22:22.000-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1:6] [tid: aafc3700] oracle.webservices.provider.ProviderException: javax.xml.ws.WebServiceException: BI Security Service Access Denied - credentials supplied in SOAP Message header failed authentication

[2019-03-11T00:22:22.000-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f,0:1:1:6] [tid: aafc3700]  [nQSError: 43126] Authentication failed: invalid user/password.

2. OBIEE 12c:

a. access.log

OBIEE 12c provides clearer information on login failures:

[12/Mar/2019:14:30:45 +0000] 192.168.1.100 - - "GET /analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FCustomer%20Dashboard HTTP/1.1" 302 401 - "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.121 Safari/537.36" "11d1def534ea1be0:3924a6df:1696adf54e9:-8000-000000000000036f" "-”

Extended log format fields

b. DefaultAuditProvider log

This log includes the username for failed logins but still no client IP address:

Audit Log
Date: Tue Mar 12 14:30:45 EDT 2019
Formatted Date: 2019-03-12 14:30:45.928
Severity: FAILURE
Subsystem: Security
Event Type: Authorization
Event Status: FAILURE
Resource:
Message ID: BEA-090902
Message: FAILED Authentication
User ID: weblogic
Roles:
Authentication Method:
Contextual Data:
ServerName=bi_server1
FailureException=weblogic.security.providers.authentication.LockoutException

c. bi_server1-diagnostics.log

BIEE 12c is not different from 11g when viewing bi server diagnostics log file. It does not contain the client ip addresses related to the authentication failure.

Handling Load Balancers

When users access OBIEE through a load balancer, the client IP in logs will be the load balancer's IP. To capture the original client IP:

Add cs(X-Forwarded-For) field to the Extended Logging Format Field

Alternatively, create a custom Java class to extract the X-Forwarded-For header: Create a file named GWXFF.java:

import weblogic.servlet.logging.CustomELFLogger;
import weblogic.servlet.logging.FormatStringBuffer;
import weblogic.servlet.logging.HttpAccountingInfo;

public class GWXFF implements CustomELFLogger {
    public void logField(HttpAccountingInfo metrics, FormatStringBuffer buff) {
        buff.appendValueOrDash(metrics.getHeader("X-Forwarded-For"));
    }
}
Java
복사

Compile and create a JAR:

javac GWXFF.java
jar cvf GWXFF.jar GWXFF.class
Bash
복사

Add this JAR to your WebLogic classpath and update your logging configuration to use the custom field.

(For more details, you can refer to this blog. This blog shows adding cs(X-Forwarded-For) field to the Extended Logging Format Field.)

Conclusion

By implementing these configurations, you can significantly enhance OBIEE's logging capabilities, capturing crucial information for security audits and troubleshooting. While the solution isn't perfect (e.g., we can't directly correlate IP addresses with usernames in a single log), it provides a substantial improvement over the default logging setup.

Remember to test these changes in a non-production environment first and consider any potential performance impacts of increased logging. Additionally, ensure that your log retention policies and storage capacity are adequate to handle the increased log volume.

By leveraging these enhanced logging capabilities, you'll be better equipped to monitor and secure your OBIEE environment, respond to security incidents, and maintain compliance with your organization's auditing requirements.