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;
}
Comments
Post a Comment