Managing DirX Audit Databases

You can choose from the list of supported relational database management servers. You can make an individual decision for the audit messages database (Data DB) and for the configuration database (Config DB) but the database type for the audit messages database and history entries database (History DB) must be the same.

A connector / driver for the SQL Server is pre-installed with DirX Audit Manager Classic and DirX Audit Server. We recommend that you do not change these settings.

A connector / driver for Oracle Database must be installed separately. See the section “Oracle Database JDBC Driver Installation” in “Installation Configurations” in the DirX Audit Installation Guide for instructions on how to install it.

Create one, two or three technical accounts for connecting to the database(s) by following the instructions in the relational database management server documentation.

Note that they need the access rights to create tables, indexed / materialized views, indexes and procedures and write records. On Oracle Database, the account must also be allowed to create and execute triggers and jobs and work with full-text structures.

When using Oracle Database, the account should have at least the following privileges:

CREATE_TABLE
CREATE_INDEXTYPE
CREATE_TYPE
CREATE_VIEW
CREATE_MATERIALIZED_VIEW
CREATE_SEQUENCE
CREATE_PROCEDURE
CREATE_JOB

and should have the following roles:

CONNECT
RESOURCE
CTXAPP

Create one, two or three databases to store your audit messages (Data DB), configuration data (Config DB) and history entries (History DB). Make the technical account(s) – an owner of the database(s). Use the relational database management server documentation to perform this task.

Follow the following rules when naming databases:

The Tenant Configuration Wizard can create Data DB objects for audit messages, Config DB objects for configuration data and History DB objects for history entries including tables, views and indexes automatically when configuring the databases and validating their contents. You can run SQL scripts located on the installation media to create the set of tables, views and indexes manually. The provided indexes support DirX Audit Manager and DirX Audit Manager Classic Audit analysis filters and predefined reports distributed with the product. Use the following scripts with your database administration tool in the order given here:

The last script has to be run in all used databases: Data DB, Config DB and History DB.

If you are upgrading DirX Audit, please follow instructions in the DirX Audit Migration Guide.

Determine whether additional indexes should be created when designing new filters in the DirX Audit Manager Audit analysis or reports. Query processing is highly dependent on the database indexes. Use the relational database management server documentation to perform this task.

If you selected to use the full-text search in the database configuration, you must also create the full-text database structures. Use the script create_data_fulltext.sql in the correct folder base on the database server type to create the full-text structures and create_data_jobs.sql to set up a job for optimizing it, Oracle Database only.

For Oracle Database, the full-text search index is synchronized on each commit. This configuration causes fragmentation and degradation of the index over time. Therefore, a job is created to optimize it once a day. See the script create_data_jobs.sql. If you encounter performance or space problems with this configuration, you should change the configuration of the full-text index. For example, synchronize every hour and optimize or rebuild on a weekend. Consult with your database administrator to determine the best approach.

The created materialized views on Oracle Database are refreshed by default once a day at 5 A.M. See the section “Managing Oracle Databases” for details.

This section provides information about typical audit messages originated in DirX Identity and strategies for controlling them.

This section provides information about typical audit messages originated in DirX Access and strategies for controlling them.

If the size of the DirX Audit Database is about to reach the system limits, you should purge a set of messages. Use the DirX Audit Database maintenance tool (DirX Audit DB tool) to perform this task. A regular system task can be scheduled when using the tool with relative time parameters. See “Using the DirX Audit Tools” in the DirX Audit Command Line Interface Guide for details.

Data exported with this utility is written in compressed XML files. Compared to the content in the database, this action reduces the size to about 3 to 5 percent.

Purged data is structured in year, month, and daily folders. Thus you can easily restore parts of this data to your standard database or to another one for specific analysis. If the audit messages restored from the archive have never been covered by fact population, you need to run also the fact population tool with a time range that contains the restored messages.

The Identification – UID (unique identifier) field is required and is constrained to be unique. If the value is missing when the audit message is collected and to be persisted, DirX Audit Server persistent unit generates a new value using the java.util.UUID class and calling its randomUUID() method. The generated Identification – UID value is extended with a configurable prefix, dxt_ by default. You can set a different prefix value; see the section “Customizing the Identification – UID Prefix” for details.

The Identification – UID value can be missing accidentally in audit messages collected from DirX Identity or DirX Access products, but this always indicates a failure in the audited system and should be reported to product support along with the original message. On the other hand, when auditing a third-party system, the Identification – UID can be missing intentionally because the audited system cannot produce any unique identifier value for audit messages. In these cases, a new unique identifier value is always generated. Please pay special attention when designing your system to guarantee that the audit message is not collected repeatedly. This action leads to duplication of the audit message in the DirX Audit Database with a different Identification – UID value. Other fields are identical.

You can search for potential duplications by running a SQL query over the DirX Audit Database.

You can extend the join condition with a test on equality of values in additional columns like:
IDENTIFICATION_CATEGORY,
IDENTIFICATION_CAUSE,
IDENTIFICATION_OP,
IDENTIFICATION_OUTCOME or
IDENTIFICATION_TYPE.

You can then delete duplicated audit messages with a query like the following:

This query cascades deletion to the rows in related tables.

You can change the Identification – UID prefix by setting the property for it in the install_path/conf/configuration.cfg file. This file is created and later updated by the Core Configuration Wizard during the first configuration.

The property key for the UID prefix is uid_prefix in the [general] section. The Core Configuration Wizard does not set this particular property in this file. You need to add it manually.

If the value is not set in the file, the default value dxt_ is used.

History database data must be processed to be available in views. During this procedure, foreign keys are calculated. A scheduled job running on the DirX Audit Server updates data only for the last several days. If you want to update older data, you can run the SQL queries delivered in install_media/Additions/Scripts/common/updatehistdb manually. Remove the section that contains a placeholder that restricts the execution for the several last days from the where clause or optionally extend manually the existing History DB update scheduled job time range parameter jobdatamap.arg_membership_validfrom in the route-dxt-scheduler-updatehistdb.xml file.