Day-to-Day Management
This chapter suggests some tasks you should perform in day-to-day management of a DirX Audit deployment to maintain its performance.
Check the Error Logs
All DirX Audit services write log files. Check them daily for any warnings and errors. For the location of the log files see the section “Log Files” in the Troubleshooting chapter of this guide.
Check for Audit Message Import Errors
When viewing the DirX Audit Server logs, you may encounter error logs about “unique key constraint violation”. These logs occur when the server repeatedly tries to import the same audit message.
Most commonly, it occurs when a file with audit messages has been put repeatedly into the file collector’s input folder. Rarely, it occurs when the import of audit messages from the DirX Identity domain via LDAP has been interrupted so that the LDAP collector can insert the message into the DirX Audit Database but not remove the record from the dxrHistory attribute in DirX Identity domain.
The collectors identify such issues and just store the not-imported audit message into a separate error folder. You can safely remove these files.
Monitoring Missing Links between History Entries
In DirX Audit Manager’s History view or in reports on history entries, you might miss a link from one entry to another; for example, from an account to a group where it is a member. In such a case, the link information is not available in the database. Reasons might be:
-
The target entry (in the account / group example: the group) is not available in the DirX Identity domain and is therefore also not in the DirX Audit History Database.
-
The target entry has not yet been imported into the DirX Audit History Database.
-
The target entry and the link value were available in the DirX Identity domain only for a short time between two consecutive runs of the History Synchronization jobs and were therefore never imported into the DirX Audit History Database.
-
The referenced target attribute is not mapped to the HST_LINK_ATTRS_IN_TIME table of the DirX Audit History Database.
-
The lifetime of the link value and the value in the referenced entry do not overlap.
-
The reference value in the DirX Identity domain is wrong.
Some background details:
The link value in DirX Identity domain is either an LDAP DN or, in the case of account-group memberships, a value referring to a configurable attribute in the target entry. In the default case for accounts and groups, the value in, for example, dxrGroupMemberOK should refer to the dxrPrimaryKey of the target entry with a value relevant only in the target system; for example, a DN in Active Directory or a primary key in a relational database.
For performance reasons, DirX Audit Server calculates a foreign key from this original information and stores it in the column LNK_ENTRIES_ID of the table HST_LINK_ATTRS_IN_TIME. This calculation is part of the regularly scheduled History DB update job. If for some reason a foreign key cannot be calculated, the column remains empty and DirX Audit Manager and reports cannot navigate to the referenced entry.
To generate an overview of these cases and allow administrators to remediate them, DirX Audit provides some reports on missing links. The reports can be identified by the tag Monitoring. A link is considered missing if the foreign key in the LNK_ENTRIES_ID column of the HST_LINK_ATTRS_IN_TIME table is empty.
-
The report Missing entry links shows the number of missing links for all entry types. For each entry type (such as Account or User), it lists the link attributes and, for two selectable time points, the number of missing links for all link values. The last column Difference gives the difference between the two time points. A summary line after each entry type shows the trend as a graph and the aggregated numbers for all link attributes.
-
The report Missing entry links by target system provides the same information for each target system. As missing links most often occur for account-group memberships, this report is considered the most important.
-
The report Missing entry links with details (CSV format) provides the most detail for all entry types in a format that can easily be post-processed by tools like Microsoft Excel. For each link attribute and value, it contains the dxrUid, dirxEntryUUID and entry name (normally the cn) along with the lifetime of the value (VALID_FROM - VALID_TO).
For more details on these reports, see the chapter “Monitoring DirX Audit Data with Reports” in DirX Audit Administration Guide.
We recommend running at least one of these reports regularly (weekly or monthly). If you observe higher numbers of missing links that do not eventually disappear, investigate these cases in more detail. Values that have been created on the current or last day might be explained by the schedules of the DirX Audit History Synchronization jobs and the DirX Audit History DB update job: the target entry might not have been updated in time.
For the other cases, check the potential reasons listed at the beginning of this section. Maybe you need to run the History Synchronization Modify jobs multiple times per day. Or if a referenced entry is never imported into the DirX Audit History Database, consider scheduling History Synchronization jobs for the type of the referenced history entry or exclude such the link attribute from the synchronization.
Minimize Directory Server Restores
Try to keep directory server restores to a minimum to avoid inconsistency in your DirX Audit History Database. Directory server restores may reimport already deleted DirX Identity entry attributes. These attributes are then synchronized to the DirX Audit History Database, where they affect the consistency of the history snapshot of your DirX Identity domain.
Check the Audit Database Size
The DirX Audit Database increases every day: new messages are inserted, history entries and their attributes are added and changed, facts on all that are added. For large queries or index updates, the database engine temporarily needs considerable space.
Make sure that the database has enough disk space and consider updating that monthly. See also the section “Remove Old Data” in this guide.
Maintain Database Indexes
Database indexes are a very important factor for the performance of database queries. This section provides hints on maintaining database indexes for best performance.
Check for Missing Indexes
DirX Audit comes with default indexes out-of-the-box. These default indexes may not be enough for the queries or the data of every customer. If some reports or some queries performed in DirX Audit Manager take unnecessarily long, a missing index can be the reason. In this case, you may need to analyze the query.
The DirX Audit Administration Guide in section “Tuning Database Performance” gives hints on how to analyze queries and tune database performance.
The following SQL queries provide a list of current indexes in the database. Choose one according to your platform.
install_path/conf/sql/adm/get_indexes_MSSQL.sql
install_path/conf/sql/adm/get_indexes_Oracle.sql
Update the Database Index
When database tables are changed (records created, updated, and deleted), the index also needs to be updated; otherwise, the index quality degrades over time and at some point, when building its execution plan, the database engine may decide to stop using the index and resort to full table scans.
Modern database engines support regular index updates automatically by default. However, the way they implement this function differs depending on the engine; in some cases, the automatic processes may not be enough.
For example, the Microsoft SQL Server database engine continuously modifies the index. This process scatters the information in the index, fragmenting it over time. Index fragmentation decreases the query performance until at some point, the database engine decides to perform full table scans. As a result, Microsoft recommends regularly and automatically rebuilding or reorganizing the indexes.
What is better: reorganizing or rebuilding? A rebuilding process locks the affected tables and views and thus blocks the clients working on them. However, rebuilt indexes are more effective. Microsoft recommends reorganizing the index if the percentage of fragmentation is between 10% and 30% and rebuilding the index if the percentage is higher than 30%.
The Oracle database engine, on the other hand, considers table statistics when calculating its execution plans. By default, it schedules a statistics-gathering job in the maintenance window at night to regularly update statistics.
Even automatic periodic index and/or statistics updates may not be adequate when, for example, a bulk load inserts or deletes a high portion of table rows and columns. Query performance may be significantly impacted until the next index rebuild. In these cases, administrators should start the index rebuilding process manually; for example, by running a regular job after the bulk load or before follow-up jobs.
For determining appropriate time ranges, it helps to build logical groups of tables and schedule the index rebuild for each group separately. Natural logical groups are: tables with history entries (HST*, HDB*), tables with audit events (DAT*), and fact with dimension and risk tables (FCT*, DIM*, TAG*, RSK*). For sample scripts, see the folder:
install_path/conf/sql/adm
Remove Old Data
From time to time, you should remove old data from the DirX Audit Database. Otherwise, the disks may become full and cause performance to degrade.
Audit Messages
For audit messages, export and delete old messages by running the DirX Audit DB tool. See the chapter “Using the DirX Audit Tools” in the DirX Audit User Interface Guide.
Use a function for the parameter to to specify up to which date events should be removed. For example value $bday(-365) tells the tool to export – and optionally delete – events older than a year.
Note that the fact and dimension tables remain untouched. That allows you to see charts on aggregated data even for events that have been removed. Of course, a drill-through to details of such events is then not possible anymore.
If necessary, you can re-import the archived audit events using the import function of the DirX Audit DB tool.
History Entries
For history entries, delete old history data (mainly attribute values) by running the DirX Audit DB tool. See the chapter “Using the DirX Audit Tools” in the DirX Audit User Interface Guide.
Use a function for the parameter to to specify up to which date data should be removed. For example value $bday(-365) tells the tool to delete history data older than a year.
Our experience shows that approximately half a million history entries can be completely exported into files having the total size of about 1 GB.
Note that the fact and dimension tables remain untouched. This allows you to see charts on aggregated data even for history entries from which data have been removed.
There is currently no option to re-import the deleted history data using the DirX Audit DB tool.