Understanding & configuring the beqom auditing system

This article provides information regarding the audit system enforced by beqom to keep track of changes performed in the application, including the auditing table, audit log storage etc.

Preamble

There are five types of audit logs in the beqom application as listed in the table below:

Audit Type	Audit Configuration	Audit Log Storage	Comment
Tenant kernel objects	To be configured in rps_audit	rps_audit_log rps_audit_log_detail	JSON file
Custom tables via grids	To be configured in track_changes	<TableName>_xhisto (automatically generated)	N+3 columns: existing table columns, id_user, date_action and type_action
Custom tables via forms	To be configured in rps_audit	rps_audit_log rps_audit_log_detail	JSON file
Process grid values	Always	k_m_values_histo	No configuration required
WCF service calls	Always	k_stats	Newt API method calls are not considered

Tenant Kernel Objects Auditing

The system offers three main table to configure the auditing of kernel objects:

• rps_audit

• rps_audit_log

• rps_audit_log_detail

This is a warning message.

Any changes to the configuration of kernel object audit requires a restart of the server. This is due to the fact that the configuration is cached in the session factory, which is created only once, after the server is restarted. To perform this operation, please contact the beqom Platform Operations team.

rps_audit Table

The rps_audit table stores the configuration of the kernel table. It contains two columns:

• id_table: taken from k_referentials_tables_views > id_table_view

• audit_level: the audit log has three different levels:

  • None (0): if the value of the column is 0, the system does not keep any logs

  • Log (enabled for table names only; 1): if the value of the column is 1, the system keeps base-level logs in rps_audit_log

  • Log Detail (enabled for both table names and table columns; 2): if the value of the column is 2, the system keeps base-level logs in rps_audit_log and log details in rps_audit_log_detail. This option has an impact on application performance; since all columns are always saved in the audit, changes performed in the system can be slowed down.

rps_audit_log Table

This table contains the base-level logs of kernel tables. When the audit_level column of the rps_audit table is configured to 1 or 2, the system then keeps logs inside this table.

Content of the log:

• id_context

• id_user_modified

• id_item: object ID (such as process or indicator)

• table_name: modified table

• date_modified: date at which the modification was performed

• audit_type: Insert, Update or Delete

• source_log: nhibernate = 1, Tsql = 2

This is an note/information message.

Only NewValue records are kept in rps_audit_log_detail/audit_values for source_log=2.

The rps_audit_log table has the following structure:

rps_audit_log_detail Table

This table contains detailed logs of kernel tables. When the audit_level column of the rps_audit table is configured to 2, the system then keeps logs inside this table.

Content of the log:

• id_audit

• column_value: modified value

• column_name: name of the kernel table column

• audit_values: changed column values. The record contains OldValue and NewValue.

The rps_audit_log_detail table has the following structure:

Configuration in Admin Portal

To configure the auditing level for kernel objects in the Admin Portal interface of the beqom application, proceed as follows:

1. Log into the beqom Admin Portal interface, and then open the (Undefined variable: CompoVariables_AP.Admin).

2. Navigate to Technical Admin > Settings Management.

3. In the Audit Configuration drop-down list, located in the "Global" section of the page, select the audit level that you want to use (see previous sections for the explanation of the various auditing levels):

   • None

   • Log

   • Log detail

4. Click the save icon, at the bottom of the page.

Audited Application Areas

The following sections describe the behavior of the auditing system across the various components of the beqom application.

Web Application

The following areas of the beqom Web Application are audited using kernel object auditing:

• Settings management, under the (Undefined variable: CompoVariables_WA.Admin). Limited to certain features

• "Rules" component

• "Populations" component

• Process grid and process form

• Data grids

Admin Portal

(Undefined variable: CompoVariables_AP.Home)

All changes to the (Undefined variable: CompoVariables_AP.Home) are audited.

(Undefined variable: CompoVariables_AP.Admin)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Admin) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Technical Admin
	Synchronization	Field save
	Universe Management	Add and delete
	Settings Management	Field save
Functional Admin
	Users	Grid and form
	Teams	Grid and form
	Profiles	Grid and form
	Business Universes	Grid and form
	Scheduler	Grid and form
	Widget	Grid and form
	Environment Manager	N/A

(Undefined variable: CompoVariables_AP.Organization)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Organization) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Populations
	All	Form update
Administration
	Payees	Form
	Hierarchies	2 kernel grids
	Dimensions	Kernel grids

(Undefined variable: CompoVariables_AP.Design)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Design) and the type of auditing that is performed for these items.

Area	Page	Audit Type
Process
	Processes	Grid and form
Administration
	Indicators	Grid and form
	Indicator Fields	Grid and form
	Periods	Grid and form
	Workflows	Grid and form
	Cube	Grid and form
	Tools > Data Operations	Grid and form
	Frequencies	Grid

(Undefined variable: CompoVariables_AP.Rules)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Rules) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Rules
	Rules	Grid and form
Execution Plans
	Execution Plans	Grid

(Undefined variable: CompoVariables_AP.Data)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Data) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Grids
	Grids
Administration
	Import Export	N/A
	Grid Editor	Grid and form
	Grid Relation	Grid and form
	Form Editor	Grid and form

(Undefined variable: CompoVariables_AP.InfoHub)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.InfoHub) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Communication Portal
	Documents
Communication Workbench
	Notification	Available
	Task	Available
	Grid Relation	Available
	Form Editor	Available

(Undefined variable: CompoVariables_AP.Process)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Process) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Hierarchy View		Grid
Process View
	Processes	N/A

(Undefined variable: CompoVariables_AP.Analysis)

The following table lists audited items in the (Undefined variable: CompoVariables_AP.Analysis) and the type of auditing that is performed for these items.

Area	Page/Item	Audit Type
Analysis
	Standard Reports	N/A
Administration
	Edit Reports	Grid and form
	Edit Filters	Grid and form

(Undefined variable: CompoVariables_AP.Simulations)

The following list details audited items in the (Undefined variable: CompoVariables_AP.Simulations) and the type of auditing that is performed for these items.

• Grids: in the (Undefined variable: CompoVariables_AP.Data), under Administration > Grid Editor, select the option Allow track changes to keep the history of the grids

• Rules and people: audit activated

Custom Table Auditing

Changes to the data of custom grids can audited using the Allow track changes feature. To enable this feature on a table, select the option Allow track changes in the (Undefined variable: CompoVariables_AP.Data), under Administration > Grid Editor.

Once the option has been activated for a given table, the system creates a history table that will the logs related to this grid.

These history tables are structured as follows:

Table Name	Columns
<TableName>_xhisto	id_user_xhisto dt_xhisto type_xhisto All columns of the original table This is an note/information message. If the grid has a staging area, a column called is_coming_from_staging is added to the history table.

When a value is changed in any column of the grid, the system will push all old values of the row to the history table/

This is an note/information message.

Text goes here.If the data of a custom table with audit activated is changed using the form, the audit logs are generated in the rps_audit_log and rps_audit_log_detail tables.

Process Grid Auditing

Changes to process grids are stored in the k_m_values_histo table.

WCF Service Call Auditing

The main table responsible for auditing user activity is the k_stats table. Usually, audit reports are based on this table. The information stored there is more specific to the beqom application, and also provides telemetry data related to the users.

This table, and the access to the this table, is governed by the following principles:

• Only people who can access the database can access these logs

• Depending on the volume of logs, information aggregation can be rather long

• The records in this table are stored indefinitely. As a result, a proper maintenance plan of the table must be enforced

• The table stored the activity of each service call, with the following details:

  • Request time

  • Response time

  • User ID in the beqom tenant

  • Parameters

  • Name of the method and service in WCF

  • Session key (unique for each beqom session).

The following query example details the method to obtain the list of active users in the tenant:

Copy

selectdistinct firstname_user, lastname_user, max(requestTime) as last_active
from k_stats with (nolock) 
where requestTime >dateadd(minute,-60,getdate())
groupby firstname_user, lastname_user
orderbymax(requestTime) desc