Insight as large Asset Management has to have retention policy

 Hello community, 

 

Today, I would like to share a small snippet sql request to cleanup Insight object histories. 

In the future, I will be happy to see the configuration in the Insight app as a retention policy. (PleaseAtlassian provide retention policy feature in Insight)

 

We have 2mln+ objects in Insight, not so far, at the moment we do a cleanup and review existing potential weaknesses and improve performance of our Jira Service management DC setup. 

Once our web navigation Insight tab was slow, If we do cleanup of history it starting to be fast then do review of records. 

image.png

Every night our system syncs up with SAP, IdP and other external systems, also, event based sync up and updates objects from remote systems. 

Therefore potential weakness is duplicated history, because change log we don’t need to keep longer than 3 months, as change log keeps on master systems. 

Also, the next query helpful to find misconfigs. 

SELECT hist."ID",
hist."AFFECTED_ATTRIBUTE",
count(hist."ID") updates
FROM "AO_8542F1_IFJ_OBJ_HIST" hist
GROUP BY hist."ID", hist."AFFECTED_ATTRIBUTE"
ORDER BY updates DESC;

image.png


You can find here the query in git. 

 

As a technique, I just remove via next query, of course, it’s useful. 

SELECT count("ID")
FROM "AO_8542F1_IFJ_OBJ_HIST" hist
WHERE
"CREATED" < NOW() - INTERVAL '3 months' ;

Then if scope is quite ok, you can replace select into delete to delete rows. 

Please, do before on test env, if you don’t understand the purpose and please, do backup.

Detailed info you can find here.



Please, Atlassian provide to us in Insight app the retention policy of history records. It helps to easily work with navigation in the Insight tab. 

Have a good cleanup.

Cheers,

Gonchik Tsymzhitov

Comments

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. I will share it with my other friends as the information is really very useful. Keep sharing your excellent work. Read more info about Customer portal reports Jira

    ReplyDelete

Post a Comment

Popular posts from this blog

Let's check installed apps dependency to be compliant for OWASP Dependency-Check

Jira: Housekeeping or continuous cleanup of the Agile activity artifacts

Atlassian Community, let's collaborate and provide stats to vendors about our SQL index usage