Deep-Dive into your WebFOCUS Audit Logs

WebFOCUS stores information that allows you to check what’s happening on your system, but sometimes it is difficult to search for the desired information, or it has been cycled to prevent the filesystem from collapsing.

But ibi™ offers you other options that are not known as it could (a lot of people don’t read the manuals, and I’m including myself on that affirmation), that’s why I’m going to guide you through the Security And Administration Manual and the Logging section, which allows you to redirect the information stored on the logs to a table on your preferred database, and it doesn’t even have to be on the same box!

You can find the manual here and you also have a web page here 

The best thing about this technique is that you can generate your own reports to retrieve the desired information and create your own Security Dashboard. In a future article, we’ll discuss how to use the REST services to gather information. You’ll then be able to mix both and get the best information for the administrators and security administrators about everything that happens through WebFOCUS.

We’ll also need the appropriate JDBD driver of the DB that we’re going to use, so have it handy with the information of the connection to the database as we’re going to need it.

The very first thing is to create the table where we’re going to store the logs (but we’ll also keep the logs on the filesystem). In this case, I’m going to store the audit.log information. The manual shows how to create the table under a PostgreSQL database, but I’ll also provide you with the script for MS SQL Server so you can see the differences and adapt them accordingly to your preferred database (Oracle, DB2, Derby…).

Here are the scripts:

MS SQL Server

USE [DesiredDB]
GO

/****** Object:  Table [dbo].[wf_log] ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wf_log](
    [eventdate] [datetime] NOT NULL,
    [logger] [varchar](128) NOT NULL,
    [lvl] [varchar](12) NOT NULL,
    [logid] [varchar](128) NULL,
    [message] [varchar](255) NOT NULL,
    [excptn] [text] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

PostgreSQL

CREATE TABLE public.wf_log
(
    eventdate timestamp with time zone,
    logger character varying(128) COLLATE pg_catalog."default",
    level character varying(12) COLLATE pg_catalog."default",
    logid character varying(128) COLLATE pg_catalog."default",
    message character varying(255) COLLATE pg_catalog."default",
    exception text COLLATE pg_catalog."default"

GRANT UPDATE, INSERT, SELECT ON TABLE public.wf_log TO webfocus;

Notice that some text can be customized to your needs (like the table names for example), also, check that in SQL Server, there are no ‘level’ or ‘exception’ column names (those are ‘lvl’ and ‘excptn’) as those are reserved words in SQL, but those can be used in PostgreSQL.

Once you have the table created, it should look like this:

The next step that we need to do is to create a backup copy of the file that we’re going to modify, if we mess with something, we’ll probably lose the filesystem logs too, so having a backup copy is always a good idea.

Navigate to your WebFOCUS installation folder: ../ibi/WebFOCUSxx/webapps/webfocus/WEB-INF/classes and create a copy of the log4j2.xml file. Once you have the copy, we can start modifying the original.

Edit the file with your preferred text editor.

Following the instructions in the manual, we should go to the <RollingFile name="LOGuoa"> block in the <Appenders> section.

Just after this block, you can add the new JDBC Block (and you can add as much as you want; during some time I redirected all my logs to both, PostgreSQL and MS SQL Server), as you can see in the following screenshot:

Notice that my PostgreSQL was on a different server, and the MS SQL Server was on the same box as my WebFOCUS.

Here’s the code in case you want to Copy/Paste it (just replace the {{strings}} for your proper values)

        <!--
        <JDBC name="LOGpsql" tableName="public.wf_log">
            <DriverManager connectionString="jdbc:postgresql://{{IP:PORT}}/{{schema}}" driverClassName="org.postgresql.Driver" username="{{username}}" password="{{password}}"/>
            <Column name="eventdate" isEventTimestamp="true" />
            <Column name="logger" pattern="%logger" isUnicode="false"/>
            <Column name="level" pattern="%level" isUnicode="false" />
            <Column name="logid" pattern="%X{userId}" isUnicode="false" />
            <Column name="message" pattern="%message" isUnicode="false" />
            <Column name="exception" pattern="%ex{full}" isUnicode="false"/>
        </JDBC>
        -->
        <JDBC name="LOGmssql" tableName="{{DB_name}}.dbo.wf_log">
            <DriverManager connectionString="jdbc:sqlserver://{{IP:PORT}};DatabaseName={{DB_name}};encrypt=false" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" username="{{username}}" password="{{password}}" />
            <Column name="eventdate" isEventTimestamp="true" />
            <Column name="logger" pattern="%logger" isUnicode="false"/>
            <Column name="lvl" pattern="%level" isUnicode="false" />
            <Column name="logid" pattern="%X{userId}" isUnicode="false" />
            <Column name="message" pattern="%message" isUnicode="false" />
            <Column name="excptn" pattern="%ex{full}" isUnicode="false"/>
        </JDBC>

The manual explains the different patterns you can save and store on the columns, so you can customize what you want to save and store there.

Now that the connection is created and the patterns for the log are assigned to each column, we need to tell this same file which operations need to be logged there. So we’ll need to go to the <Loggers> block and add our “appender” for every logger we want to store in tables.

Due to the nature of the tables I created, I’m going to store all the information that comes from the logs related to any ‘com.ibi.uoa.xxxxx’ function. That includes any change on users, groups, rules, roles, signin, import or export of Change Management packages…

You can also redirect to PostgreSQL or MS SQL Server based on these loggers, for example, signin operations can be sent to PostgreSQL, while the rest of them can be sent to MS SQL Server.

Feel free to customize this to match your needs!

Here’s my code:

        <Logger name="com.ibi.monitor.requests" level="info" additivity="false">
            <AppenderRef ref="LOGrequests"/>
        </Logger>
        <Logger name="com.ibi.uoa" level="error" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.impex.import" level="debug" additivity="false">
            <AppenderRef ref="LOGcm_import"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.impex.export" level="debug" additivity="false">
            <AppenderRef ref="LOGcm_export"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.groups" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.users" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.roles" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.rules" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.signin" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.ownership" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.shares" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.content" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.config" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.seats" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.caster_config" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>
        <Logger name="com.ibi.uoa.magnify" level="info" additivity="false">
            <AppenderRef ref="LOGuoa"/>
            <AppenderRef ref="LOGmssql"/>
        </Logger>

And just to finish this part, you will need to add the appender to the root level=”error” block.

That way you’ll also be able to log any error occurring on those functions that appear on the events.

        <Root level="error">
            <AppenderRef ref="LOGevent" />
            <AppenderRef ref="sysout" />
            <!--<AppenderRef ref="LOGpsql"/>-->
            <AppenderRef ref="LOGmssql"/>
        </Root>

Save all of these changes, stop your application server, make sure that it can find the proper driver, for example, copying it under ../ibi/tomcat/lib folder or adding it to the CATALINA_OPTS (in case of Tomcat), clear the application server cache (work folder in case of Tomcat) and start it again.

You should be able to signin now in WebFOCUS, and see how the new table starts growing with data:

Now, just create a new synonym on the WebFOCUS Reporting Server against this/these table/s (depending on how many of them you just created). 

And once done, you can start creating your visualizations for your Dashboard:

And build something you can use to find the information you want to see from the logs in a more comfortable view:

Start enjoying reviewing your logs and leave apart those boring plain-text ones!

Hope this helps you to secure even more your environment!

Pablo Alvarez