Managing History Database Tables

This chapter describes how to configure the scheduled History Database (History DB) update job, which computes additional information from stored history values. Its main function is to calculate foreign keys from the stored DN values in imported history entries to obtain relational database references to History DB tables. In addition, it performs some other tasks and can also be extended.

The History DB update job runs as a scheduled job in the DirX Audit Server and is enabled automatically when the license for the History DB feature is valid and DirX Audit Server is installed and properly configured. Each configured tenant has its own scheduled job and a separate instance of DirX Audit Server is used for every tenant starting with version 7.2.

If you want to change the History DB update job schedule, you only need to start the Tenant Configuration Wizard for the respective tenant and choose the Scheduled Jobs Configuration. After finishing the configuration, if the DirX Audit Server is running, the job is rescheduled immediately. For more details, see "Scheduled Jobs Configuration" in the DirX Audit Installation Guide.

The job scheduling and details for the History DB update job can also be configured manually in the file install_path/server_container/tenants/tenantID/deploy/routes/route-dxt-scheduler-updatehistdb.xml. This file has the same structure and syntax as the scheduled fact population service definition file. See the section "Defining the Schedule" in the chapter "Managing Fact and Dimension Tables" of this guide for details. After modifying the file, copy it to a different directory outside of the tenant route deployment folder, delete the file from the tenant route deployment folder, and then copy the saved file back into the tenant route deployment folder. This procedure ensures that the modifications are registered with the server and reflected in the job scheduling.

The next sections describe how to define the job and how to express relative dates in the SQL script files.

Defining the History DB Update Job

You must define the job and (optionally) provide the SQL script files that contain the additional statements to execute. There are several update tasks hard-coded in the DirX Audit Server for updating the DB. The configurable SQL files are used to extend and customize the History DB update job. The SQL files are executed prior to the hard-coded actions.

You can limit the number of updated entries by setting the filter for VALID_FROM fields of the updated entries. The general limit can be set in the jobdatamap.arg_update_validfrom_condition argument. This argument must be a simple condition (included in the where clause). This filter will be used in the hardcoded actions and can be referenced also in the SQL files (using the %ARG_VALIDFROM_COND% placeholder). No filter will be used if this argument is not given or is empty (all suitable entries will be updated).

By default, no filter is used and all relevant entries are updated and fields calculated. If the action takes too long, set the valid-from condition.

You can supply multiple SQL script files separated by commas in the jobdatamap.arg_sql_files parameter. Each file must contain only one statement. The file paths can be either absolute or relative to a folder given in the optional argument (key jobdatamap.arg_sql_folder) or to the current working directory. The jobdatamap.arg_sql_folder value can be either an absolute or relative path (to the folder install_path/conf/sql).

A licensed History Database installation provides configured versions of all the necessary files. You can also find all SQL script files at the following locations on the installation media:

install_media/Additions/Scripts/common/updatehistdb/
update_HST_LINK_ATTRS_IN_TIME.dxtreldatesql.txt
install_media/Additions/Scripts/common/updatehistdb/
update_HST_ROLEPARAMS_IN_TIME_PARAMENTRY.dxtreldatesql.txt
install_media/Additions/Scripts/common/updatehistdb/
update_HST_ROLEPARAMS_IN_TIME_PARAMVALUE.dxtreldatesql.txt
install_media/Additions/Scripts/common/updatehistdb/
insert_HDB_REQWF.txt
install_media/Additions/Scripts/common/updatehistdb/
insert_HDB_REQWF_ACTIVITIES.txt
install_media/Additions/Scripts/common/updatehistdb/
insert_HDB_REQWF_RESOURCES.txt

There is one optional parameter jobdatamap.arg_membership_validfrom, which is only relevant to the membership calculation task of the History DB update job and represents the time at which these memberships will be recalculated.

Here is an example of a job definition in the file for the History DB update job that uses five SQL script files:

...
<route>
<from uri="dxt-scheduler://DXT-71a75691-d28a-48ce-a542-6d6af7ece680-jobs/UpdateHistoryDbJob?
cron=0+0+1+?+*+*&amp;
jobClass=com.dirxcloud.audit.common.jobs.quartzjobs.UpdateHistoryDbJob&amp;
jobdatamap.tenant_id=71a75691-d28a-48ce-a542-6d6af7ece680&amp;
jobdatamap.arg_sql_folder=common/updatehistdb&amp;
jobdatamap.arg_sql_files=
update_HST_LINK_ATTRS_IN_TIME.dxtreldatesql.txt,
update_HST_ROLEPARAMS_IN_TIME_PARAMENTRY.dxtreldatesql.txt,
update_HST_ROLEPARAMS_IN_TIME_PARAMVALUE.dxtreldatesql.txt,
insert_HDB_REQWFS.txt,
insert_HDB_REQWF_RESOURCES.txt,
insert_HDB_REQWF_ACTIVITIES.txt&amp;
jobdatamap.arg_membership_validfrom=TD-30" />
<log loggingLevel="INFO"
logName="com.dirxcloud.audit.server.routes.scheduler.updatehistdb.Route"
message="Job 4606b126-b70e-4c16-b271-faf8c4f9674d -
UpdateHistoryDbJob has finished." />
</route>
...

Expressing Relative Dates in SQL Files

The SQL files can use following special placeholders for expressing dates relative to current date and time of the particular execution:

%REL_DATE_FROM(RELDATE_EXPR)% - to set the from-date. The date and time are shifted by the given offset and then set to the beginning of the given relative date period; for example, beginning of day, week, month.
%REL_DATE_TO(RELDATE_EXPR)% - to set the to-date. The date and time are shifted by the given offset and then set to the end of the given relative date period; for example, end of day, week, month.
%ARG_VALIDFROM_COND% - use the valid-from condition from the quartz definition XML file (see above). Please notice that this argument expands to a condition including the operator.

The RELDATE_EXPR uses the same syntax as the expressions for fact population. For details, see the section "Defining the Schedule" in the chapter "Managing Fact and Dimension Tables".

Examples of the relative date expressions include:

TD - today
TD-1 - yesterday
TD-3 - three days ago
TW-1 - last week

These placeholders are replaced by the corresponding date-time value in the query as a java.sql.Timestamp value with configured calendar. There can be any number of these placeholders used. Each will be replaced by the correct date-time value during execution.

update HST_LINK_ATTRS_IN_TIME
set LNK_ENTRIES_ID = (
 select top 1
  HST_ENTRIES_IN_TIME.HST_ENTRIES_ID
 from
  HST_ENTRIES_IN_TIME
 where
  HST_LINK_ATTRS_IN_TIME.LNK_ENTRIES_ID is null
   and HST_LINK_ATTRS_IN_TIME.ATTRIBUTE_VALUE =
    HST_ENTRIES_IN_TIME.dn
   and HST_LINK_ATTRS_IN_TIME.VALID_TO is null
   and HST_ENTRIES_IN_TIME.VALID_TO is null
 order by
  HST_ENTRIES_IN_TIME.VALID_FROM desc
 )
where
 LNK_ENTRIES_ID is null
  and HST_LINK_ATTRS_IN_TIME.VALID_FROM >= %REL_DATE_FROM(TD-3)%