Insight - How to delete Object History logs

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Atlassian Support Offerings

The following approaches that involves SQL queries are beyond Atlassian Support Offerings. Please note that Atlassian does not support direct database INSERT, UPDATE or DELETE queries, as they can easily lead to data integrity problems. Atlassian will not be held liable for any errors or other unexpected events resulting from the use of the following SQL queries.

Purpose

Remove Object's History logs is not possible through Insight UI. This is how to perform this action using Database queries.

This has been recorded as an improvement area for Insight:

JSDSERVER-8706 - Getting issue details... STATUS

Until the product improvement is in place this Article intends to describe a way of removing Object's History records.

Environment

Insight asset management

Solution

Before you begin

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.


Table AO_8542F1_IFJ_OBJ_HIST is where all the History values are stored for existing Objects. To verify all of the affected entries, and the database structure for further tweaks, run a select query against the object that you're looking to remove history from:

You can specify the criteria for the deletion of rows, for example - any History row/entry created before December 31st 2020:
SELECT * FROM "AO_8542F1_IFJ_OBJ_HIST" 
WHERE "OBJECT_ID" = XXX;

To remove the history logs, we have 2 options, one to remove by date older than, or by specifying an object ID:

You can specify the criteria for the deletion of rows, for example - any History row/entry created before December 31st 2020:
DELETE FROM "AO_8542F1_IFJ_OBJ_HIST" 
WHERE "CREATED" < '2020-12-31 00:00:00.0';

OR

If you want to delete History per specific Object, where xxx is the Object ID of the object you wish to delete History for:
DELETE FROM "AO_8542F1_IFJ_OBJ_HIST" 
WHERE "OBJECT_ID" = XXX;

The Object ID is the numeric part of the Object Key, and will also show in the Object URL when you navigate to the Object

This is tested on PostgreSQL and may need modifying for other DBMS such as Oracle or SQL Server.

Deleting History entries will only remove the History records from the Object, no other part/functionality in Insight should be affected by removing the Object's History

If using PostgreSQL, you must run the VACUUM FULL command to avoid the deleted data to keep using the Table's space. It is most recommended when performing some huge updates, as mentioned on Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX





Last modified on Sep 14, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.