sys_audit table is too big ( need a way to clean that using script )

[RudhraKAM]

1. Can some one help me how to know which table is  have more audits ? by year by year ?,, ( I tried using report ,its timing out as our audit history is too big ) , can we do it with background script ?

Tried this but its not giving any results 

var gr= new GlideRecord("sys_audit");

gr.addEncodedQuery("sys_created_onON2013-07-28@javascript:gs.dateGenerate('2013-07-28','start')@javascript:gs.dateGenerate('2016-07-28','end')");

gr.query();

gs.print(gr.getRowCount());

and 2. fix script to delete the records which are older than july 4 2015 

delCoreCompanyRec();

function delCoreCompanyRec()
{
var gr= new GlideRecord("sys_audit");

gr.addEncodedQuery("sys_created_onON2016-07-28@javascript:gs.dateGenerate('2016-07-28','start')@javascript:gs.dateGenerate('2016-07-28','end')");

gr.setWorkflow(false); //Don't fire Business rule,notifications
gr.deleteMultiple();

}

and 3.a schedule job script to delete records which are older than 3 years 

[Prateek kumar]

please take a look at this.

https://hi.service-now.com/kb_view.do?sysparm_article=KB0724183

[Sumanth16]

In first point don't run back ground (or) fix scripts on system tables.Because those tables have large number of records,so when you run background (or) fix script ,it will reach to max transaction time then after the screen will blank out.Sometimes it causes instance down also.

So use scheduled job with limit number of records (setlimit) in scheduled job.This way you can achieve your requirement.

There is another way you can achieve that ,use database view take table name and record instead of querying all that table record ,you can simple use particular fields in database view by joining some fields.

Thanks,

Sumanth

[RudhraKAM]

can some one help me with the script .

[Prateek kumar]

There is a solution proposed in my comment. 

Did you happen to take a look at it?

https://hi.service-now.com/kb_view.do?sysparm_article=KB0724183

[RudhraKAM]

I tried that but seems like its not deleting 

var vTableName = 'u_private_ldap_user_table';
var vDaysAgo = 10;
PurgeAuditDeletedRecords(vTableName, vDaysAgo);

function PurgeAuditDeletedRecords(pTableName, pDaysAgo) {
    if ((pTableName) && (pDaysAgo)) {
        var v_encoded_query = 'sys_created_on<javascript:gs.daysAgoStart(' + pDaysAgo + ')^tablename=' + pTableName;
        var gr = new GlideRecord('sys_audit');
        gr.addEncodedQuery(v_encoded_query);
        gr.setLimit(2000);
        gr.query();
        gs.log('Deleting ' + gr.getRowCount() + ' records for ' + pTableName + ' from sys_audit_delete (and related sys_audit_relation records)', 'PurgeAuditDeletedRecords');
        while (gr.next()) {
            gr.deleteRecord();
        }
    }
}