Creative way to generate a csv/excel report for all custom tables (ua_custom_table_inventory). This can be very useful to help identify things that can be cleaned up, such as unused custom tables. For this solution, I kept it simple and created a fix script to generate a csv file, but obviously there are other ways to implement. Once you run the fix script, it attaches a csv file to the fix script record so you can easily download the file.
Information includes:
Table Name
Application Scope
Number of Records in Table
Time Stamp of Last Record.
Create a Fix Script
(This should go without saying, always test in sub-prod first)
Name: Custom Table Tracker
Script:
var headers = ["Table Name", "Application", "Record Count", "Latest Record"];
var fileName = new GlideDateTime().toString() + '_CustomTables.csv';
var csvData = '';
for (var i = 0; i < headers.length; i++) {
csvData = csvData + '"' + headers[i] + '"' + ',';
}
csvData = csvData + "\r\n";
var customTable = new GlideRecord('ua_custom_table_inventory');
customTable.query();
while (customTable.next()) {
var counter = aggregateCount(customTable.getDisplayValue('table_name'));
var date = getLatestRecord(customTable.getDisplayValue('table_name'));
csvData = csvData + '"' +
customTable.getDisplayValue('table_name') + '",' +
'"' + customTable.getDisplayValue('app_name') + '",' +
'"' + counter + '",' +
'"' + date + '"';
csvData = csvData + "\r\n";
}
//attach the file to this record.
var grRec = new GlideRecord("sys_script_fix");
grRec.addQuery("name", 'Custom Table Tracker'); // Note this should be the name of this Fix Script
grRec.query();
if (grRec.next()) {
var grAttachment = new GlideSysAttachment();
grAttachment.write(grRec, fileName, 'application/csv', csvData);
}
function aggregateCount(table) {
var gaCount = new GlideAggregate(table);
gaCount.addAggregate('COUNT');
gaCount.query();
var count = 0;
if (gaCount.next()) {
count = gaCount.getAggregate('COUNT');
return count;
}
return count;
}
function getLatestRecord(table) {
var latestDate = '';
var latest = new GlideRecord(table);
latest.setLimit(1);
latest.orderByDesc('sys_created_on');
latest.query();
if (latest.next()) {
latestDate = latest.sys_created_on;
return latestDate;
}
return latestDate;
}
MyServiceNowStuff.com 