duplicate short description

[eashwar]

I am trying to run a script to pull incidents with duplicate short description.

Not sure whats wrong with below script.

var count=0;

var myIncidents=[];

var incidents= new GlideRecord("incident");

incidents.addQuery('sys_created_on', '>=', '2016-01-20 00:00:00');

incidents.addQuery('caller_id', '!=', 'ClarifySN1 Integration');

incidents.addAggregate('COUNT');

incidents.addHaving('COUNT', '>=', '2');

incidents.groupBy('short_description')

incidents.query();

gs.print('grGroup Query : ' + incidents.getEncodedQuery() + ' = ' + incidents.getRowCount());

while (incidents.next()) {

gs.print(incidents.number.toString());

}

[manikorada]

In the line :

incidents.addQuery('caller_id', '!=', 'ClarifySN1 Integration');

caller_id is a reference field and you need to have a sys_id for comparision

[Mike Allen]

When I did this:

var incidents= new GlideAggregate("incident");

incidents.addAggregate('COUNT', 'assigned_to');

incidents.orderByAggregate('COUNT', 'assigned_to');

incidents.addHaving('COUNT', '>=', '1');

incidents.query();

var records = [];

while(incidents.next()){

        var user = incidents.assigned_to.name;

        var count = incidents.getAggregate('COUNT', 'assigned_to');

        records.push(user + ' - ' + count + '\n');

}

gs.print(records);

I got:

Script: - 41

,Courtney Shishido - 6

,Alex Linde - 1

,Alishia Sergi - 1

,Alli Hichard - 1

I think the key is the GlideAggregate instead of the GlideRecord you had.

[Brian Dailey1]

Hi Eashwar,

I'm going to assume you're trying to pull the actual records to do something to them (update, delete, etc.).   I think the problem is you are trying to use the method addAggregate() on a GlideRecord, which does not have that method.

See refs here:

GlideRecord - ServiceNow Wiki

GlideAggregate - ServiceNow Wiki

Duplicate Record Scripts

Here is code I've adapted from the Duplicate Record Scripts (above) to pull all duplicate records based on a certain field and then do something with them:

getDuplicates();

      function getDuplicates() {  

              //Get your list of duplicated values, here we're looking at 'u_category' on records in [incident]

              var dupRecords = [];  

              var gaDupCheck1 = new GlideAggregate('incident');  

              gaDupCheck1.addAggregate('COUNT', 'u_category');  

              gaDupCheck1.addNotNullQuery('u_category');  

              gaDupCheck1.groupBy('u_category');  

              gaDupCheck1.addHaving('COUNT', '>', 1);  

              gaDupCheck1.query();  

              while (gaDupCheck1.next()) {  

                      dupRecords.push(gaDupCheck1.u_category.toString());  

              }  

              gs.print(dupRecords);  

              //Query your table for any record with a value matching one of the duplicated values

              var grDupes = new GlideRecord('incident');

              grDupes.addQuery('u_categoryIN' + dupRecords);

              grDupes.orderBy('u_category');

              grDupes.query();

              //Process the duplicated records

              var lastDuplicateValue = "";

              while(grDupes.next()){

                      // This is important if you want to keep the first record of each set of duplicates

                      if(grDupes.u_category.toString() != lastDuplicateValue){

                              lastDuplicateValue = grDupes.u_category.toString();

                              gs.print("Saving: " + grDupes.number);

                              continue;

                      }

                      // Here is where you would insert code to update/delete the record

                      //...

                      //grDupes.update();

                      gs.print("Deleting: " + grDupes.number);

              }

      }

Give that a try and see how if it meets your requirements.   You'll need to change table/field names to match your purposes.

Thanks,

-Brian