KTA - Audit SQL Object Documentation

Purpose

While OpenText AppEnhancer has comprehensive auditing features, the software does not track changes of individual document index values. These auditing sql scripts create stored procedures and triggers that work with the ae_audit to track index value changes.

Limitations

• Auditing data is provided only through SQL queries and does not contain a user interface. 3rd party reporting tools are required for user interface design.

• Scripts are written exclusively for ae content stored in Microsoft SQL server

• SQL objects create by these scripts ARE NOT supported by OpenText.

  • For Support Mitigation see Support Cases With OpenText

• No cross AE DataSource support

Installation

Prerequisites

• A SQL or Windows account with dbowner rights to the AppEnhancer database where the object will be deployed.
• SQL Server Management Studio (SSMS) or any software utility that permits executing Microsoft SQL Server scripts.
• Auditing in AppEnhancer Admin must be enabled for Document Index Add/Modified/Deleted and Document/Page Add/Delete Events.
  • Without AE Auditing enabled the user information cannot be joined with the index data in the au_dt table!!

Steps

1. Copy all SQL files to a local directory accessible to SMSS.
2. Open SSMS and login to the target SQL server.
3. In SSMS, click the File | Open | File... select the directory where the SQL scripts were saved and open the CreateAeAudit.sql file
4. In the "Available Databases" drop-down box, select the database containing the AppEnhance objects
5. Click the "Execute" button.
   • This will create the audit tablespace, tables,stored procedures, and functions used for auditing.
   • If you right-click on the ae database in Object Explorer and expand Tables, you should see tables names starting with audit.au_*
6. Repeat steps 2-4 and run the scripts contained in the getDtFix.sql and sp_update_au_fld.sql files.
7. At this point be aware that auditing WILL NOT be captured until the next steps are completed.
8. Click the "New Query" button and select the ae database in the "Available Databases" drop-down box.
9. Type the following command into the query and click the "Execute" button.

   exec audit.rebuildAll
   

10. To verify completion
    1. Right-click on the ae database in Object Explorer and select refresh.
    2. Expand the Tables | dbo.ae_dt# (where # is the id of an AE application. example dbo.ae_dt1_) | Triggers
    3. You should see a trigger named tr_au_dt# (where # is the id of an AE application. Example tr_au_dt1 )
    4. Perform the following sql queries in the ae database. Each select statement should return at least one row.

       select * from audit.au_apps			
       select * from audit.au_adefs
       

Maintenance

AppEnhancer AE Application Changes

In the event that new AE Applications are created or existing ones modified, auditing must also be updated. Perform the following steps:

1. Open SMSS and login to the target SQL Server
2. In SSMS, click the "New Query" button and select the select the ae database in the "Available Databases" drop-down list.
3. In the query editor run the following command

   exec audit.rebuildAll
   

4. This will update the audit.au_dt and audit.au_adefs with the latest ae application schema and rebuilt all tr_au_dt# triggers.

Considerations for the audit.au_dt Table

Since the audit.au_dt stores all ae_dt# index row updates in json format, this table has the potential to grow extraordinarily large. Older data can be purged based on either the audit.au_dt._createts field or the id field. Example for deleting index updates more than 1 year old.

' Deletes audit rows more than 1 year old
DELETE FROM audit.au_dt
WHERE createts < DATEADD(YEAR, -1, SYSDATETIMEOFFSET());

Support Cases With OpenText

Since OpenText Technical Support will not support the objects used by this audit, triggers placed directly on the dbo.ae_dt# can be removed with a sql stored procedure and added back after the case is resolved.

To remove the triggers in the event of a support case, execute this command in SSMS on the ae database.

exec audit.deleteAuTriggers

After the support case is resolved, execute this command in SSMS on the ae database.

exec audit.deleteAuTriggers