With new data breaches being discovered nowadays almost weekly, it becomes ever more important to protect your application data and keep track of which user has accessed what data and any changes he may have made.  Such a chronological record of access to and modification of data is called an audit-trail.  

Audit trails are often used as an investigative tool to detect and analyze application and user behavior for data breaches before they have made such a significant impact such as the complete exposure of the entire database of the application. In a properly configured audit trail system events leading to such an exposure are monitored, and as such data breaches can be kept to a minimum impact, if proper action is taken quickly. 

Audit trails are commonly kept in logs called audit logs, which keep track of: 

• Every access and changes to sensitive data in detail. This way, any unintended access can be traced directly to the person who viewed this data, or even changed it. 

• Any successful login attempt is kept track of; however, also every unsuccessful attempt. If someone is trying different passwords on the same account, it could be an indication that this account is being hacked, or the user had forgotten his password at least 

• Which pages are accessed, and the duration of any action on that page. 

In this article we explain how to create such an audit trail for a USoft 10 web application with help of external tools, and which actions and changes must be made to enable these features. Strategically though, you could exclusively implement the trail in USoft. Because the log information in a USoft application could originate from several different components, like the Services Framework, the Page Engine, and the Rules Engine, we have chosen to combine these different streams of information into one single logging source, in a standardized logging format called Syslog. We found that Syslog is perfectly suited for this, because it can keep track of when messages were generated, which component (or as Syslog calls it: ‘facility’) was responsible for it and which machine or IP address the message originated from.  

Any additional information could be added within the log message itself, which would then be parsed by the audit logging server that receives the Syslog messages.  

The logging capabilities of Web Designer applications running as part of a Service Definer publication have been improved since the USoft 9.1.1M release. 

The improvement extends the logging capability by allowing following additional log outputs: 

• accessing url's and files (using the original request data)  

• session monitoring (which session is used, does the user log in or out, did a login succeed or not, timeouts and pings)  

• Page Engine input and output  and its generated queries  

This extra or modified logging is INFO logging level or higher (e.g. ERROR where appropriate), because audit-trails typically do not contain DEBUG information.  

This extra logging allows you to direct logging to an external target. We will demonstrate how to do this to an (external) Syslog server, and as a demonstration on how to make an effective (searchable) audit-trail as well, also on ElasticSearch. This is a two-part article, the first part contains the instructions for enabling the logging, whilst the second part demonstrates its usage with external applications.
 
For sake of simplicity, we will assume that all logging will be directed to the local filesystem of a single host. A single Service Framework server and Page Engine is installed on this host. The log server will also run locally and the external applications are available within the domain network, but will reside on a seperate server. 
 

Enabling the extra logging feature 

Since USoft uses Apache Log4j2 for the Page Engine and for the Service Framework logging service, enabling Syslog logging feature involves extending the log4j configuration files. 

Page engine logging configuration

When publishing a web application, the log configuration file for the Page Engine is published  in the publication directory as well.  To enable logging to SYSLOG for the Page Engine do the following: 

1. Navigate to the _internal directory of the Web publication. 

2. Edit the log configuration file log4j2.xml by appending the following in the <Appenders> section: 
    

<Socket name="SYSLOG" host="localhost" port="514" protocol="UDP"> 

   <PatternLayout pattern="<%level{TRACE=143, DEBUG=143, INFO=142, WARN=140, ERROR=139, Fatal=139}&gt; audit: %m%n"/> 

</Socket>

This is where the Page Engine implements syslog logging format via log4j its SocketAppender and the specific pattern layout .More on PatternLayout. We are telling log4j to send the log data to localhost via  UDP sockets at port number 514, the default port number for syslog protocol. 

3. Add the following to the <Loggers> section:
    

<Logger name="MY_APPLICATION_NAME:Audit" level="INFO" additivity="false"> 

	<AppenderRef ref="SYSLOG"/> 

	<AppenderRef ref="consoleLogger"/> 

</Logger> 

4. The page engine is now enabled for Audit logging. 

Service framework logging configuration

The Service Framework also uses the same log4j2 service and its configuration is similar to that of the Page Engine.  To enable logging for the Service framework do the following: 

1. Navigate to the Logging directory of the Server Framework server publication. 

2. Edit the log configuration file log4j2.xml by removing the complete ”com.usoft.uservice.urest.Main” <Logger> entry under Loggers.  

3. Add the following <Socket> elements  inside the <Appenders> , just below the last <RollingFile> entry : 
    

<Socket name="SyslogMain" host="localhost" port="514" protocol="UDP">  

	<PatternLayout pattern="&lt;%level{TRACE=143, DEBUG=143, INFO=142, WARN=140, ERROR=139, Fatal=139}&gt; %C: %x %m%n"/>  

</Socket>  

<Socket name="SyslogWeb" host="localhost" port="514" protocol="UDP">  

	<PatternLayout pattern="&lt;%level{TRACE=143, DEBUG=143, INFO=142, WARN=140, ERROR=139, Fatal=139}&gt; web: %x %m%n"/>  

</Socket>  

<Socket name="SyslogSession" host="localhost" port="514" protocol="UDP">  

	<PatternLayout pattern="&lt;%level{TRACE=39, DEBUG=39, INFO=38, WARN=36, ERROR=35, Fatal=35}&gt; session: %x %m%n"/>  

</Socket>  

<Socket name="SyslogApplication" host="localhost" port="514" protocol="UDP">  

	<PatternLayout pattern="&lt;%level{TRACE=143, DEBUG=143, INFO=142, WARN=140, ERROR=139, Fatal=139}&gt; application: %x %m%n"/>  

</Socket> 

Notice the same details apply here as seen with the Page engine.

4. Add the following <Logger> elements  inside the <Loggers> :

<Logger name="com.usoft.uservice.urest.Main" level="INFO" additivity="false"> 

	<AppenderRef ref="UService_Appender"/> 

	<AppenderRef ref="SyslogMain"/> 

</Logger> 

<Logger name="org.glassfish.grizzly.http.server" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogMain"/> 

</Logger> 

<Logger name="com.usoft.uservice.server.Server" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogMain"/> 

</Logger> 

<Logger name="com.usoft.uservice.security.RequestInfoFilter" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogWeb"/> 

</Logger> 

<Logger name="com.usoft.uservice.security.ResponseInfoFilter" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogWeb"/> 

</Logger> 

<Logger name="com.usoft.uservice.security.SessionManager" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogSession"/> 

</Logger> 

<Logger name="com.usoft.uservice.pageengine.PageEngineResource" level="INFO" additivity="false"> 

	<AppenderRef ref="SyslogApplication"/> 

</Logger>

5. The Service Framework is now enabled for Audit logging.
    
6. Restart any running service framework server.

Application level logging via Syslog client RDMI component 

The above instructions enable the beforementioned logging types, except that logging of DB (Select, Insert, Delete, Update) SIDU operations within a USoft application is not included yet. To include such logging, instead of relying on reconfiguration of log4j, this type of logging is enabled component wise, so we rely on building a RDMI component as the syslog client.  

The advantage is that you have freedom to develop this component.  
 

Syslog Client RDMI component 

An example of an RDMI component of a Syslog client is available as an Off The Shelf (OTS) component. This component can be fetched from the Github repository. More instructions on retrieving OTS components. The example is a slightly adapted version of an open source example of a C# Syslog client as found here. 

The component (appropriately named OTS_SYSLOG_CLIENT_RFC3164) itself is kept fairly simple, and consists of a constructor method and one method called SendLog. This method generates a one-line log message, which is sent to the configured Syslog server. By default, the address of this server is on “localhost” with port 514, using the UDP protocol. Other settings may be specified via the component constructor, please refer to the component description for further details. 

The resulting messages sent to a Syslog server could look like :
 

<110>Apr 17 12:20:33 AUDIT_EXAMPLE USR_ACCDu]: <!-- On Thu 17 Apr 2021 12:20:33 test5 updated USR_ACC( ACC_ID, USR_ACC). ACC_ID: '100' USR_ACC: 'test123' FIRST_NAME: 'John Doe' changed to: 'John test' --> <USR_ACC TIMESTAMP="2020/1217122033" USER="test5"> <ACC_ID key="100" /> <USR_ACC key="test123" /> <FIRST_NAME old="John Doe" new="John test" /> </USR_ACC> 

Here, the <110> depicts the binary value representation of logging level and facility value being used, in this case Info level, on facility LogAudit. For most if not all log lines that pass through this component, this combination of level and facility are used. The format is according to RFC3164.The next part is the date/time stamp used by the Syslog format, followed by the machine that generated it, followed by our log message, which first indicates the table that is being logged, followed by the query (SIDU) type (in brackets), and then the message itself. Any line breaks of the original message have been replaced by spaces, because Syslog depicts that a message must be written on a single line.
 

Constraints 

To call this client with the appropriate log data, we implement two type of table constraints. There is one that triggers the call to the syslog client on any SIDU operation (post-commit) on a driving table “AUDIT_LOG”, e.g: 
 

invoke OTS_SYSLOG_CLIENT.sendlog with
SELECT  adl.audit_type 
       ,adl.sidu 
       ,adl.logging
FROM audit_log adl 

This table is the audit log source table. Similarly, this table should be inserted via table constraints on any other table that needs to be included in the logging. This means that you will need separate table constraints per table. A snippet of such a constraint for some user account role table:
 

INSERT INTO auth_audit_log ( audit_type , sidu , logging )
SELECT  'E' 
       ,decode(old(user_account_role_id) ,null,'I' /*

INSERT */ , decode(user_account_role_id , null, 'D' /*
DELETE */ , 'U' /* update */ ) ) , auth_logutil.RecordDiff ( 'USER_ACCOUNT_ALLOWED_ROLE' , user , '#USER_ACCOUNT_ROLE_ID', old(uar.user_account_role_id), uar.user_account_role_id , '#USER_ACCOUNT_ID', old(uar.user_account_id),
FROM user_account_allowed_role uar
WHERE .... etc 

You can see that inserted records match with the expected input parameters of the C# client. The first parameter indicates in code that a table is being updated, the second distinguishes SIDU via decode functions, the third provides XML formatted details on record differences. This XML is provided via a call to a seperate custom LOGUTIL component intended for formatting, in the earlier example you saw the XML content of the log message. We have omitted the details of this component. The developer has freedom to implement the XML formatting component. 

At this stage, there exists no process yet to receive, manage and visualize the log data. To include this part we need to introduce additional external applications.

Check out the following article: Syslog logging for audit trails - Part II - Output to external applications