Field transformation and normalisation examples
Introduction
This guide aims to explain how to use the Normalization and Transformation module (from this point forward referred to as, the Normalization module or plugin) in order to import data from Nexthink into ServiceNow according to specific company policies.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or de-compilation of this software, unless required by law for interoperability, is prohibited.
Version: 1.1.0
Last Revision: 2019-10-09
Overview
Reconciliation of existing data in CMDB and data imported from Nexthink using the CMDB Connector is possible via the usage of the Field Normalization and Transformation plugin available in ServiceNow.
The Field Normalization and Transformation plugin allows the definition of transformation rules for certain fields in specified tables. This makes it possible to apply the transformation rules before the data from Nexthink is inserted in those tables, with low impact on the import process performance.
To enable the usage of the Field Normalization by the CMDB Connector, refer to the Nexthink CMDB Connector Installation guide document.
This document includes examples of how to use the ServiceNow plugin for sample cases that might be needed for transformation of Nexthink data into existing ServiceNow formats.
Simple transformation – changing capitalization
It is possible to change the capitalization of Nexthink fields to be saved in ServiceNow tables using the Field Normalization and Transformation plugin. The Normalization plugin allows 4 different capitalization transformations: lower, UPPER, Proper (upper case for the first letter only) and Formal (Each Word Starts With Upper Case). Suppose that in your company, the policy establishes that every name must follow Formal case.
Example: The device type is “laptop” and in ServiceNow it needs to be “Laptop.”
Creating a Transformation
Go to the Field Normalization plugin and click on Transformations, see the illustration below.\
Click on the New button and fill out the form with the field information you need to normalize. Be aware of the following considerations:
The Mode should be set to active to work.
Normalize query allows ServiceNow to normalize any query over the configured field to be transformed before the query. If unchecked, then you must transform it manually in order to be able to recover the field.
Raw field is used to store original data. If you need to send back a request using this data as primary key for an external service (e.g., Nexthink Incident Management Connector) then you should create a field to store it.
In this example, we would update the user City field to be transformed to lower case, see the illustration below. After filling out the form, click on the Submit button.
Now the transformation should appear in the Transformations section. However, until now we have only defined the field we want to transform, not the actual transformation.
Defining transformations
The Normalization plugin is quite powerful. Each Transformation can handle several Transforms, which is the actual part that changes the data. See the illustration below to revisit the previous example.
There are no Transforms defined, so we will create a new one by clicking the New button. We will have to choose between several transformation options, see the illustration below.
Click on the Change case link.
For the new Transform you must insert a name, mark it as active and select the transformation mode.
The other options allow for the creation of specific conditions that are applied to the Transformation or can stop the transform process all together, see the illustration below.
Click on the Submit button to finish_._
Now the transformation is active. Any new data will be transformed just before being inserted in ServiceNow. You can click on the Test transforms link to verify that it works as expected, see the illustration below.
Handling selection lists
If your destination field is not a simple String but rather a Choice list, it is best that all the transformed values belong to the choice list. If not, the data will be inserted, but may result in inconsistencies. For example, if you want to modify it, the drop-down list will show all the valid possibilities, but not the transformed values that are not defined in that list.
To modify the choice values:
Go to the table definition System Definition -> Tables.
Search for the table and select the field.
Go to the Choices tab and add the valid values. An example for the Form factor field in the Computer table is shown in the illustration below.
Simple transformation – selecting part of a text
Replace is one of the most powerful transforms as it allows the use of regular expressions on the data. Let’s suppose that you only want data to the right of the slash (/) symbol. After creating the Transformation to select the table and field to be transformed, you must create a Replace Transform as shown in the illustration below.
In the Find field we can insert a static String or a regular expression (they start with /regex/). A full description of regular expressions in field normalization can be found in the official ServiceNow documentation. In our example we use (.*)/(.*) as the regular expression. The dot means any character, and when followed by the * it means “any occurrence of any character”. Parenthesis are used to group parts of regular expressions to be referenced in the Replace with field. So, in our regular expression we have two parts separated by a slash character. In other words, we are splitting the input data using a forward slash as the separator.
In the Replace with we can also insert a static String or use references to previous groups. In our case, $2 represent the second part of the string previously defined. We can then test the execution, as shown in in the illustration below.
Complex transformation – new data types
The Normalization plugin can transform both text and numeric data. By default, there are installed rules for transforming the following data types according to those categories:
Text: String, URL
Numeric: Integer, Decimal, Float, Numeric
Both types have associated transform definitions by default, for example: for text we have replace, change case, insert, substring, etc. Each definition is coded, and you can create new or modify existing ones. A full description of regular expressions in field normalization can be found in the official ServiceNow documentation.
Example: The date format in Nexthink is different from the date format in ServiceNow.
In order to allow transformations over other data types such as dates we must specify to which category they belong. Let’s suppose we want to modify a Date/Time field.
Select the Transform Categories.
Associate it with a text transformation in order to change the format.
Select Text and add a new Field Type, as shown in the illustration below.
In ServiceNow the type would be glide_date_time. We can choose to use this definition for transforms and for normalization. In short, a normalization is just a common name for a set of values, while a transformation changes input data according to some rules. Fields for the Date/Time type can be seen in the illustration below.
Once you have created the field type, when adding a new transformation all the fields associated to that type will appear on the Field drop-down list, see the illustration below.
Creating a transformation is similar to our previous examples. Let’s suppose that we have an input format such as dd/mm/yyyy hh:mm:ss and we want to transform it to yyyy-mm-dd hh:mm:ss. We have 2 different parts: the date that must be modified and the time that must not. We can then split the blank space by using slashes. The regular expression should be: /regex/(.*)/(.*)/(.*) (.*), so we have 4 groups. The first 3 groups represent dd, mm and yyyy, and the fourth represents the time. Be aware that there is a blank space between them.
Output should be just rearranging date groups in reverse order: $3-$2-$1 $4.
A full example is shown in the illustration below.
Complex transformation – transfer Nexthink data to several ServiceNow fields
When importing data for a third-party provider such as Nexthink, fields don’t always match exactly. It may be necessary to divide a single input data to ingest several ServiceNow fields. With the Nexthink CMDB populator plugin we can do that easily, by simply creating one import rule for each destination field and creating a transformation in each one. Below is a step-by-step example.
Example: “Last logged on user” is one field in Nexthink, but for certain configurations it might be two fields in ServiceNow, split by an @ sign, and mapped into two fields: Primary User Domain, Primary Username.
Our input data field contains information about the last user logged in on a workstation. The format is Name@Info, where the Name could be a username and the Info could be a domain. We want to split it into 2 different fields in ServiceNow: Last Logged On User and Last Logged On User Secondary. We have created both String fields in the cmdb_ci_computer table.
In ServiceNow
Select Nexthink CMDB Connector -> CI Type Fields, as shown in the illustration below.
Click the New button to create a new import rule.
Fill in the fields as shownin the illustration below. Pay special attention to SN Filed and NXT Field. The SN field identifier can be found in the ServiceNow Table definition. The Nexthink field name can be found in the Nexthink Data Model.
We do the same for the other destination field as shown in the illustration below.
The next step is to create the transformations, one for each field, as shown in the illustration below. As both transformations have the same input, we can store the raw data in just one of them.
In this example the transform associated to each transformation is a very simple replace mode, see the illustration below. The other transformation is the same except for the Replace with field which should be $2 instead $1.
Last updated
Was this helpful?
