ServiceNow display list of large tables where last record updated > 6 months ago

code snippet - modify filter parameters as needed (STARTSWITH query could also be changed to LIKE)

var sLogSource = 'fixScr:largeOldTables';

gs.print('script start', sLogSource);

 

var sizeCheck= 100;

var monthsAgoCheck=6;

var tableNameMatch='cmdb';

 

var gr = new GlideRecord('sys_dictionary');

gr.addEncodedQuery('internal_type.label=Collection');

gr.addEncodedQuery('nameSTARTSWITH' + tableNameMatch);

gr.query();

gs.print('TOTAL TABLES TARGETTED: ' + gr.getRowCount());

while (gr.next()) {

    var tableName = gr.name.toString();

    try {

        var gr1 = new GlideAggregate(tableName);

        gr1.addAggregate('COUNT');

        gr1.query();

        if (gr1.next()) {

            var lastUpdated = getLatestRecord(tableName, sLogSource)+"";

            if (!gs.nil(lastUpdated)) {

                var tableCount = gr1.getAggregate('COUNT');

                if (tableCount > sizeCheck) {

                   

                    var luDate = new GlideDateTime(lastUpdated);

                    if (luDate < gs.monthsAgoStart(monthsAgoCheck)) {

                        gs.print('Table: ' + tableName + ' has: ' + tableCount + ' records, [table last updated: ' + lastUpdated + ']', sLogSource);

                    }

                }

            }

        }

    } catch (err) {

        gs.logError("We've got an error for table: " + gr.name.toString() + "- " + err.toString(), sLogSource);

        //break;

        continue;

    }

}

gs.print('script end', sLogSource);

function getLatestRecord(tableName, logsource) {

    var dReturn = '';

    try {

        var gr = new GlideRecord(tableName);

        gr.orderByDesc('sys_updated_on');

        gr.setLimit(1);

        gr.query();

        if (gr.next()) {

            if (!gs.nil(gr.sys_updated_on)) {

                dReturn = gr.getValue("sys_updated_on");

                //gs.log(tableName+'::TABLE LAST UPDATED:' + dReturn,logsource);

            }

        }

    } catch (ex) {}

    return dReturn;

}

}