New article
Recently updated
Using Delete Audits customisation to bulk-delete records
Who is this article for?Users who want to understand bulk-deletion of audits.
System Administrator permissions are required.
The Delete Audits customisation will delete from the system any audit matching criteria you set, regardless of its state.
This means that you can, for example, delete all audits for a given audit period (even if they are Completed, Approved, or Closed). This is useful where it is a requirement to delete all audits older than a certain date.
While other criteria can be set, we provide examples for only Audit Period, other requirements may be chargeable.
1. Versions
Earlier versions of the Delete Audits customisation do not support deleting Timesheets and Document Requests, and will cause problems if these are found.
To bulk-delete audits, ensure that:
- You are running v 5.4.0.x or v6.0.1.x of Internal Audit.
- You are using one of the versions of the Delete Audits customisation linked to here:
Each of these downloads contains a customisation package and a SQL script for adding some indexes.
2. Preparing a customisation
System
Before running any customisation, test it in UAT first.
To make the system ready to run the customisation:
- Ensure nobody is logged in to the application.
- Stop the Retain Server service (where present).
- Stop the Ideagen Internal Audit (Aura) robot service.
- Stop the Ideagen Internal Audit (Aura) application pools.
- Back up the Pentana database and verify.
- Back up the Retain database and verify (where present).
Adding database indexes
Run the script Ideagen Internal Audit (Aura) - Add Delete Audits indexes.sql against the database. This script adds a number of indexes that will significantly reduce the time taken to complete the process.
Customisation
The customisation package requires editing before it can be used.
To make the customisation ready to run;
- Make a list of each audit period name for which you want to delete the audits.
- At the server, open App Manager.
- Select the correct instance.
- Switch to the "Tasks" tab.
- In a File Explorer window, locate Customize_Delete_Audits.zip.
Note that if you have downloaded this file then, you need to Unblock it before it can be used. - Copy Customize_Delete_Audits.zip.
- Paste it to AppManager\Customize.
- Click Refresh in App Manager.
Delete Audits option will now be available in the list. - Quit App Manager.
- Navigate to AppManager\Customize\Customize_Delete_Audits.
- Open Config.xml in a text file editor.
- The Criteria section contains a set of entries that allow you to specify audit periods to delete.
The first one is already active and contains the dummy value "Audit period to delete". - Replace "Audit period to delete" with the name of your first audit period.
If you want to specify a second audit period:- Uncomment the second section, by deleting the comment marks <!--and -->.
- Replace the example value (the first example given is 2013).
- If you want to specify further audit periods, repeat the uncomment/replace process.
- Save the file
The customisation is now ready to run.
3. Running the customisation
With the system stopped and the database(s) backed up:
- Tick the Delete Audits check box.
- Click Apply.
App Manager will then delete the listed audits.
Deleting a large number of records may cause a lot of cache updating activity the next time a user runs the application, so it is useful to rebuild the local database.
To rebuild the local database:
- Switch to the "Config" tab.
- Click Rebuild LocalDB.
If there is an error or if App Manager is halted while the customisation is being run, the application will fail to start and you will have to revert to your database backup.
If the process completes without error:
- Restart the Ideagen Internal Audit (Aura) application pools.
- Restart the Robot service.
- Restart the Retain Service (if present).
Below is a video demonstrating the process from start to finish:
https://app.screendesk.io/recordings/193b136c-0ba1-4ae0-bbf5-997e17d942e8
Since the video was made, the v5.4.0.11 customisation has become available. Either of the two packages linked to at the top of this article are valid.
Note that after the customisation is run, timesheet lines associated with deleted audits will show as being associated with tasks that have no name. Optionally, you can update the records such that they appear against an audit called "DELETED AUDIT". In order to do this, follow the steps in this article: https://internalaudit.help.ideagen.com/hc/en-gb/articles/19404392808466
4. Additional information
Run time
It is not possible to predict how long this will take as there are various factors:
- Number of audits specified.
- Number of records attached to the audits.
- Speed of servers.
Running the customisation in UAT first will help you gauge how long the process will take.
Effect on FileStore and _log tables
This process deletes data via the application service and uses the same mechanisms as the desktop application.
This means that:
- Deleting a row causes a new row to be added in the related _log table.
- 'Deleting' an attachment does not remove it from the file store, it simply becomes inaccessible through the application.
Therefore, you will not see much decrease in database size after the process has run. To purge orphaned file data, ensure the Purge Filestore Robot job is scheduled.
Effect on checked out audits and reserved files
Users may have an audit or file checked out, and that then gets deleted.
If this happens:
- Reserved files cannot be accessed and will not appear in the user’s Home screen tiles.
- Checked-out audits cannot be checked back in or synchronised. The application will continue to prompt the user to connect to local or server when starting up.