Normalization Data Services Frequently Asked Questions - FAQs - Product Knowledge

Normalization Data Services Frequently Asked Questions - FAQs Question What is Normalization Data Services and how to make the best use of it?

Answer The Normalization Data Services (com.glide.data_services_canonicalization.client) help administrator to maintain consistency across the platform for fields and records that refer to the Company (core_company) table. Some examples are areas such as ITSM, ITOM, ITAM and tables such as 'cmdb', 'cmdb_ci'.

It is used to keep a consistent naming convention such as the variations for company names, for example 'Microsoft' with it's widely known variants such as 'Microsoft Corp', 'Microsoft Corporation', and the like.

How is it activated? Navigate to 'System Definition' > 'Plugins' and search for the plugin named 'Normalization Data Service Client' or 'com.glide.data_services_canonicalization.client'.

Once plugin installation is complete you can follow the guide setup by navigating to 'User Administration' > 'Normalization Data Services' > 'Guided Setup' and follow the steps:

1. Activate Plugin

This step corresponds to the activation of the 'Normalization Data Services Client'

2. Download Normalized Data

The instance will proceed and download a set of the most common normalization names and mappings from the ServiceNow repository onto a customers instance to get them started. This can be turned off after the initial set up if you want to control the data completely.

3. Update Reference Qualifiers

In this step the workflow will attempt to update existing reference qualifiers automatically on the platform for fields that are referencing the core_company table that have not been customized. In some scenarios you will be prompted with a list of records for which the instance was unable to manually update the reference qualifiers, this is normal and usually affects the CMDB table. ServiceNow has a documented process to mass update these records rather than do them manually, you can find a link to this article and more on the useful links list at the bottom of this document.

4. Normalization Properties

In this step the instance will make sure that all related properties are set to their proper default value. Please refer to the following table:

Property Name Type Value Description glide.cmdb.canonical.company.qualifier.enabled true|false true Reference qualifiers on all tables that reference Company will be updated to use the Normalized field. glide.cmdb.canonical.always_run true|false true Enable business rule that automatically normalizes manufacturer names for configuration items. The purpose of this business rule is to ensure that configuration items added or updated in the instance by a mechanism other than Discovery (i.e. via the Import feature) are also normalized. Enabling this business rule does not prevent CIs inserted or updated by Discovery from being normalized. glide.cmdb.canonical.discovery.enabled true|false true Enable Discovery to use the normalization service for manufacturer name. glide.cmdb.canonical.company.enabled true|false true This property is to enable or disable the Normalization process. glide.cmdb.canonical.normalize.existing.canonical.core_company_records true|false true Normalize existing canonical core_company records. glide.cmdb.canonical.debug true|false false Enable/Disable logging for canonicalization plugin glide.canonical_name.enable.logging true|false false Turn On or Off the logging for company normalization processing in CanonicalName. glide.cmdb.canonical.url string https://sncdataservices.service-now.com/ URL for the central Canonical Repository glide.cmdb.canonical.jobname string canonical_lookup Name of the normalization job glide.cmdb.canonical.log true|false true Logs the execution of the normalization jobs and it's updates. glide.cmdb.canonical.upload.enable true|false false Determines whether the instance uploads normalize mapping to the ServiceNow repository for review and future inclusion. glide.cmdb.canonical.upload_associated_data true|false true Works in conjunction to the property 'glide.cmdb.canonical.upload.enable' to upload related data. Both properties must be set to true for upload to occur. 5. Normalized Configuration Items (CMDB)

In this step the instance will normalize fields for the cmdb tables for fields such as manufacturer that reference the core_company table with the normalized (canonical) name.

6. Normalize Configuration Items (CMDB) Model

In this step the instance will normalize fields for the cmdb_model table for fields such as manufacturer that reference the core_company table with the normalized (canonical) name.

7. Normalize Software Asset Management

In this step the instance will normalize fields fields such as Publisher that reference the core_company table with the normalized (canonical) name under the Software Asset Management umbrella.

NOTE: In some instance the automated workflow can fail to update the reference qualifiers in such scenarios please follow the steps on this Knowledge article or to normalized any skipped record follow this documentation .

How is a record determined to be normalized? There are two scenarios if which the flag for a core_company record is set to normalize is true:

1. If the core_company.name record corresponds to a discovered name on the table cds_client_mapping in the following condition: core_company.name===cds_client_mapping.discovered_name===cds_client_mapping.canonical_name | core_company.name===cds_client_mapping.discovered_name===cds_client_name.name

2. If the core_company.name record does no have a corresponding record on the cds_client_mapping.discovered_name it will be set to true.

NOTE : Normalized names are case sensitive so they must match exactly when comparing. For example "Microsoft" is not the same as "microsoft" when it comes to normalization and they are determined to be two completely different values.

If there is no mapping or discovered name, does the user need to create them manually? is there a built in way to mass create normalization mappings? Yes, if there are not existing and you wish to normalize a set of company names the administrator will need to create the mappings and the normalize name manually and then either run the normalization job manually or wait for it to run automatically every day.

Currently the instance will set any core_company record that has no matching entry on the normalized mappings to normalized as a customer override, but there is not current tool that will suggest normalized mappings for administrators.

How are the core_company records related to the cds_client_name and cds_client_mapping records?

A core_company record can have none to one corresponding record on the cds_client_name table.

A core_company record can have none to one corresponding discovered name on the cds_client_mapping table.

A cds_client_name can have one to many corresponding discovered name on the cds_client_mapping table.

A cds_client_mapping.discovered_name must have only one cds_client_mapping record.

How can I update the reference qualifier for records which were not automatically updated during the set up? Set the reference qualifier on the parent record to simple and then run the following script:

var comp = new GlideRecord('sys_dictionary'); comp.addEncodedQuery('use_reference_qualifier=simple^reference=core_company^internal_type=reference') comp.query(); while(comp.next()){ comp.reference_qual_condition = JSUtil.notNil(comp.reference_qual_condition) ? comp.reference_qual_condition + "^canonical=true^EQ" : "canonical=true^EQ"; comp.reference_qual = JSUtil.notNil(comp.reference_qual) ? comp.reference_qual+ "^canonical=true^EQ" : "canonical=true^EQ"; comp.setWorkflow(false); comp.update(); } NOTE: Every time you run a script please make sure you have selected to enable rollback context in case the result is not expected.

How can I mass update the skipped reference qualifiers for records on the cmdb hierarchy? 1. Access the following URL from an affected instance:

https://[instance].service-now.com/now/nav/ui/classic/params/target/sys_dictionary_list.do%3Fsysparm_query%3Dreference%253Dcore_company%255Edynamic_ref_qualISEMPTY%255EORdynamic_ref_qual.sys_name!%253DCanonicalFilter%255Ereference_qual_conditionNOT%2520LIKEcanonical%253Dtrue%26sysparm_first_row%3D1%26sysparm_view%3D -The filter is returning all the fields that the reference qualifier was unable to update.

2. Open the parent fields (on parent table) for example: table: cmdb field: company

table: cmdb field: vendor

3. On the reference specification tab add the following condition: Normalized is True

4. Save the record. Once the backend updates has completed and propagated reload the link on step 1. It should have drastically decreased.

5. Navigate to 'System Definition' > 'Scripts - Background' as a admin and run the following script:

6. Once the script finish executing reload the link on step 1.

7. For any records that are remaining, open the record and update the reference qualifier to 'normalized=true' and save.

How can I retrieve the hash value for a company record? Run the following script:

CanonicalUtil.getHash('Name of the company'); NOTE: Every time you run a script please make sure you have selected to enable rollback context in case the result is not expected.

Is there a way to find and fix any invalid hashes for company records? To find the references where invalid hash records are being used please use the following script: (Note the state of the dryRun flag)

You can get this fix them by running the following script to find and fix:

fixInvalidHashes(); function fixInvalidHashes() { if (!GlidePluginManager.isActive('com.glide.data_services_canonicalization.client')) return; fixInvalidHashesInNormalizedNames(); fixInvalidHashesInNormalizedMappings(); fixInvalidHashesAndCanonicalFlagInCoreCompany(); fixDuplicateHashCoreCompanies(); } function fixInvalidHashesInNormalizedNames() { var nngr = new GlideRecord('cds_client_name'); if (!nngr.isValid()) return; var actualHash = ''; var expectedHash = ''; var canonicalName = ''; nngr.query(); while (nngr.next()) { canonicalName = nngr.getValue('name'); actualHash = nngr.getValue('hash'); expectedHash = SNC.CanonicalName.getHash(canonicalName); if (actualHash != expectedHash) { nngr.setWorkflow(false); nngr.hash = expectedHash; nngr.update(); } } } function fixInvalidHashesInNormalizedMappings() { var mapgr = new GlideRecord('cds_client_mapping'); if (!mapgr.isValid()) return; var actualHash = ''; var expectedHash = ''; var discoveredName = ''; mapgr.query(); while (mapgr.next()) { discoveredName = mapgr.getValue('discovered_name'); actualHash = mapgr.getValue('discovered_name_hash'); expectedHash = SNC.CanonicalName.getHash(discoveredName); if (actualHash != expectedHash) { mapgr.setWorkflow(false); mapgr.discovered_name_hash = expectedHash; mapgr.update(); } } } function fixInvalidHashesAndCanonicalFlagInCoreCompany() { var gr = new GlideRecord('core_company'); if (!gr.isValid() || !gr.isValidField('hash') || !gr.isValidField('canonical')) { gs.log('Invalid `core_company` definition. Either the `hash` or `canonical` column is not present', "FixInvalidHashes"); return; } gr.addEncodedQuery("hashNOT LIKEInvalid^ORhashISEMPTY"); gr.addOrderBy('name'); gr.query(); var currentHash = ''; var expectedHash = ''; var companyName = ''; var curSysId = ''; var canonicalFlag = false; var cdn = new GlideRecord('cds_client_name'); while (gr.next()) { companyName = gr.getValue('name'); currentHash = gr.getValue('hash'); curSysId = gr.getUniqueValue(); expectedHash = SNC.CanonicalName.getHash(companyName); if (currentHash == expectedHash) continue; if (('' + currentHash).startsWith(invalidHashPrefix())) continue; canonicalFlag = isCanonicalCompany(cdn, companyName, expectedHash); // Check if there is already an entry with the expectedHash, otherwise we'll get 'Unique Key violation' var dupEntry = getDupEntryIfExist(expectedHash); gr.setWorkflow(false); // If no duplicate entry, go ahead and correct the hash if (dupEntry == null) { gr.hash = expectedHash; // Change the 'canonical' value only if it is going to be 'true'. // We don't want to change the current record's 'canonical' flag from 'true', because it might be currently referenced by other records. if (canonicalFlag) gr.canonical = canonicalFlag; gr.update(); logHashUpdate(companyName, curSysId, currentHash, expectedHash); continue; } // In scenarios where we cannot correct the hash, we are going to update it with this 'invalidHash'. // The prefix 'Invalid' is to identify records which we are not able to correct. Helpful during debugging var invalidHash = invalidHashPrefix() + curSysId; dupEntry.setWorkflow(false); // Goal of the scenarios below is to ensure that data correction is done to the existing canonical records where possible. Non-canonical records with invalid hash are not much of a problem. if (!dupEntry.canonical && !gr.canonical) { gr.hash = invalidHash; gr.update(); if (canonicalFlag) { dupEntry.canonical = canonicalFlag; dupEntry.update(); } logHashUpdate(companyName, curSysId, currentHash, invalidHash); continue; } // The current record is a canonical record, but the duplicate entry is non-canonical, but has a correct hash. In this case update the hash of the canonical record. if (!dupEntry.canonical && gr.canonical) { //Duplicate entry need to be updated before the current record, otherwise 'Unique Key Violation' will occur dupEntry.hash = invalidHash; dupEntry.update(); gr.hash = expectedHash; gr.update(); logHashUpdate(companyName, curSysId, currentHash, expectedHash); continue; } if (dupEntry.canonical || (!dupEntry.canonical && canonicalFlag)) { gr.canonical = false; gr.hash = invalidHash; gr.update(); dupEntry.canonical = true; dupEntry.update(); logHashUpdate(companyName, curSysId, currentHash, invalidHash); continue; } } function getDupEntryIfExist(expectedHash) { var gr = new GlideRecord('core_company'); gr.addQuery('hash', expectedHash); gr.query(); return gr.next() ? gr : null; } function isCanonicalCompany(cdn, name, hash) { cdn.initialize(); cdn.addQuery('name', name); cdn.addQuery('hash', hash); cdn.query(); return cdn.hasNext(); } } function logHashUpdate(companyName, sysId, currentHash, newHash) { gs.log("Hash Mismatch. Updated Company: '" + companyName + "', SysId: '" + sysId + "', hash from '" + currentHash + "' to '" + newHash + "'", "FixInvalidHashes"); } function invalidHashPrefix() { return 'Invalid'; } function fixDuplicateHashCoreCompanies() { var gr = new GlideAggregate("core_company"); gr.addEncodedQuery("hashNOT LIKEInvalid"); gr.addNotNullQuery("hash"); gr.groupBy("hash"); // hashes which have more than one core_company gr.addHaving("COUNT", ">", "1"); gr.query(); while (gr.next()) { var hashValue = gr.getValue("hash"); if (hashValue.startsWith(invalidHashPrefix())) continue; gs.print("fixing duplicates with hash: " + hashValue); setUniqueHashInCC(hashValue) } function setUniqueHashInCC(hash) { var gr = new GlideRecord("core_company"); gr.addQuery("hash", hash); gr.orderByDesc("canonical"); gr.query(); // skip first company if (gr.next()) { gs.print("core_company with right hash: " + gr.getUniqueValue()); while (gr.next()) { var newHash = invalidHashPrefix() + gr.getUniqueValue(); logHashUpdate(gr.getValue("name"), gr.getUniqueValue(), gr.getValue("hash"), newHash); gr.setValue("hash", newHash); gr.setWorkflow(false); gr.update(); } } } } NOTE: Every time you run a script please make sure you have selected to enable rollback context in case the result is not expected.

How do I disable normalization on my instance? Set the system property 'glide.cmdb.canonical.company.qualifier.enabled' to false.

Additional Useful Information

Key Tables:

Display Name Table Name Description Company core_company This table holds all the company records for a customer instance. Normalized Company Name cds_client_name This table holds the normalized also known as 'canonical' company name.

NOTE: During the guided setup this table is populated with data downloaded from the ServiceNow repository, it is normal to have normalized names that do not corresponds to records on the core_company table.

Normalized Mapping cds_client_mapping This table stores the relationships between discovered names and their normalized names.

A normalized name can have more than one mapping but each mapping may only have one normalized name.

NOTE: During the guided setup this table is populated with data downloaded from the ServiceNow repository, it is normal to have normalized names that do not corresponds to records on the core_company table.

Normalized Client Change cds_client_staging Stores information about the discovered name, it's hash, the filed, and source that was compared and the source table.

Data Services: Download Schedule

cds_client_schedule Holds the execution records for the automatic downloads for Normalized Company names and Company Mappings. Out of the box they are set to run once a week.

Key Fields:

Table Column label Column name Type Default value Display Description core_company Normalized canonical true|false false false Used to determine if the related core_company record is returned by the reference qualifier on the related tables.

NOTE: This is set to true under one of two conditions:

core_company.name matches a cds_client_name.name

*core_company.name has no corresponding record on the cds_client_mapping table.

*This default behavior was changed address an issue where core_company records were not being returned to users if there was no corresponding mapping causing discrepancies for users.

core_company Hash hash string false Once the normalized jobs has executed a hash will be calculated and stored on this field to mark the record as being normalized.

core_company Name name string true Used to compared against cds_client_mapping entries to determine the normalized flag.

Business Rules:

Name Table When Condition Description Make Canonical Company core_company before Advanced Find a matching cds_client_mapping entry. If it exists, lookup the cds_client_name for the canonical info If it does not exist, this is a custom company and set it to canonical *If the canonical version of the company does not exist, it will create the canonical core_company.

Set Canonical Hash core_company before core_company.name changes Will set the hash value using CanonicalUtil API. If updated to empty it will clear the hash, otherwise it will calculate a new hash. Set Canonical Hash cds_client_name before cds_clien_name.name changes Will set the hash value using CanonicalName API. If updated to empty it will clear the hash, otherwise it will calculate a new hash. Update Canonical Record cds_client_name after cds_clien_name.name changes Compares the previous hash values with the current calculated hash to nd company record to identify the right company record which requires an update. If a match is found, the name and hash on company table is updated with the updated name and hash available on Normalized company name table. Abort company deletion if mapping exists cds_client_name before delete If a record has an existing mapping it will abort the transaction. Canonicalize Manufacturer Company CI cmdb_ci before gs.getProperty("glide.cmdb.canonical.always_run") == "true" && (current.operation() == "insert" || (current.operation() == "update" && current.manufacturer.changes())) Get the canonical name of the manufacture if it exist. Mark Customer Override cds_client_name before If either name, description, table or field changes If customer makes any of this changes the customer override is set to true. Canonicalize Manufacturer Company Model cmdb_model before gs.getProperty("glide.cmdb.canonical.always_run") == "true" && (current.operation() == "insert" || (current.operation() == "update" && current.manufacturer.changes())) Get the canonical name of the manufacture if it exist. Update CDS Name core_company after name changes and normalized is true if a company name that is the normalized value changes it will update the normalized name on related table and will update the hash with the current calculated value while updating the customer override to true. Add canonical to scratchpad core_company display Add the canonical flag to the client because most forms will not have the flag on it. Generate Discovered Name Hash cds_client_mapping before insert or update of a record Discovered hash is updated based on the discovered company name field. Canonicalization Tracking cmdb_ci before Insert or update Only active when 'glide.canonical_name.enable.logging' is set to true.

Script Includes:

Name Package Description AJAXCanonicalizationWorker Normalization Data Services Configuration Progress workers for some of the steps in the Canonicalization Guided Setup CanonicalizationUpdater Normalized Data Services Client This updates the manufacturer field in cmdb to refer to canonicalize names CanonicalServiceFilter Normalized Data Services Client If ' glide.cmdb.canonical.company.qualifier.enabled' set to true the instance will only return the canonical values on the reference filters where it applies. ClearNonCoreCompanyExtensionFix Normalized Data Services Client Clears the hash field and fix canonical field for all non-core_company records in the core_company table (e.g., customer_account). Ensures base core_company records remain unchanged. Intended for customers enabling the new NDS property (glide.cmdb.canonical.use_base_core_company_only) to remove stale hash values from extension tables. Set canonical field to false for all non-core_company records. Logs the total number of updated records in system logs.

Schedule Jobs

Name Trigger type Description Download Normalized Company Mappings Once a week/Days in week Instance will download update list of normalized mapping from ServiceNow repository. Only new records will be downloaded.

For records marked as customer override they will be skipped even if a new mapping exist on the repository.

Download Normalized Company Names Once a week/Days in week Instance will download update list of normalized company name from ServiceNow repository. Only new records will be downloaded.

For records marked as customer override they will be skipped even if a new normalized company name exist on the repository.

Run normalization job daily Daily Normalizes existing records on the instance according to the mappings and normalized names defined.

Miscellaneous:

Name Type Description Promote Discovered Name UI Action This UI Action allows the promotion of a discovered name as the Normalized company name and will mark the record as a customer override. Normalize UI Action UI action available of the cmdb tables that leverages the canonical api to normalize a manufacturer's attributes. CanonicalFilter Dynamic Filter Options If system property "glide.cmdb.canonical.company.qualifier.enabled" is set to true then the reference qualifier will only return the core_company records for which the normalized is true value. cds_client_staging Auto Flush Will clean up the normalization staging table for records that have been processed and are older than 7 days from the sys_created_on attribute Related Documentation:

How to manually normalize reference qualifiers

ServiceNow Company Normalization - Duplicate Company

Normalization data services doesn't work on Contract table

Normalization Data Services Guided Setup did not update some reference qualifiers

Normalization Issue - Duplicate entries of normalized records for same vendor on core_company table

What is the property "glide.cmdb.canonical.normalize.existing.canonical.core_company_records" for ?

Admin users not able to view the Normalization Data Services scheduled job entries.

Field Normalization failing on CMDB tables

Background Script to Normalize Reference Qualifiers Skipped During Normalization Data Services Guided Setup

Normalization Data Services - Creating or updating Normalization Mappings