How often are fields used on a table? Get field usage of a table.

Janel
Kilo Sage

‎03-26-2024 07:20 AM

(not a question, just passing along something helpful I made a few years ago)

 

Have you ever had someone ask you to add a custom field to a table, but you already know they aren't using the last custom field they were given?  Ever found an out-of-box field and wonder if it has been used?  Ever wondered if an old field is still used or if it can be deleted?

 

I've had those questions before. 

So I wrote a script that is meant to be run as a Fix Script to get the usage of fields from a specific table.  It generates a CSV that gets attached to the sys_user record of the person that ran the script.

It includes the following headers; Field (name), Label, Count, Percent, Table (if the field is inherited), Type, Reference (if the field is a reference, what it is a reference to), URL (to see the list of records). and total number of records on the table.

The script does have some different options of things to look at.  You can choose whether you only want to look at custom fields, if you want to include or exclude sys_ fields, out-of-box fields, etc.

 

This has helped us make decisions on whether we should remove a field or reuse it for something else.

 

NOTE: It can take awhile to run for large tables, depending on how many things you plan on looking at.  I'd recommend doing it outside of business hours and maybe just run it for one or two types of fields at a time (just in case).

 

The script below is setup to look only for custom fields on the incident table and includes fields inherited from Task where the usage count may be zero.

/* Creates a CSV of field usages on a table. Run as a fix script.  Attaches a CSV to your user profile (or click the sys_attachment rollback option) showing counts and percentages of use.  Needs the following:
 * table: Name of the table to look at.
 * filename: Name to give the outputted CSV file.
 * includeSystem true/false: Include system fields (sys_).  This cannot be done by itself and needs to include one of the next two (dunno why)
 * includeOOB true/false: Inlcude OOB fields (not start with u_ or sys_)
 * includeCustom true/false: Include custom fields (u_)
 * showExtendedZero true/false: Whether to include extended or inherited fields that have zero records against the table we're looking at.  E.G. a field that exists on task and is only used on incidents and problems, but not on requested items. */

checkFieldUsage('incident', "incident custom field usage", false, false, true, true); 
//table, filename, include system fields, include OOB fields, include custom fields, include fields extended from a parent table that have 0 records against them.



function checkFieldUsage(table, filename, includeSystem, includeOOB, includeCustom, showExtendedZero) {
    if (!table || !filename || (!includeCustom && !includeOOB && !includeSystem)) {
        gs.print("Need more values!");
        return;
    }
    /* Get the total count of records for the table */
    var gaTotal = new GlideAggregate(table);
    gaTotal.addAggregate("COUNT");
    gaTotal.query();
    gaTotal.next();
    var tableTotal = gaTotal.getAggregate("COUNT") || 0;

    if (tableTotal == 0) {
        gs.print("There are no records on the table to reivew.  Ending.");
        return;
    }

    /* Get a record to get the fields from it (and use an an object!) */
    var tableRecord = new GlideRecord(table);
    tableRecord.initialize(); //Used to get dictionary object from JSON string
    var f = tableRecord.getFields();
    var dataObj = {};

    /* Get the table fields and sort out custom, system, and OOB fields */
    var fieldList = [];
    for (var i = 0; i < f.size(); i++) {
        var fieldName = f.get(i).getName() + ""; //tableRecord[fieldName] can be used after this

        //Only pull for the things we want to look at.
        if (!includeSystem && fieldName.indexOf('sys_') == 0) { //If it starts with sys_ and we don't want it, then skip
            continue;
        } else if (!includeOOB && (fieldName.indexOf('u_') == -1 && fieldName.indexOf('sys_') == -1)) {
            continue;
        } else if (!includeCustom && fieldName.indexOf('u_') == 0) {
            continue;
        }
        var fieldObj = {};
        var fieldTable = f.get(i).getTableName() + '';
        var fTotals = getCounts(table, tableRecord[fieldName].sys_meta.type, fieldName, tableTotal);
        fieldObj.name = fieldName;
        fieldObj.label = f.get(i).getLabel() + '';
        fieldObj.count = fTotals.count;
        fieldObj.percent = fTotals.percent;
        fieldObj.table = fieldTable;
        fieldObj.type = tableRecord[fieldName].getED().getInternalType().toString();
        fieldObj.reference = f.get(i).getED().reference != null ? tableRecord[fieldName].getED().reference : '';
        fieldObj.link = fTotals.link;
        fieldList.push(fieldObj);
    } //End of For


    /* Build the CSV */
    var header = 'Field, Label, Count, Percent, Table, Type, Reference, Query,, Total Records:, ' + tableTotal + '\n';
    var data = '';
    for (var k = 0; k < fieldList.length; k++) {
        if (!showExtendedZero) { //Do not show fields from extended tables that have a count of 0
            if (fieldList[k].count == 0 && fieldList[k].table != table) {
                //gs.print("continuing");
                continue;
            }
        }
        data += fieldList[k].name + ',' + fieldList[k].label + ',' + fieldList[k].count + ',' + fieldList[k].percent + ',' + fieldList[k].table + ',' + fieldList[k].type + ',' + fieldList[k].reference + ',' + fieldList[k].link + '\n';
    }

    var csv = header + data;

    var rec = new GlideRecord("sys_user");
    rec.get(gs.getUserID());
    rec.addQuery("sys_id", gs.getUserID());
    GlideSysAttachment().write(rec, filename + ".csv", "text/csv", csv);


    /* To get the usage by field, we query the table and look for records where the field we're checking on isn't blank/false/0.  Then we return the results for the JSON obejct. */
    function getCounts(table, fType, fName, grandTotal) {
        var countObj = {
            'count': 0,
            'percent': 0,
            'link': ''
        };
        var gaFCount = new GlideAggregate(table);
        gaFCount.addAggregate("COUNT");
        if (fType == -7) { //Boolean fields, check how many are marked as true
            gaFCount.addQuery(fName, true);
        } else if (fType == 4) { //Integer fields, check how many have a value more than 0
            gaFCount.addQuery(fName, '!=', 0);
        } else {
            gaFCount.addNotNullQuery(fName);
        }
        gaFCount.query();
        if (gaFCount.next()) {
            var usage = gaFCount.getAggregate("COUNT");
            countObj.count = usage;
            countObj.percent = (usage > 0) ? Math.floor(usage / grandTotal * 100) : 0; //Nobody likes dividing by zero
            countObj.link = 'https://' + gs.getProperty('instance_name') + '.service-now.com/' + table + '_list.do?sysparm_query=' + gaFCount.getEncodedQuery();
        } //End of field counting
        return countObj;
    }
} //End of big function

 

Hope this helps!

  • 741 Views
0 REPLIES 0