CMDB: Duplicate Records in Reports

[Lawrence _Law_]

We currently are pulling all of our servers out of the CMDB into an excel spreadsheet via a SvcNow report.  Unfortunately, the three tables we have connected to generate a single inventory report is resulting in duplicate records. Is there any way within SvcNow natively to automatically normalize the report to remove duplicate records?  Thanks - Law

[Erik Gunther2]

I'd be interested to see a screenshot of your report and know which tables you are including. Maybe you can modify how you are dot-walking to prevent the duplicates.

I don't know of an easy way to dedupe a report. But there are some workarounds (and some of these are way outside the box but maybe they'll help you):

1. (I'm not sure if you're doing this one already based on your description) Export report to Excel, click on the menu "Data" and then "Advanced" in the "Sort & Filter" section. The Excel popup has a "Unique records only" check box, which removes the duplicates. Note, I know this isn't native to ServiceNow, but this is a quick and dirty approach.
2. Export to Excel as described above and then re-import the table using the Load Data approach. Again, this is way out there in terms of approaches but this would work. Via the Load Data approach, let it create the Import Set table and then copy that table so you have 2 tables with the same schema. Via Load Data, import into your import set, create a Transform Map to the second table, set the CI's sys_id as the coalesce value. Then create your report based on that table. So your process would be to export to excel, then re-import and run your new report against the second table. It's horrible, yes, but once established, the export/re-import process only takes less than a minute.
3. Create a database view instead of a report, generate the list and do the export import on that.

These approaches aren't great but I hope they help.

[Lawrence _Law_]

Hello Erik - Thanks for he prompt reply.  We are currently using option 3 and getting the dups.  I will have my team look at option 2 and see how that might work.  Thanks again for providing feedback. Regards - Law

[Azim Kazi]

Hello Law,

Duplicate CIs are encountered during the CMDB identification and reconciliation process. And once they are encountered, each set of duplicate CIs is grouped in a de-duplication task for remediation. The cause of this might be weak identification rules. There are a number of ways in which IT teams can detect and merge the duplicate CIs. One way to detect duplicate CIs is by creating background scripts. For instance, you can check the unique field in the form which should be unique for each record of your table. It can be your mac address, IP address or asset tag. Run the script and you will find the dupes in front of you. The second way is the most practiced; some discovery issues may be resolved by a one-time delete. You may delete a duplicate server record if it matches all of the following criteria: – No tasks are associated with the CI. If tasks are associated with both of the duplicate records, merge these records using the following steps: i. Select the newer record as the record to be deleted. ii. Change the name of the record you plan to delete. iii. Merge all relevant data to the older record. iv. Delete the duplicate record. v. After deletion, revisit the duplicate CI after a few days to ensure that the issue did not recur. If so, continue troubleshooting. During the CI identification method in the CMDB, duplicate CIs are determined by the following properties: glide.identification_engine.skip_duplicates: True by default. glide.identification_engine.skip_duplicates.threshold: set to 5 by default. To modify these properties we need to add them to the System Properties (sys_properties) table, where the numbers of duplicate CIs are detected. You can configure these properties so duplicated CIs are automatically reconciled, skipping duplication. If glide.identification_engine.skip_duplicates is true, and the numbers of duplicate CIs are lesser than the threshold specified by glide.identification_engine.skip_duplicates.threshold, then the oldest of the duplicate CIs are picked as a match and gets updated. The rest of the duplicate CIs are tagged as duplicates by setting the cmdb_ci’s discovery_source field as ‘Duplicate’. During matching, the identification engine filters out any CIs in which statediscovery_source=Duplicate. If glide.identification_engine.skip_duplicates is false, then matching of duplicate CIs fails with an error, and none of the duplicate CIs are updated.

Link:https://www.google.co.in/amp/s/www.softwebsolutions.com/resources/servicenow-cmdb-duplicates.html/amp/

If you find my response worthy kindly make ans helpful as well as correct

Regards,

Ajim.

[shloke04]

Hi,

You can use "Field Normalization" plugin for your purpose. Have a look at the documentation at the below URL:

https://docs.servicenow.com/administer/field_administration/concept/c_FieldNormalization.html

Hope this help. Please mark the answer as helpful/correct based on impact.

Regards,

Shloke