Find out how much a field on a table is actually used

[OlaN]

More than once I've encountered an instance that has multiple custom fields added to the OOB tables,

and that's usually fine and works perfectly good...

But given enough time these custom fields might not be used anymore, for various reasons, and the person behind requesting the additional field might no longer work in the company, so there's no one to ask why the field got there in the first place, and what purpose it serves.

So, an easy way out in many cases is only to remove the field from the form view (and list view), but leaving the dictionary entry as is.

Using unnecessary space.

So I've created a little script for anyone to use,

if you want to find out which fields on a table that's rarely used.

(yes, I'm a big fan of keeping an instance clean and tidy 😀)

The script below can be run directly in "Scripts - Background" or, if you prefer, you can download the attachment as a Fix script you can import to your instance.

Happy cleaning!

var tableName = 'incident'; // tablename to check
var fieldName = 'cmdb_ci,business_service'; // commaseparated list of fields to check
var timeInterval = 'sys_created_onONLast 6 months@javascript:gs.beginningOfLast6Months()@javascript:gs.endOfLast6Months()';

calculatePercentUsed(tableName, fieldName, timeInterval);


/**************************************************/

function calculatePercentUsed(table, field, time){

	var countAll = new GlideAggregate(table);
	if (time){
		countAll.addEncodedQuery(time);
	}
	countAll.addAggregate('COUNT');
	countAll.query();
	var allRecords = 0;
	if (countAll.next()){
		allRecords = countAll.getAggregate('COUNT');
		gs.info('All records [' + table + ']: ' + allRecords);

	}
	var arrayFields = field.split(',');
	for (var i=0; i<arrayFields.length; i++){

		var countEmpty = new GlideAggregate(table);
		countEmpty.addAggregate('COUNT');
		countEmpty.addEncodedQuery(arrayFields[i] + 'ISNOTEMPTY');
		if (time){
			countEmpty.addEncodedQuery(time);
		}
		countEmpty.query();
		var emptyRecords = 0;
		if (countEmpty.next()){
			emptyRecords = countEmpty.getAggregate('COUNT');
			gs.info('Records using field [' + arrayFields[i] + ']: ' + emptyRecords);
		}
			
		if (allRecords != 0){
			var percentUsed = parseFloat(emptyRecords/allRecords);
			gs.info('Percent used: ' + (percentUsed*100).toFixed(2) );
		}

	}
	if (allRecords == 0){
		gs.info('No records found');
	}

}

[-levi]

Hi,

This is interesting to see indeed 🙂 

You could make it slightly easier with GlideQuery which uses a simple counter GlideAggregate in the background and also checks if your query is correctly formatted.

var tbl = 'incident';
var flds = 'cmdb_ci,business_service';
var qry = 'sys_created_onONLast 6 months@javascript:gs.beginningOfLast6Months()@javascript:gs.endOfLast6Months()';



calculatePercentageUsed(tbl,flds,qry)


function calculatePercentageUsed(tbl,flds,qry){
	
	
	var emptyRec = 0;
	var filledRec = 0;
	var allRec = 0;
	var percRec;
	
	var fldsArr = flds.split(',');
	
	for (var i=0; i< fldsArr.length; i++) {
 
		
		emptyRec = new GlideQuery.parse(tbl,qry + '^' + fldsArr[i] + 'ISEMPTY').count();
		allRec = new GlideQuery.parse(tbl,qry).count();
        filledRec = allRec - emptyRec;
		percRec = (filledRec / allRec * 100).toFixed(2);
 
		gs.info('field ' + fldsArr[i] + '; ' + emptyRec + ' empty records' + '; ' + filledRec + ' filled records' + '; ' +  percRec  +' percentage filled records');
		
 
}
}