SYS Audit Table Management & Best Practices

[cloudboy]

Hello ,

We are facing issues of Activity stream loading while opening any record from the list view, during our analysis we found that there are huge entries in sys_audit table and its loading take huge time, approx > 20K records are for one table and corresponding doc key, i have written below fix script which executes in 3 hrs , for finding the huge entries also for 1 record deletion from sys audit took 2 hours. Would like to know whats the best practice to fix the problem here..

var threshHoldAudit=3000; 

var agg = new GlideAggregate('sys_audit');

agg.addAggregate('count');

agg.addQuery("tablename","tsp1_project")

agg.groupBy('documentkey');

agg.query();

while (agg.next()) {

var currAggVal = agg.getAggregate('count');

if(currAggVal > threshHoldAudit)

gs.info('#WARNING: Audit for docKey ' + agg.documentkey + ' is having = ' + currAggVal + " Records");

}

What should be the min entries in sys audit table for specific dockey/sys_id and how we can delete/purge them periodically?

Thanks.

[Deepak Ingale1]

It seems to be either the problem with

1) Number of fields on record + Number of times record is getting updated

Lets say you have 20 fields, and record is getting updated 5 times, then system creates 20  * 5 = 100 entries in audit table assuming that all the fields are being audited for this table

Now since you want to delete the audit, then it raises the question on " do you want to really audit all the fields on this table ? ", there might be some fields which might be getting update frequntly but does not necessarily meant to be audited, you can exclude those fields by addiing "no_audit=true" attribute on the dictionary entry of that particular field.

2nd option would be to modify the sysstem property for activity on that table based on which record shows the fields in activity log.

I would get into RCA of the problem rather than DELETING the records from audit table

Note: Please mark reply as correct / helpful if it answered your question

[cloudboy]

Hi Deepak,

We have couple of financial fields which require audit also its related with timesheets every week data is updated for project & life cylce of project is large as compared to incidents etc. Also other users update the data manually..

So as you suggest another option is to control via system property but i am not able to find the exact property which limit activity stream.

Also looking for a purging policy.

Thanks.

[Deepak Ingale1]

Hello,

If you put filter criteria as name starts with glide.ui.<table_name>, you should be able to see the property glide.ui.<table_name>_activity.fields, in below screenshot, its 2nd from top which say what fields should be made available for activity stream. If you take out the "most annoying" field from this, you should be fine, field will still be audited, but it wont show up in activity stream and wont cause load time issue.

[cloudboy]

Thanks Deepak, for incident table i can see it but for tsp1_project/pm_project tables i can't find it (entries are there in audit table).