Use a script to find any "duplicate" records within your ServiceNow application.
Why would you need this?
One example might be duplicate user ids. Users may have duplicate user ids if the Transform Map is coalesced off another field other than User ID. This happens in corporate environments with multiple company mergers/partnerships where AD directories and user name rules are not combined or determined yet.
Other times there might be a mistake in an import, discovery issue, customization mistake, or maybe you just need to find this out for a report. Regardless of the reason to find a "duplicate" record, these scripts may assist you.
Find Duplicate Records with a Background Script
What I do to check for "duplicate" records most often is just to run a quick background script. This works great for testing your imports or helping to find a permanent fix to an existing issue.
1. Find Duplicate Users by User Name
gs.print(getDuplicates('sys_user','user_name'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}2. Find Duplicate GRC Controls by Control Id
gs.print(getDuplicates('grc_control','control_id'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}3. Find Duplicate CIs by Serial Number
gs.print(getDuplicates('cmdb_ci_server','serial_number'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}4. Find Duplicate CIs by IP Address
gs.print(getDuplicates('cmdb_ci_server','ip_address'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}Find Duplicate Records with a Left Navigation Bar Link or Report
If you have a reoccurring duplicate record issue in your system, you can add a Left Navigation Bar link or a Report
1. Script Includes
Name: getDuplicateUsers
Client Callable: true
Return an array of
Script:
function getDuplicateUsers() {
var dupRecords = [];
var gaDupCheck1 = new GlideAggregate('sys_user');
gaDupCheck1.addQuery('active','true');
gaDupCheck1.addAggregate('COUNT', 'user_name');
gaDupCheck1.addNotNullQuery('user_name');
gaDupCheck1.groupBy('user_name');
gaDupCheck1.addHaving('COUNT', '>', 1);
gaDupCheck1.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}2. Module
Name: Active Duplicate User IDs
Application: user admin
Roles: admin
Order: 1200
Link Type: URL from Arguments
Arguments: sys_user_list.do?sysparm_query=user_name=javascript:getDuplicateUsers()
3. Report
Name: Active Duplicate User IDs
Type: List
Visible to: Admin Group
Table: User [sys_user]
User ID is javascript:getDuplicateUsers()