Welcome Login
Blog Photos Links

RSS Feed

Sorting Group By Lists by count

September 28, 2021, 4:54 pm - James Farrer

I've often needed to sort by whatever had the most/least of a grouping and generally used the list context menu to go to a Bar Chart but then I often can't see the data without another click and other hassles. As it turns out there's a URL parameter that can be added to set the aggregate sort. 

&sysparm_group_sort=COUNT

OR

&sysparm_group_sort=COUNTDESC

Merging duplicate records in ServiceNow

August 9, 2019, 4:42 pm - James Farrer

To merge duplicate records, you have to make sure you know which record to keep and combine data in that record. The first usually takes a manual comparison and update of the fields. The other major piece is resolving references to the record that will be deleted. That's where this script comes in.

If you’ve got a unique identifier of some kind you can use a script like this to find the duplicates. Otherwise, it’s likely going to be a manual process of identifying them.

var count = new GlideAggregate('cmdb_ci');
count.addAggregate('count', 'name');
count.orderByAggregate('count', 'name');
count.addHaving("count", ">", 1);
count.query();  
while (count.next()) {
   var name = count.name;
   var record_count = count.getAggregate('count', 'name');
   gs.info("Duplicates: " + name + ", Count: " + record_count);
}

Once you know which record to keep and which to delete, the following code will be helpful. It goes through the relevant tables where we can find any references to it and updates the records to point to the record that will be kept. It’s got a built-in flag so you can do a test run. The script takes the table the records are on, the Sys ID of the old record, the Sys ID of the record to move references over to, and a flag to indicate whether to save the changes (true = save changes, false or omitted = test run).

function mergeRecords(table, move_from_id, move_to_id, save_changes){
  if(move_from_id == move_to_id){
    gs.info("The ID's are the same: " + move_from_id);
    return;
  }
	
  var refTable = new TableUtils(table).getTables();
  gs.include('j2js');
  refTable = j2js(refTable).join();

  //Query dictionary table for reference, document_id, and condition fields
  var dict = new GlideRecord('sys_dictionary');
  dict.addEncodedQuery('referenceIN' + refTable).addOrCondition('internal_type', 'document_id').addOrCondition('internal_type', 'conditions');
  //Do not query audit and log fields
  dict.addQuery('name', 'DOES NOT CONTAIN', 'var__m_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ecc_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ha_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'syslog');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_history');
  dict.addQuery('name', 'DOES NOT CONTAIN', '_log');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'text_search');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'ts_');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_watermark');
  dict.addQuery('name', 'DOES NOT CONTAIN', 'sys_audit');
  dict.orderBy('name');
  dict.orderBy('element');
  dict.query();
  while(dict.next()){
    var table_name = dict.name;
    var field_name = dict.element;
    // Skip tables used for Table Rotation
    var table_rotation_gr = new GlideRecord("sys_table_rotation_schedule");
    table_rotation_gr.addQuery("name.name", '!=', table_name);
    table_rotation_gr.addQuery("table_name", table_name);
    table_rotation_gr.query();
    if(!table_rotation_gr.hasNext()){
      
      var reference_type = dict.internal_type;

      // Look up the Table Scope
      var table_gr = new GlideRecord("sys_db_object");
      table_gr.addQuery("name", table_name);
      table_gr.addQuery("scriptable_table", false);
      table_gr.query();
      if(table_gr.next()){

        // Change to the scope of the table
        gs.setCurrentApplicationId(table_gr.sys_scope);
        
        
        //Query each table for matching records
        var related_record_gr = new GlideRecord(table_name);
        if(reference_type == 'glide_list' || reference_type == 'conditions'){
          related_record_gr.addQuery(field_name, 'CONTAINS', move_from_id);
        } else{
          related_record_gr.addQuery(field_name, move_from_id);
        }
        related_record_gr.query();
        while(related_record_gr.next()){
          // Do a replace so we support all field types including lists and conditions
          var old_value = related_record_gr.getValue(field_name);
          var new_value = old_value.replace(move_from_id, move_to_id);
          related_record_gr.setValue(field_name, new_value);
          //Silent update
          related_record_gr.setWorkfow(false);
          related_record_gr.autoSysFields(false);
          if(save_changes){
            related_record_gr.update();
          }
        }
        var record_count = related_record_gr.getRowCount();
        if(record_count > 0){
          var record_message = ' records found';
          if(record_count == 1){
            record_message = ' record found';
          }
          gs.info(record_count + record_message + " in " + table_name + "." + field_name);
        }
      }
    }
  }

  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Catalog Variables
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Change back to global scope
  gs.setCurrentApplicationId("global");
  // Process Catalog Variable References
  var catalog_var_gr = new GlideRecord('sc_item_option');
  catalog_var_gr.addQuery('value', 'CONTAINS', move_from_id);
  catalog_var_gr.query();
  while(catalog_var_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = catalog_var_gr.getValue(field_name);
    var new_value = old_value.replace(move_from_id, move_to_id);
    catalog_var_gr.setValue(field_name, new_value);
    //Silent update
    catalog_var_gr.setWorkfow(false);
    catalog_var_gr.autoSysFields(false);
    if(save_changes){
      catalog_var_gr.update();
    }
  }
  var variables_count = catalog_var_gr.getRowCount();
  var variables_message = ' catalog variables found';
  if(variables_count == 1){
    variables_message = ' catalog variable found';
  }
  gs.info(variables_count + variables_message);
  
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Workflow Variables
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  //Query for workflow variable values
  var_value_table_name = 'sys_variable_value';
  var var_value_gr = new GlideRecord(var_value_table_name);
  var_value_gr.addQuery('value', 'CONTAINS', move_from_id);
  var_value_gr.query();
  while(var_value_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = var_value_gr.getValue("value");
    var new_value = old_value.replace(move_from_id, move_to_id);
    var_value_gr.setValue("value", new_value);
    //Silent update
    var_value_gr.setWorkfow(false);
    var_value_gr.autoSysFields(false);
    if(save_changes){
      var_value_gr.update();
    }
  }
  var wf_variables_count = var_value_gr.getRowCount();
  var wf_variables_message = ' workflow variables found';
  if(wf_variables_count == 1){
    wf_variables_message = ' workflow variable found';
  }
  gs.info(wf_variables_count + wf_variables_message); 


  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Attachments
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  //Query for attachments
  attachment_table_name = 'sys_attachment';
  var attachment_gr = new GlideRecord(attachment_table_name);
  attachment_gr.addQuery('table_sys_id', move_from_id);
  attachment_gr.query();
  while(attachment_gr.next()){
    // Do a replace so we support all field types including lists and conditions
    var old_value = attachment_gr.getValue('table_sys_id');
    var new_value = old_value.replace(move_from_id, move_to_id);
    attachment_gr.setValue('table_sys_id', new_value);
    //Silent update
    attachment_gr.setWorkfow(false);
    attachment_gr.autoSysFields(false);
    if(save_changes){
      attachment_gr.update();
    }
  }
  var attachment_count = attachment_gr.getRowCount();
  var attachment_message = ' attachments found';
  if(attachment_count == 1){
    attachment_message = ' attachment found';
  }
  gs.info(attachment_count + attachment_message); 


  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  // Delete old record
  //~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  var old_gr = new GlideRecord(table);
  if(old_gr.get(move_from_id)){
    gs.info("Deleting old record: " + table + " - " + old_gr.getDisplayValue() + " (" + old_gr.sys_id + ")");
    if(save_changes){
      old_gr.deleteRecord();
    }
  }
  
}

Getting a ServiceNow instance URL

August 30, 2018, 2:06 pm - James Farrer

In the London release, ServiceNow is introducing support for custom domain names. What this means is that anywhere a full URL is generated (usually notifications) we'd need to make sure that we're making the URL's properly. I've most often seen and used the instance_name property to get the hostname and then added that in with ".service-now.com". The new feature should be backwards compatible so things won't break but it won't use the new domain should they start using that.

Moving forward we should instead use the following to get the full domain name and then we just need to add the specific path after it.

Use this:

gs.getProperty("glide.servlet.uri")

To get this:

https://crossfuze.service-now.com/

Which in London release could be configured as https://servicenow.crossfuze.com/ or whatever else we want.

Table Name field type and scoped applications

April 30, 2018, 8:47 am - James Farrer

I've run into an issue several times lately and wanted to make a note for myself and thought I'd share it at the same time since it wasn't easy to find in the documentation.

When using a Table Name field on a scoped table there is a restriction by default that only allows tables from the applications scope to be selected in the field. Every time I've used this type of field I've needed to show other tables, particularly Task tables.

The fix is a simple, though obscure, attribute that can be added to the field. 

To fix it:

  1. Go to the dictionary record of the Table Name field with the issue.
  2. Add a new attribute using one of the two main methods:
    1. Using the Attributes related list
      1. Click the New button in the Attributes related list.
      2. Select the "Allow public" attribute.
      3.  Set the value to true.
    2. Using the Attributes field in the Advanced View
      1. Add the following to attributes string with commas to separate the attributes if there are others present:

        allow_public=true

For the field I was working on I had the following to show all Task tables while excluding Task itself:

allow_public=true,​base_table=task,​skip_root=true,​base_start=true

Identify workflows where a group is listed

January 17, 2018, 4:43 pm - James Farrer

 Here's a script that goes through the workflow activity fields and prints out where the group is listed in workflows.

// Sys ID of the group that we're testing for
var group_id = "c4762f920fd0310066e76ab8b1050e58";

var group_gr = new GlideRecord("sys_user_group");
if (group_gr.get(group_id)) {
	gs.print("Querying workflows for group: " + group_gr.getDisplayValue());
}
var wf_var_value_gr = new GlideRecord("sys_variable_value");
wf_var_value_gr.addQuery("value", group_id);
wf_var_value_gr.query();
while (wf_var_value_gr.next()) {
	// Query for the workflow activity
	var wf_activity_gr = new GlideRecord(wf_var_value_gr.document);
	if(wf_activity_gr.get(wf_var_value_gr.document_key)) {
		if(wf_activity_gr.workflow_version){
			// Query for the workflow version
			var wf_version_gr = new GlideRecord("wf_workflow_version");
			wf_version_gr.addQuery("sys_id", wf_activity_gr.workflow_version);
			wf_version_gr.addQuery("published", true);
			wf_version_gr.query();
			if (wf_version_gr.next(wf_activity_gr.workflow_version)) {
				gs.print("Group is assigned to " + wf_version_gr.name + " -> " + wf_activity_gr.name);
			} else {
				//gs.print("WF version not published " + wf_activity_gr.workflow_version.getDisplayValue() + " -> " + wf_activity_gr.name);
			}
		} else {
			gs.print("Workflow activity version is blank for " + wf_activity_gr.name);
		}
	}
}

ServiceNow Formatted Text (HTML WYSIWYG) editor

January 24, 2017, 2:54 pm - James Farrer

Editing formatted text in a web browser has come a long way over the years. This is true within ServiceNow as well. In general it's pretty easy to write a knowledge article and format things the way you want. Unfortunately there's a gap between just formatting it the way you want and having consistent formatting across the entire knowledge base, system, and self-service portals.

A lot of what I do is work with the CMS and now Service Portal within ServiceNow. If you don't know these are the sections of the tool that allow for creating a branded portal for end-users to interact with IT and other areas of the business support side of things. 

When you're working on a portal you build out the blocks, widgets, lists, menus, etc. and from the overall structure perspective you're in control. But as soon as you introduce the knowledge base and the articles that are created by anyone and everyone, consistency tends to go out the door. 

The default editor for formatting text in an article (and elsewhere in ServiceNow) is used and offers options for selecting the font, font size, bold, italics, underline, etc. This works ok if you're looking at all of the content in one place, but when you start to look at styles across articles and compared to other places in the self-service portal this starts to cause problems. Any good portal is going to come with a decent set of default styles for headings, paragraphs, links, etc. But the default editor options don't include any way to tie into these unless you are an HTML wizard and can edit the source.

Fortunately, there is a system property that allows us to add the common HTML tags that allow us to use these standard formats. The glide.ui.html.editor.v4.toolbar.line1 property contains the list of options available in the editor. I strongly suggest adding in the formatselect option with gives the Paragraph and Heading options so users can select a title and the various section headers and apply formatting automatically that will be consistent across the site.

The property can be found under System Properties -> UI Properties.

 

After you've got the property updated, a little bit of training to help the big contributors to start using it and your site should start to have a much more consistent look and feel.

Service Portal missing panel styles

December 13, 2016, 10:44 am - James Farrer

In ServiceNow and Service Portal there are some great features in a lot of places. There are, however, a number of rough edges since it's still pretty new. One of the places that I've found is a little rough is with the CSS. Many of the default widgets delivered by ServiceNow use the bootstrap panel structure. This gives a pretty nice format for setting up blocks with headings.

The problem is that most of the brand colors are not supported. It instead uses the default Bootstrap colors. The solution to this is to add the following styles to the widget CSS. It could also be added to the page CSS if you don't want to modify the widget. 

This code utilizes the brand colors set in the Branding Editor to apply the colors.

/* add styles to support brand colors in basic panels */
.panel-danger > .panel-heading {
    color: $panel-bg;
    background-color: $brand-danger;
    border-color: $brand-danger;
}
.panel-warning > .panel-heading {
    color: $panel-bg;
    background-color: $brand-warning;
    border-color: $brand-warning;
}
.panel-success > .panel-heading {
    color: $panel-bg;
    background-color: $brand-success;
    border-color: $brand-success;
}
.panel-info > .panel-heading {
    color: $panel-bg;
    background-color: $brand-info;
    border-color: $brand-info;
}

"Your" vs. "My" and interacting with the user

November 16, 2016, 2:33 pm - James Farrer

If you're building a self-service website, when dealing with the stuff relating to the user, you should most definitely use "Your" not "My".

I've done a lot of work in the self-service side of ITSM over the years. Much of this has been with ServiceNow as the environment. I've also dabbled in various levels of web development and interface design. A very common mistake and point of argument is that the menu link should be "My [stuff]". I'm not sure where this got started but I'm guessing it has something to do with wanting to make people feel at home and in control of the situation when dealing with a website. Either that or someone was lazy and just not thinking things through.

When I am dealing with a website me, my stuff, my computer, etc. is on my side of the network connection. The company, website, forms, information, etc. is another entity that I am having a dialog with. 

Flip that around and as a website developer I'm creating the dialog between the organization or company I represent and the end user. This means that when I'm giving instructions I will say "Here's what you need to do..." and anything that discusses the organizations thoughts, interests, etc. should take a first person narrative of "we", "us", "our", or in the case of a personal blog or something like that, "I", "me", and "my". 

I ran into a page today where I realized there was a sentence explaining how something works with "you can go to...and this is where you'll find...". Right below that there was a block label "My Approvals" where the approvals of the end user, not the organization, can be found. It seems like a pretty minor thing and enough websites have flip-flopped with these that most people aren't going to think too much of it but somewhere in the back of their mind something is going to be saying "hey, something is not quite right here". 

As an example of a site that has done this the right way, check out Amazon and their numerous links to "Your Account", "Your Orders", etc.

On a somewhat related note, I've also heard discussions on what form to put documentation for a system into. Should it be first person? Or perhaps an awkward neutral set of instructions that's cold and strictly business? This one often isn't quite as straight forward but I always appreciate the information that's written as more of an honest dialog that clearly articulates who is talking and who is listening. Making things a little more personal in a formal business world can often ease a little stress of a situation since it's just more natural.

So to sum it all up, here is my post that will hopefully persuade you to make your sites better at telling me when there is stuff relating to me with a link titled "Your stuff". Just to be as clear as mud.

 

Javascript in Helsinki - ECMA3 vs ECMA5

May 27, 2016, 1:51 pm - James Farrer

I was pleasantly surprised to hear that ECMA5 will now be supported in the Helsinki release of ServiceNow. For most people this won't mean much of anything. I was excited to hear about the change since there were a few things I had noticed missing when working with server side javascript. Most notably the indexOf operation on arrays.

Today I was testing out some functionality in Helsinki and ran into a really ugly error with something that worked perfectly fine in Geneva. 

After a little digging I discovered that the format for JSON was updated as well and is more strict now. So if you're doing any parsing of JSON in ServiceNow, make sure your property names use double quotes instead of single quotes and you'll save yourself some trouble. 

As an example, this:

{'incident':'caller_id'}

is incorrect. Instead you should use this:

{"incident":"caller_id"}

What's New

There are currently no new items, please check back later.

Archives
2021 (2)
  September (1)
  May (1)
2019 (1)
  August (1)
2018 (3)
  August (1)
  April (1)
  January (1)
2017 (1)
  January (1)
2016 (4)
  December (1)
  November (1)
  May (1)
  January (1)
2015 (1)
  December (1)
2014 (2)
  August (1)
  February (1)
2013 (4)
  October (1)
  July (1)
  June (1)
  April (1)
2012 (11)
  December (2)
  October (3)
  September (1)
  May (1)
  April (1)
  February (2)
  January (1)
2011 (14)
  December (1)
  November (1)
  September (2)
  July (2)
  June (1)
  May (1)
  April (2)
  March (3)
  January (1)
2009 (2)
  October (1)
  June (1)
2008 (1)
  September (1)