We are doing a data cleanup in CMDB and am curious if anyone has written a report to identify potential duplicates in sys_usr table?

Doug McCormick
Giga Contributor

We are curious if anyone has a report where they identify potential duplicate records in sys_usr or other foundational tales for that matter.  We are trying to clean up our CMDB data and am pretty sure we have some duplicates.  We can export the data and do some external compares; was just curious if anyone has done something like that within CMDB itself?

3 REPLIES 3

Shane41
ServiceNow Employee
ServiceNow Employee

Hi Doug,

You can use the CMDB health dashboard to identify duplicates in your CI classes

https://docs.servicenow.com/bundle/paris-servicenow-platform/page/product/configuration-management/c...

The CSDM & CMDB Data foundations dashboards provides useful insights in to your CMDB data

https://docs.servicenow.com/bundle/rome-servicenow-platform/page/product/configuration-management/co...

In this post there are list of 'Get Well Playbooks' for the CMDB which can help guide you through remediation process for a number of different scenarios

https://community.servicenow.com/community?id=community_article&sys_id=db4e06251b33fc909f20ece7624bc...

There isn't any OOTB report that I can think of that looks directly at duplicates in foundation data tables

Hope this information helps

Shane

 

Shane41
ServiceNow Employee
ServiceNow Employee

Also the article below has a useful script that can be run to identify duplicates in any table

https://pathwayscg.com/easily-identifying-duplicate-records-in-servicenow/#:~:text=However%2C%20Serv...

_ChrisHelming
Tera Guru

You could make a script include that's a glidequery based on the field you want to check for dupes in:

function getDupeUsersBy(field) {
  return new GlideQuery('sys_user')
    .aggregate('count', field)
    .groupBy(field)
    .where(field, '!=', '')
    .having('count', field, '>', 1)
    .select()
    .toArray(100)
    .map(function(u){return u.group[field];})
    .join(',');

};

 

then just set your filter to field is one of javascript:getDupeUsersBy('email');. Replace email with whatever field you want to check, like user_name.