Sys Admin useful Service Portal widget to check for duplicates in tables

Widget defined in global scope; note can set cross scope privileges for access to scoped tables
[- should generate entries in sys_restricted_caller_access which can then be set to permitted]

HTML:

<title>Check ServiceNow Table for duplicates</title>
<div class='container'>
<!-- your widget template -->
  <label>Enter table name here</label>
    <input value ='sys_user' id='tbl_name'>
  <br/>
  <label>Enter field name here</label>
    <input value='email' id='dupl_field'>
  <br/>
  <label>Custom query string</label>
    <input value='active=true' id='custom_query'>
  <br/>
  <br/><br/><button ng-click="runtheoperation()" class="btn btn-danger action-btn pull-left">Click to check dupl</button>
<br/><br/>
   <label>results</label><br/>
   <textarea id='results_q' rows="10" cols="100">{{c.duplrecordsList}}</textarea>
</div>
<h1>Total list:{{c.duplrecordsSize}}</h1>

CSS:

.container {
  clear: both;
  text-align:left;
  align-content: left;
}

.container input {
  width: 100px;
  clear: both;
}

.container label {
  width: 150px;
  clear: both;
}

.container textarea {
  color:black;
}

CLIENT:

function($scope) {
  /* widget controller */
  var c = this;
    c.duplrecordsSize=0;
    //c.duplrecordsList=[];
    c.duplrecordsList='';
   
    $scope.runtheoperation = function() {
        //console.log("message", 'run the op');
        c.server.get({
               action: "get_dupls",
             msg : "Running glideaggregate to check for duplicates...",
               sn_table : angular.element($('#tbl_name')).val(),
               duplfield : angular.element($('#dupl_field')).val(),
               customQuery : angular.element($('#custom_query')).val()
           }).then(function(r){
              // alert('SIZE: ' + res.data.duplrecordsSize);
              //alert('test2: ' + r.data.duplrecordsSize);
              c.duplrecordsSize=r.data.duplrecordsSize;
              var duplData=r.data.duplrecordsList;
              c.duplrecordsList=duplData.join('\n');
             
        });
     
  };

}

SERVER:

(function() {
  /* populate the 'data' object */
  /* e.g., data.table = $sp.getValue('table'); */
    data.duplrecordsList=[];
    data.duplrecordsSize=0;
    if (input && input.action === "get_dupls"){
        data.duplrecordsList=(checkDuplUsers(input.customQuery, input.duplfield,input.sn_table));
       
      data.duplrecordsSize=data.duplrecordsList.length;
    }
function checkDuplUsers(customQuery, fieldParam, table){
  var ga= new GlideAggregate(table);
  ga.addAggregate('COUNT', fieldParam);
  if (customQuery){
    ga.addQuery(customQuery);
  }
  ga.addHaving('COUNT', '>', 1);
  ga.query();
    var dupls=[];
    dupls.push("The following " + ga.getRowCount() + " users are duplicate on " + fieldParam);
  while (ga.next()) {
        var DuplLine=ga.getAggregate('COUNT', fieldParam) + " => " + ga.getElement(fieldParam);
     dupls.push(DuplLine);
 }
    //gs.log(dupls,'duplWidget_arr');
    return dupls;
}
})();

Comments

Popular posts from this blog

Running transform maps asynchronously

Post a command to the ECC queue for the mid server to initiate a powershell file copy

GlideRecord setValue