Search KEY field on sys_ui_message table

PumpWerks
Tera Expert

‎04-03-2023 12:12 PM

There was an issue with Localization on a clients instance. My current task is to search the KEY field on the sys_ui_message table to find text that IS NOT in English (in this case Spanish, German, or Dutch) when the LANGUAGE field  on the sys_ui_message table is set to English.  

 

There are over 144,000 records, and I'd rather not do it manually if there is a way to filter the KEY field.  I can't figure out any way to search just the KEY field for non-English text when the LANGUAGE field is set to English.

 

Is there a way to do this?

 

PumpWerks_0-1680549058804.png

 

Thank you in advance!

 

Dave

0 Helpfuls
  • 518 Views
1 REPLY 1

Punit S
Giga Guru

‎04-03-2023 01:42 PM

Yes, there is a way to search the KEY field on the sys_ui_message table to find text that is not in English when the LANGUAGE field is set to English.

One way to do this is to use a script that queries the sys_ui_message table and filters the results based on the language and content of the KEY field. Here's an example script that you could use:

 

// Set the language to filter by
var language = 'en'; // English

// Query the sys_ui_message table for records with the specified language
var gr = new GlideRecord('sys_ui_message');
gr.addQuery('language', '!=', language);
gr.query();

// Loop through the results and check the KEY field for non-English text
while (gr.next()) {
  var key = gr.getValue('key');
  var message = gr.getValue('message');

  // Check if the key or message contains non-English text
  if (!isEnglish(key) || !isEnglish(message)) {
    // Do something with the record, such as logging it or updating it
    gs.log('Found non-English text in record ' + gr.getUniqueValue());
  }
}

// Function to check if text is in English
function isEnglish(text) {
  // TODO: Implement a language detection algorithm to check if text is in English
  return true;
}

This script queries the sys_ui_message table for records with a language other than English and loops through the results, checking the KEY field and MESSAGE field for non-English text. The  isEnglish function is a placeholder for a language detection algorithm that you would need to implement to check if the text is in English.

You could run this script in the background using a scheduled job, or you could run it on demand from a script include or script action. Note that running this script on a large table with over 144,000 records could take some time, so you may want to test it on a smaller subset of records first.

 

Please mark my answer correct/helpful in case it adds value and moves you a step closer to your desired ServiceNow solution goal. 

Thanks,
Punit

0 Helpfuls