Can json parser be used in script include function and call it in reports?

[abhi710]

I have a written a function in script include to list all the duplicate records from table. 

Tried to call the script include function in background script, got the duplicated records sys_id but when I tried to call the same function in report, it is not working and shows sys_id in null.

Below is the code:

sampleFunction:function() {

var sys_arr1 = [];
var dupRecords = [];
var duplicateCheck = new GlideAggregate('u_cmdb_ci_environment');
duplicateCheck.addNotNullQuery('u_gbl_apm_business_application');
duplicateCheck.addNotNullQuery('u_gbl_apm_provider');
duplicateCheck.addNotNullQuery('u_gbl_apm_type');
duplicateCheck.groupBy('u_gbl_apm_business_application');
duplicateCheck.groupBy('u_gbl_apm_provider');
duplicateCheck.groupBy('u_gbl_apm_type');
duplicateCheck.addHaving('COUNT', '>', 1); // addHaving func won't work in scope app
duplicateCheck.query();
while (duplicateCheck.next()) {
var jsonObj = {}; // declare a json object
jsonObj['u_gbl_apm_business_application'] = duplicateCheck['u_gbl_apm_business_application'].toString();
jsonObj['u_gbl_apm_provider'] = duplicateCheck['u_gbl_apm_provider'].toString();
jsonObj['u_gbl_apm_type'] = duplicateCheck['u_gbl_apm_type'].toString();
dupRecords.push(jsonObj);
}

var jsonString = JSON.stringify(dupRecords); // convert json object to string

var parser = new JSONParser();
var parsedData = parser.parse(jsonString);
var length = parsedData.length;

for (var i = 0; i < length; i++) {

var encodedQuery = 'u_gbl_apm_business_application' + '=' + parsedData[i]['u_gbl_apm_business_application'] + '^' + 'u_gbl_apm_provider' + '=' + parsedData[i]['u_gbl_apm_provider'] + '^' + 'u_gbl_apm_type' + '=' + parsedData[i]['u_gbl_apm_type'];

var tableRec = new GlideRecord('u_cmdb_ci_environment');
tableRec.addEncodedQuery(encodedQuery);
tableRec.query();
while (tableRec.next()) {
sys_arr1.push(tableRec.getUniqueValue());
}
}
// sys_arr1 = sys_arr1.toString();
//gs.log('sys_arr1' + sys_arr1);
//return sys_arr1.join();

return sys_arr1;

},

Please refer the below screenshots for reference.

Will this json parser work when we call this function in report?

[Amit Gujarathi · Accepted solution]

Hi @abhi710 ,
I trust you are doing great.

It may be helpful to check whether the function is being called correctly in the report and whether the input parameters are being passed correctly.

In addition, it is worth noting that the addHaving() function may not work within a scoped app, as mentioned in the code comments. Therefore, an alternative approach may be needed to identify the duplicates in the report.

Regarding the code provided, it seems to be working fine in the background script, but it may be helpful to consider the following suggestions to improve the code:

1. Use GlideRecord instead of GlideAggregate to query the table. GlideAggregate is used when aggregate functions (such as SUM, COUNT, etc.) are needed, but in this case, GlideRecord may be sufficient.

2. Use a try-catch block to handle any potential exceptions that may occur during the execution of the function.

3. Remove the unnecessary conversion of the sys_arr1 array to a string and then back to an array.

var sampleFunction = function() {
  var sys_arr1 = [];
  var dupRecords = [];
  
  try {
    var tableRec = new GlideRecord('u_cmdb_ci_environment');
    tableRec.addNotNullQuery('u_gbl_apm_business_application');
    tableRec.addNotNullQuery('u_gbl_apm_provider');
    tableRec.addNotNullQuery('u_gbl_apm_type');
    tableRec.query();
  
    var values = {};
    while (tableRec.next()) {
      var key = tableRec.u_gbl_apm_business_application.toString() + '|' + tableRec.u_gbl_apm_provider.toString() + '|' + tableRec.u_gbl_apm_type.toString();
      if (values[key] === undefined) {
        values[key] = [];
      }
      values[key].push(tableRec.getUniqueValue());
    }
  
    for (var key in values) {
      if (values[key].length > 1) {
        var parts = key.split('|');
        var jsonObj = {
          'u_gbl_apm_business_application': parts[0],
          'u_gbl_apm_provider': parts[1],
          'u_gbl_apm_type': parts[2]
        };
        dupRecords.push(jsonObj);
        sys_arr1 = sys_arr1.concat(values[key]);
      }
    }
  
    return sys_arr1;
  } catch (ex) {
    gs.error('Error in sampleFunction: ' + ex);
    return [];
  }
};