---
title: KTA - Audit SQL Object Documentation
description: This guide documents the SQL objects and their usage for auditing
---
[Skip to content](https://knowledgebase.metasource.com/kta-audit-sql-object-documentation#main-content)
[](https://www.metasource.com/)
Open main navigation
Close main navigation
- [Go to Customer Portal](https://clientsupport.metasource.com/tickets-view)
[Go to Customer Portal](https://clientsupport.metasource.com/tickets-view?hsLang=en)
Hello. How can we help you?
- There are no suggestions because the search field is empty.
1. [MetaSource Knowledge Base](https://knowledgebase.metasource.com/?hsLang=en)
2. [OpenText AppEnhancer (ApplicationXtender)](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en)
3. [Troubleshooting](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#troubleshooting)
# KTA - Audit SQL Object Documentation
## This guide documents the SQL objects and their usage for auditing
#### 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](https://knowledgebase.metasource.com/kta-audit-sql-object-documentation#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 oldDELETE FROM audit.au_dtWHERE 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
```
- [Tungsten (Kofax)](https://knowledgebase.metasource.com/tungsten-kofax?hsLang=en#main-content)
- [Installation and Setup](https://knowledgebase.metasource.com/tungsten-kofax?hsLang=en#installation-and-setup)
- [Troubleshooting](https://knowledgebase.metasource.com/tungsten-kofax?hsLang=en#troubleshooting)
- [Updates](https://knowledgebase.metasource.com/tungsten-kofax?hsLang=en#updates)
- [OpenText AppEnhancer (ApplicationXtender)](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#main-content)
- [Installation and Setup](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#installation-and-setup)
- [Basic Features](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#basic-features)
- [Troubleshooting](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#troubleshooting)
- [Updates](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#updates)
- [System Requirements](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#system-requirements)
- [Security](https://knowledgebase.metasource.com/opentext-appenhancer-applicationxtender?hsLang=en#security)
- [OpenText Intelligent Capture (Captiva)](https://knowledgebase.metasource.com/opentext-intelligent-capture-captiva?hsLang=en#main-content)
- [Installation and Setup](https://knowledgebase.metasource.com/opentext-intelligent-capture-captiva?hsLang=en#installation-and-setup)
- [Troubleshooting](https://knowledgebase.metasource.com/opentext-intelligent-capture-captiva?hsLang=en#troubleshooting)
- [PaperVision](https://knowledgebase.metasource.com/papervision?hsLang=en)
- [OpenText OTS (Output Transformation Server)](https://knowledgebase.metasource.com/opentext-ots-output-transformation-server?hsLang=en#main-content)
- [OTS](https://knowledgebase.metasource.com/opentext-ots-output-transformation-server?hsLang=en#ots)
- [FileBound](https://knowledgebase.metasource.com/filebound?hsLang=en)
[](http://www.metasource.com)
[getsupport@metasource.com](mailto:getsupport@metasource.com)
[(888) 634-7684](tel:18886347684)
© 2025 MetaSource, LLC. All rights reserved.