Displaying user names from failed data certification policy tasks

[markdesouza]

Hello,

I'm creating a report on the CMDB Certification Attribute Status [sn_cmdb_ws_dm_certification_attribute_status] table to show failed cmdbtasks for the last 6 months.  I'm able to pull element, original value, and certified value in and successfully display text based information but the vendor manager (vendor_manager) and contract owner (u_contract_owner) fields are reference fields to the user table.  Original and certified value fields are string fields so I can't convert the sys_ids to the display values.  I've tried the following script include and business rule:

Script include:

var UserNameDisplayHelper = Class.create();
UserNameDisplayHelper.prototype = {
    initialize: function() {},
    
    getUserDisplayName: function(sys_id) {
        var user = new GlideRecord('sys_user');
        if (user.get(sys_id)) {
            return user.getDisplayValue();
        }
        return '';
    },
    
    type: 'UserNameDisplayHelper'
};

business rule:

var userNameHelper = new UserNameDisplayHelper();
var gr = new GlideRecord('sn_cmdb_ws_dm_certification_attribute_status');
gr.query();
while (gr.next()) {
    var originalValueDisplay = gr.original_value;
    var certifiedValueDisplay = gr.certified_value;
    
    if (gr.element == 'vendor_manager' || gr.element == 'u_contract_owner') {
        originalValueDisplay = userNameHelper.getUserDisplayName(gr.original_value);
        certifiedValueDisplay = userNameHelper.getUserDisplayName(gr.certified_value);
    }
    
    // Use originalValueDisplay and certifiedValueDisplay in the report output
    // Example: gs.print('Original Value: ' + originalValueDisplay + ', Certified Value: ' + certifiedValueDisplay);
}

But my report does not update.  Any suggestions would be appreciated. 

[folusho]

• Reports in ServiceNow pull raw data from fields and do not run server-side scripts like Business Rules or Script Includes during execution.

• Your original_value and certified_value fields are strings, and even though they may store sys_ids of users, the report doesn't know how to convert them to display values.

• Business Rules do not modify what’s shown in list reports or dashboards unless you store the converted values in a field.

My suggestion would be to create two new string fields to sn_cmdb_ws_dm_certification_attribute_status:

1. u_original_user_display

2. u_certified_user_display   

   Then use scheduled job or fix script to populate the display names:

var helper = new UserNameDisplayHelper();
var gr = new GlideRecord('sn_cmdb_ws_dm_certification_attribute_status');
gr.addQuery('sys_created_on', 'ONLast6months@javascript:gs.beginningOfLast6Months()@javascript:gs.endOfLast6Months()');
gr.query();
while (gr.next()) {
    if (gr.element == 'vendor_manager' || gr.element == 'u_contract_owner') {
        gr.u_original_user_display = helper.getUserDisplayName(gr.original_value);
        gr.u_certified_user_display = helper.getUserDisplayName(gr.certified_value);
        gr.update();
    }
}

[markdesouza]

Thanks Folusho,

We know that this is available but generally don't want to create any custom fields.  But let me check in with my team and see if this would work as an exception case.