shloke04
Kilo Patron
Options
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
07-10-2024
11:22 AM
Hello,
While exploring about Company Normalization capabilities, found there are hidden secrets with not all details documented within ServiceNow docs on how the design for Normalization data services work.
What are Normalization Data Services?
Normalization data services plugin helps maintain consistency across the platform for attributes which refers to Company (core_company) table being used in different areas like ITSM, ITOM , ITAM etc.
For example: VISA Inc can have multiple variants such as VISA Extractor, Visa etc.
Activate Normalization Data Services
Plugin named "Normalization Data Services Client (com.glide.data_services_canonicalization.client)" needs to be activated by a user who has admin privileges on the platform.
Key Components installed with the plugin:
- Two new attributes named "Hash" and "Normalized" gets added to the Company (core_company) table.
- Two new tables also gets added to the platform mentioned below:
- Normalized Company Name : This tables holds the list of normalized company names.
- Normalized Mappings : This table holds the mappings between company records discovered and normalized company names.
Setup:
Setup can be completed using a series of steps mentioned in guided setup. Reference link mentioned below:
Replace "instance" with your instance name.
- Download Normalized Data: This step downloads standard company data and it's relevant mappings available for that company record from content library into Normalized company name and mapping table mentioned above.
- Update Reference Qualifiers: This step is responsible for updating reference qualifier automatically by the platform for attributes which are not customized. Post completion of this step, there will be still customized attributes or cloned attributes specific to CMDB mostly do not get updated as part of this step. This needs to be done manually. Now there can be attributes which are higher in number which updating manually will be an overkill and can be done in a better manner explained below in the article.
- Normalization Properties: Ensure all properties are marked as True. Details are covered in sections below on different properties.
- Normalize Configuration Items (CMDB): This step is specifically responsible for updating data for attributes like Manufacturer referencing to company table with standardized names from content service.
- Normalize Configuration Items (CMDB) Models: This step is responsible for updating Manufacturer Name within Product model table (cmdb_model)
- Normalize Software Asset Management: This step is responsible to normalize attributes referencing to core company table within SAM like for example Publisher attribute on Discovery Model table.
Note: Within the guided setup, there are no steps to Normalize existing records within Company table.However, the step 5 "Normalize Configuration Items (CMDB)" actually not only normalizes the CMDB but also normalizes the existing company records in core_company table.
Normalization Properties:
| Property Name | Description |
| glide.cmdb.canonical.company.qualifier.enabled | This property is responsible to ensure all attributes on the platform referencing to company table makes use of Normalized names, provided reference qualifier are updated for those attribute as "Normalized is True" |
| glide.cmdb.canonical.always_run | 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 | Enable Discovery to use the normalization service for manufacturer name. |
| glide.cmdb.canonical.company.enabled | This property is to enable or disable the Normalization API. |
| glide.cmdb.canonical.normalize.existing.canonical.core_company_records | This property is important as it provides an ability on the platform to update back the normalized flag to false based on scenario when an existing company record entry is added to the normalized mapping table.
OOTB Scheduled Job: Run normalization job daily gets executed which will check for above use case and update the Normalized flag correctly considering property is marked as True.
For more reference : https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0957144 |
| glide.cmdb.canonical.debug | Enable/Disable logging for canonicalization plugin |
| glide.canonical_name.enable.logging | Turn On or Off the logging for company normalization processing in CanonicalName. |
| glide.cmdb.canonical.url | URL for the central Canonical Repository i.e. "https://sncdataservices.service-now.com/" |
Key Technical Details:
| Component Name | Component Type | Details |
| Download Normalized Company Names | Data Services : Download Scheduled | These are responsible to download company and it's respective mapping names from Content library to stay up to date. |
| Access Normalization Data Services | Application Menu /Module | User Administration >> Normalization Data Services >> Modules available |
| cds_client_staging | Auto Flush | This auto flush is being used to clean up staging table records which has been processed and are 7 days old relying on sys_created_on attribute. Processed and Ready are two available choice options for state attribute in staging table. |
| Tables | Normalized company name: Normalized entry for a company record which gets tied to different discovered entries for the same company record stored in Normalized mapping table. |
| CanonicalFilter | Dynamic Filter Options | This is available for selection as a option for Reference Qualifier. Behind the scenes, this dynamic filter option performs a check if the property named "glide.cmdb.canonical.company.qualifier.enabled" is marked as true then Reference Qualifier condition gets set as "Normalized is True" |
Normalized Company Mapping | Relationship | This can be added on the company record form. This relationship performs below check before data gets displayed:
|
| Normalized Company Name | Relationship |
|
| Make Canonical Company | Business Rule | This is one of the important component for Normalization data service plugin as most of the key features are logically built here itself and is important to understand mentioned below:
|
| Set Canonical Hash | Business Rule | When: Before Insert/Update Table: Company (core_company) Condition: Name of company Changes Details: If name of company changes to empty, then updates the HASH column as empty as well. Other wise generates a new HASH value using Canonical api and updates the HASH column. |
| Update Canonical Record | Business Rule | When: After Update Table: Normalized Company Name Condition: Name Changes Details:
|
| Abort company deletion if mapping exists | Business Rule | When: Before Delete Table: Normalized Company Name Details:
|
| Canonicalize Manufacturer Company CI | Business Rule | When: Before Insert/Update Table: Configuration Item Condition:
Details:
|
| Mark Customer Override | Business Rule | When: Before Update Table: Normalized Company Name Condition:
Details:
|
| Canonicalize Manufacturer Company Model | Business Rule | When: Before Insert/Update Table: Product Model Condition: Manufacturer is not empty Details: Same as BR named "Canonicalize Manufacturer Company CI"
|
| Update CDS Name | Business Rule | When: After Update Table: Company Condition: Name Changes AND Normalized is True Details:
|
| Add canonical to scratchpad | Business Rule | When: Display Table: Company Details:
|
| Generate Discovered Name Hash | Business Rule | When: Before Insert/Update Table: Normalized Mapping Details:
|
| CanonicalizationUpdater | Script Include | This updates the manufacturer field in cmdb to refer to canonicalize names |
| CanonicalServiceFilter | Script Include | Used in Dynamic Filter Option mentioned above |
| Promote Discovered Name | UI Action | Available only to users with admin privileges This action allows to promote a discovered name as the Normalized company name with Customer Override checkbox marked as true |
| Normalize | UI Action | This is available as a list choice on Configuration item table and makes use of black box Canonical api to normalize manufacturer attributes. |
Useful FIX Scripts
1. Retrieve HASH value of a company record
CanonicalUtil.getHash('Name of the company')
2. Find Invalid HASH - This can be useful to determine if there are existing company records which do not have a valid HASH value available on company record and appropriate action can be taken to correct the right HASH value updated
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();
}
}
}
}
3. Find references where above Invalid HASH records are being used and move references accordingly:
var dryRun = false;
var columns = getAllCompanyRefs();
gs.print("columns=" + JSON.stringify(columns))
var unknownCompanies = {};
for (var i = 0; i < columns.length; i++) {
var column = columns[i];
fixCompanies(column.table_name, column.column_name);
}
//Test run for just one table and column
//fixCompanies("samp_sw_publisher", "manufacturer");
function isInvalid(company) {
if (company == null)
return true;
// define what invalid company means.
var hash = '' + company.hash;
// hash is empty
// or, hash starts with 'Invalid'
// can be added, company.canonical == false. This will result in normalization of companies
if (hash == '' || hash.startsWith("Invalid"))
return true;
else
return false;
}
function isUnknown(company) {
// define what invalid company means.
var name = company.name + "";
if (name == '' || name.toLowerCase().startsWith("unknown"))
return true;
else
return false;
}
function replaceCompanyWith(current) {
// define the replacement. Following code will find canonical company for the given name.
// if however, a different logic is required, modify this method to return sysId of the company
return SNC.CanonicalName.normalizeCompany('' + current.name, false);
}
// gs.print("unknownCompanies=" + JSON.stringify(unknownCompanies))
function fixCompanies(tableName, columnName) {
gs.print("FIXING: table_name=" + tableName + " column_name=" + columnName)
var record = new GlideRecord(tableName);
record.addNotNullQuery(columnName);
// interested in companies who's hash is invalid and canonical=false
record.addEncodedQuery(columnName + ".canonical=false");
record.addEncodedQuery(columnName + ".hashSTARTSWITHInvalid");
record.query();
while (record.next()) {
var currCompanySysId = record.getValue(columnName);
// records does not have company set
if (currCompanySysId == '') {
continue;
}
var currCompany = getCompanyGr(currCompanySysId);
// company is valid
if (!isInvalid(currCompany)) {
continue;
}
// company is unknown
if (currCompanySysId in unknownCompanies) {
gs.print("UNKNOWN: record has unknown company " + record.getTableName() + ":" + record.getUniqueValue()
+ " | company sys_id=" + currCompanySysId);
continue;
}
// name is empty
if (('' + currCompany.name) == '') {
gs.print("ERROR: current company.name is empty " + record.getTableName() + ":" + record.getUniqueValue()
+ " | company sys_id=" + currCompanySysId + ", name=" + currCompany.name);
continue;
}
gs.print("INFO: company " + currCompanySysId + ":" + currCompany.name + " is invalid");
var newCompanySysId = replaceCompanyWith(currCompany);
// no changes possible
if (newCompanySysId == currCompanySysId) {
gs.print("UPDATE-ERROR: new company same as old " + record.getTableName() + ":" + record.getUniqueValue()
+ " | company sys_id=" + currCompanySysId + ", name=" + currCompany.name);
continue;
}
var newCompany = getCompanyGr(newCompanySysId);
// can not update with invalid company
if (isInvalid(newCompany) || isUnknown(newCompany)) {
gs.print("UPDATE-ERROR: new company is invalid " + record.getTableName() + ":" + record.getUniqueValue()
+ " | company sys_id=" + newCompanySysId + ", name=" + (newCompany == null ? "null" : newCompany.name));
continue;
}
// may be this check can be skipped
if (newCompany.canonical == false) {
gs.print("UPDATE-ERROR: new company is not canonical " + record.getTableName() + ":" + record.getUniqueValue()
+ " | company sys_id=" + newCompanySysId + ", name=" + newCompany.name);
continue;
}
// finally update
gs.print("UPDATE: Updating " + record.getTableName() + ":" + record.getUniqueValue() +
" | with company sys_id=" + newCompanySysId + ", name=" + newCompany.name
+ " | was company sys_id=" + currCompanySysId + ", name=" + currCompany.name);
if (!dryRun) {
record.setValue(columnName, newCompanySysId);
record.setWorkflow(false);
var returnCode = record.update();
gs.print("UPDATE: Updated " + record.getTableName() + ":" + record.getUniqueValue() + " | updated=" + returnCode);
} else {
gs.print("UPDATE: DRY-RUN");
}
}
}
function getCompanyGr(companySysId) {
if (companySysId in unknownCompanies) {
return null;
}
var company = GlideRecordCache.get("core_company", companySysId);
if (company == null) {
var company = new GlideRecord("core_company");
if (company.get(companySysId)) {
GlideRecordCache.put(company);
} else {
// company not found
gs.print("NOT_FOUND: company sys_id:" + companySysId + " not found");
unknownCompanies[companySysId] = true;
return null;
}
}
return company;
}
function getAllCompanyRefs() {
var gr = new GlideRecord("sys_dictionary");
gr.addEncodedQuery("internal_type=reference^reference=core_company");
gr.query();
var columns = [];
while (gr.next()) {
var td = GlideTableDescriptor.get(gr.getValue("name"));
var ed = td.getElementDescriptor(gr.getValue("element"));
if (ed != null && ed.isFirstTableName()) {
var col = {};
col.table_name = gr.getValue("name");
col.column_name = gr.getValue("element");
columns.push(col);
}
}
return columns;
}
4. Update Reference Qualifier for attributes which were not automatically updated as part of guided setup:
a. Use Reference Qualifier : Simple
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: Do check rollback sequence table to ensure there are no cascade insert/update/delete operations happening as part of the above fix scripts. Also the above fix scripts should be tried in a lower prod environment and based on individual instances, changes can be made as required before executing the same in Production
Additional Support Articles for Reference:
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0784201
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0852264
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0825321
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0752238
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0855759
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0746726
There might be couple of posts in Community forum for the discussion on the same topic, just wanted to share the way I explored. Hope this helps.
Labels:
- 11,456 Views
12 Comments
- « Previous
-
- 1
- 2
- Next »
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
