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

var sLogSource = 'fixScr:largeOldTables'; gs.log('script start', sLogSource); var gr = new GlideRecord('sys_dictionary'); gr.addEncodedQuery('internal_type.label=Collection'); //gr.addQuery('element', 'sys_updated_on'); gr.query(); 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)) { if (tableCount > 25000) { var tableCount = gr1.getAggregate('COUNT'); var luDate = new GlideDateTime(lastUpdated); if (luDate < gs.monthsAgoStart(6)) { gs.log('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.log('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

Popular posts from this blog

GlideRecord setValue

URL link in addInfoMessage