How to make a report from or similar to background script output?

[Robert Campbell]

I have this script that gives the results that I want but I want to put this into a report.  I'd like to group by these different groupings I have.  I will also want to group by an additional column "Audit" and maybe "created by" as well as "Date" as mentioned in the script.  How do I use this in the report module and allow for the additional group by?

var ce = new GlideAggregate('cert_element'); // Identify the table to work with
var date = '2022-12-09'; // One variable I'd want to use in the report.  The other variable would be "Audit".

ce.addAggregate('COUNT', 'configuration_item'); 
ce.addQuery('sys_created_on', '>=', date); 
ce.orderBy('configuration_item'); 
ce.orderBy('state'); 
ce.groupBy('configuration_item'); 
ce.query(); 

const failedci = []; // Initialize the arrays
const pendingci = []; 
const certifiedci = []; 
const pending = []; 
const certified = [];
const cert = [];

// Build the arrays

while(ce.next()){
  if(ce.state == 'Failed'){
    failedci = failedci.push(ce.configuration_item.name);
  }
  if(ce.state == 'Pending'){
    pendingci = pendingci.push(ce.configuration_item.name);
  }
  if(ce.state == 'Certified'){
    certifiedci = certifiedci.push(ce.configuration_item.name);
  }
}

// Compare and filter the arrays

for(var i=0; i < pendingci.length; i++){
  if(failedci.indexOf(pendingci[i]) == -1){
    pending.push(pendingci[i]);
  }
}
for(var i=0; i<certifiedci.length; i++){
  if(failedci.indexOf(certifiedci[i]) == -1){
    cert.push(certifiedci[i]);
  }
}
for(var i=0; i<cert.length; i++){
  if(pending.indexOf(cert[i]) == -1){
    certified.push(cert[i]);
  }
}

// Print results

gs.print('======================================================= FAILED ============================================================');
gs.print(failedci.join('\r\n'));
gs.print('======================================================= PENDING ============================================================');
gs.print(pending.join('\r\n'));
gs.print('======================================================= CERTIFIED ============================================================');
gs.print(certified.join('\r\n'));

I would like to be able to use the data in a bar, pie, histogram, list report.  I tried to run another while loop but that didn't work.

for(i=0; i<certified.length; i++){ // Loop through the new array
  while(ce.next()){
    if(ce.configuration_item.name.toString() == certified[i]){ // Compare each ci in the new array to the one from the table and where there's a match, print the other values
      gs.print('Certified\t' +certified[i]+ '\t' +ce.Assigned_to+ '\t' +ce.Audit); // Nothing is printing.  Unable to use while(ce.next()) again.
    }
  }
}

In the end, I would like to be able to click on the data in the report and access that record.  I forgot to add Task.

There is probably a better, more efficient way to have coded this but this is my current skill set.  Hopefully this community can help me improve 😁

[Yousaf]

Hi Robert,
From what I understand you are syaing that you have a code that works fine but now you want to call this in the report 
You can write this code in script include and call that script include and function in report. Correct me if I understood wrong.

Mark Correct and Helpful if it helps.

[Robert Campbell]

That is correct.  But I guess "works fine" needs some clarification.  I get the data (for the most part) that I want to display (I've updated the script).

var ce = new GlideAggregate('cert_audit_result'); // Identify the table to work with 
var date = '2022-12-09'; // One variable I'd want to use in the report.  The other variable would be "Audit". 
  
ce.addAggregate('COUNT', 'configuration_item');  
ce.addQuery('sys_created_on', '>=', date);  
ce.orderBy('configuration_item');  
ce.orderBy('state');  
ce.groupBy('configuration_item');  
ce.query();  
  
const failedci = [{state: '', ci: '', assigned_to: '', audit: ''}]; // Initialize the arrays 
const pendingci = [{state: '', ci: '', assigned_to: '', audit: ''}];  
const certifiedci = [{state: '', ci: '', assigned_to: '', audit: ''}];  
const pending = [{state: '', ci: '', assigned_to: '', audit: ''}];  
const certified = [{state: '', ci: '', assigned_to: '', audit: ''}]; 
const cert = [{state: '', ci: '', assigned_to: '', audit: ''}]; 
  
// Build the arrays 
  
while(ce.next()){ 
  if(ce.state == 'Failed'){ 
    failedci = failedci.push(ce.state,ce.configuration_item.name,ce.configuration_item.managed_by.name,ce.audit); 
  } 
  if(ce.state == 'Pending'){ 
    pendingci = pendingci.push('' +ce.state,ce.configuration_item.name,ce.configuration_item.managed_by.name,ce.audit); 
  } 
  if(ce.state == 'Certified'){ 
    certifiedci = certifiedci.push('' +ce.state,ce.configuration_item.name,ce.configuration_item.managed_by.name,ce.audit); 
  } 
} 
  
// Compare and filter the arrays 
  
for(var i=0; i < pendingci.length; i++){ 
  if(failedci.indexOf(pendingci[i]) == -1){ 
    pending.push(pendingci[i]); 
  } 
} 
for(var i=0; i<certifiedci.length; i++){ 
  if(failedci.indexOf(certifiedci[i]) == -1){ 
    cert.push(certifiedci[i]); 
  } 
} 
for(var i=0; i<cert.length; i++){ 
  if(pending.indexOf(cert[i]) == -1){ 
    certified.push(cert[i]); 
  } 
} 
  
// Print results 
  
gs.print('======================================================= FAILED ============================================================'); 
gs.print(failedci.join('\r\n')); 
gs.print('======================================================= PENDING ============================================================'); 
gs.print(pending.join('\r\n')); 
gs.print('======================================================= CERTIFIED ============================================================'); 
gs.print(certified.join('\r\n')); 
 

1. How do I use each one of those values (state, ci, managed_by, audit) in a report.  
2. Audit never returns a value.
3. State only returns a value for Failed unless I update the array push to array.push('' +ce.state,ce.configuration_item.name,ce.configuration_item.managed_by.name,ce.audit); for the other 2 arrays.

Once I get all 4 values to show, how do I put that in an script include and reference them in a report so that it shows each element as a value because since I can't get the sys_id, I think I need to set the report up with the following conditions:

• ci is ce.configuration_item
• AND state is ce.state
• AND assigned_to is ce.configuration_item.managed_by
• AND audit is ce.audit (If I can ever get this to work)

[Rachel Gomez]

In ServiceNow, you can get around using background scripts by using these other methods

Manually creating, updating, or deleting records

Using the List Editor

Importing Data to create, update, or delete records

However, that is not the most efficient way to update data in cases.  It might not be feasible to use the list editor to update 200K records or make a csv file to update the records.  Sometimes a background script can be created in minutes that solves the issue.

Regards,

Rachel Gomez

[Robert Campbell]

I'm only trying to report on records in a way that ServiceNow doesn't by default.  I'm trying to create a report but I don't know how to properly identify the records because sys_id is not available it seems.