Audit exports are used to investigate changes to employee data, including who made the change, when it occurred, and what the previous and new values were.
This is especially useful for resolving data discrepancies, tracking system activity, or reviewing historic amendments to key records like pay history, absence, or employment details.
Create an audit export
Log in to the Admin Tool.
Click Import/Export data.
Click Create an export definition.
Click Select the data to export.
Select the database: SelectPersist.
In the SQL tab, copy and paste the following SQL:
Select AUDIT.[Object Schema], AUDIT.[Object Name], AUDIT.[Key Number], AUDIT.[Audit Date], AUDIT.Action, [Audit Fields].[Field Name], [Audit Fields].[Is Text], [Audit Fields].[Was Text], [Audit Fields].[Is Date], [Audit Fields].[Was Date], [Audit Fields].[Is Number], [Audit Fields].[Was Number], [Audit Fields].[Is Decimal], [Audit Fields].[Was Decimal], [Audit Fields].[Is Boolean], [Audit Fields].[Was Boolean], Security.Users.[User Name] From AUDIT Inner Join [Audit Fields] On AUDIT.[Audit Number] = [Audit Fields].[Audit Number] Inner Join Security.Users On AUDIT.[User Number] = Security.Users.[User Number] Where AUDIT.[Object Schema] = 'employee' And AUDIT.[Object Name] = 'pay history'
📌 Note: This SQL query shows changes made to pay history records.
Filter the audit
Below is a breakdown of the audit query, and example criteria to ensure a targeted audit query.
Object Schema: A database schema is a way to logically group objects such as Tables & Views e.g, Employee.
Object Name: This is essentially the table in which the data is categorically stored e.g, Pay History.
Key Number: This is the Primary Key of the table that the record is associated with e.g, Pay Number.
🤓 Tip: It is recommended to filter by the key number to ensure the required data is returned.
For example, if you are auditing a pay record, run a data export to find the pay number.
Audit Date: This is the date recorded at the time a record was inserted, updated, or deleted.
Date criteria
>= '01-Oct-2018' | Later than or Equal to 'Date' |
<= '30-Oct-2018' | Earlier than or Equal to 'Date' |
>= '01-Oct-2018' And <= '30-Oct-2018' | Later than or equal to 'Date' And Earlier than or Equal to 'Date' |
Actions
INSERT | Indicating a new record has been created |
UPDATE | Indicates that a record has been changed |
DELETE | Shows a record has been removed |