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

abhi710 · ‎04-25-2023

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 · ‎04-26-2023

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:

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.
Use a try-catch block to handle any potential exceptions that may occur during the execution of the function.
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 [];
  }
};

Was this answer helpful?

Please consider marking it correct or helpful.

Your feedback helps us improve!

Thank you!

Regards,

Amit Gujrathi

View solution in original post

Rao Vamshi · ‎04-25-2023

Hi @abhi710,

Are you trying to return valid JSON data by using this function?

Thanks,
Vamshi

abhi710 · ‎04-25-2023

Hi Vamshi,

Yeah, it is returning the required sys_id in background script; but not working in reports. Is there any restriction of using json parser while calling the function reports?

Thanks,

Abhi

Rao Vamshi · ‎04-26-2023

Hi @abhi710

There should be no restriction on using a json parser in a report as long as the function has the appropriate scope and permissions to access the necessary data and resources.

It is possible that there may be some differences in how the json parser is used in a report versus a background script. One thing to check is whether the json data is being returned correctly in the report, you can use console.log or gs.info messages to output the json data to the log and verify that it is being parsed correctly. Another thing to check is whether the function is being called with the correct parameters and in the correct context, just ensure that any variables or objects that the function relies on are available in the report context.

Thanks,
Vamshi

Ankur Bawiskar · ‎04-26-2023

@abhi710

I have faced challenges using GlideAggregate in client callable script include when called from Reporting

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader