Integrating ServiceNow OT Asset Workspaces with AWS IoT SiteWise Asset Fashions

In at present’s digital enterprise panorama, organizations more and more depend on asset administration options to streamline their operations. Corporations usually discover themselves managing the identical bodily belongings throughout a number of IT and operational know-how (OT) methods. One of many companies that assist IT groups observe and handle enterprise IT belongings is ServiceNow. This service handles all the things from {hardware} and software program stock to service requests, license compliance, and the entire lifecycle of know-how sources. For OT, AWS IoT SiteWise is a managed service that allows corporations to gather, set up, and analyze industrial tools information at scale. This service offers a unified repository of stay and historic operational information, empowering organizations to make data-driven selections that improve manufacturing effectivity and optimize asset upkeep.

A typical problem that organizations face utilizing ServiceNow and AWS IoT SiteWise collectively is sustaining constant asset data throughout their methods. When an asset hierarchy is up to date in ServiceNow, operations groups should manually replicate these adjustments in AWS IoT SiteWise, resulting in duplicate work and potential inconsistencies. This course of can be time-consuming, error-prone, and creates pointless overhead to handle the identical belongings in each environments.This weblog submit presents an method to synchronize asset information between ServiceNow and AWS IoT SiteWise. By implementing this integration sample, you’ll be able to get rid of handbook updates, scale back errors, and preserve constant asset hierarchies throughout your IT and OT platforms.

Resolution overview

This answer makes use of AWS companies to create an automatic integration between ServiceNow and AWS IoT SiteWise. When adjustments happen in ServiceNow’s asset administration system, they will routinely circulation by means of to AWS IoT SiteWise, guaranteeing each methods stay synchronized.

Determine 1: Structure

Determine 1 exhibits a two-phase information circulation. Within the first section (Ingest), information strikes from ServiceNow by means of Amazon AppFlow to in Amazon Easy Storage Service (Amazon S3). Within the second section (Import), the info flows by means of AWS Glue and again to Amazon S3 earlier than reaching AWS IoT SiteWise. Each phases serve totally different functions:

Ingest section:

• Amazon AppFlow pulls asset information from ServiceNow tables: Operations Expertise (OT), OT Entity, OT Entity Kind.
• The information is then saved in Amazon S3 and in parquet format.

Import section:

• AWS Glue transforms the parquet information to a JSON format that AWS IoT SiteWise can import.
• Reworked JSON information are saved in Amazon S3.
• AWS IoT SiteWise imports the asset data to create or replace asset fashions and hierarchies.

Implementation overview

This submit presents the next phases to implement this integration:

1. Configure the ServiceNow connector in Amazon AppFlow to ingest asset information into Amazon S3.
2. Create AWS Glue jobs to remodel the info from parquet to JSON to match the required AWS IoT SiteWise import format.
3. Arrange AWS IoT SiteWise asset import from Amazon S3.

Stipulations

Earlier than implementing this answer, you’ll want:

• A ServiceNow occasion with entry to your asset tables. On this instance, we use:
  • Operations Expertise (OT) (cmdb_ci_ot): Accommodates operational know-how system data from ServiceNow. These data embrace fundamental attributes like title, serial quantity, mannequin quantity, producer, and site data.
  • OT Entity (cmdb_ot_entity): Accommodates data that outline the OT entity cases and their relationships. It additionally represents how units join to one another within the operational hierarchy.
  • OT Entity Kind (cmdb_ot_entity_type): Accommodates data that outline the categories or classes of OT entities (akin to, Space, Course of Cell, Unit, and Gear Module). It additionally defines the allowed parent-child relationships within the operational hierarchy.
• Three tables work collectively to offer a whole image of OT belongings:
  • cmdb_ci_ot handles the bodily system data (configuration Objects).
  • cmdb_ot_entity manages the cases and relationships of those units.
  • cmdb_ot_entity_type defines the hierarchy construction guidelines and classes.
• An AWS account with permissions to make use of Amazon AppFlow, Amazon S3, AWS Glue, and AWS IoT SiteWise.
• ServiceNow credentials for a system-only consumer with permission to learn your asset tables.

Implementation

Configure the ServiceNow connector

On this part, you’ll arrange Amazon AppFlow to tug information from ServiceNow and configure AWS Glue to catalog the info.

Create a ServiceNow connection in Amazon AppFlow

1. Navigate to the Amazon AppFlow console.
2. Within the left menu, below Connections, select ServiceNow from the Connectors dropdown.
3. Select Create connection.
4. Within the Hook up with ServiceNow pop up, see Determine 2, enter the next:
   1. Choose both Primary Auth or OAuth2 as wanted.
   2. Fill within the vital data in accordance with the consumer information.
      1. For those who select OAuth2 fill within the Consumer ID, Consumer secret and Occasion URL in your ServiceNow Occasion.
      2. For those who select Primary Auth fill within the Username, Password and Occasion URL in your ServiceNow Occasion.
   3. Click on join as soon as all data is crammed in.

Determine 2: Hook up with ServiceNow

Create flows for every desk

1. Navigate to the Amazon AppFlow console.
2. Within the left menu, below Flows, select Create circulation.
3. Enter a Circulation Title (for instance: cmdb_ci_ot), see Determine 3, and choose Subsequent.

Determine 3: Create circulation

4. Within the Supply particulars dialog field, see Determine 4, enter the next:
   1. For Supply Title, choose ServiceNow.
   2. Make sure that the connection you created earlier is chosen below ServiceNow connection. The reference may have the title of your ServiceNow occasion, this instance makes use of “dev287617”.
   3. For ServiceNow object, choose Operational Expertise (OT).
5. Navigate to the Vacation spot particulars dialog field, see Determine 4, and enter the next:
   1. For Vacation spot title, select Amazon S3.
   2. Underneath Bucket particulars, both select your vacation spot bucket or create one by means of the Amazon S3 console. This instance makes use of the bucket prefix cmdb_ci_ot.
6. Select Subsequent.

Determine 4: Circulation supply and vacation spot

7. Within the Supply to vacation spot area mapping dialog field, see Determine 5, enter the next:
   1. Underneath Supply area title, select Map all fields instantly.
   2. Select Subsequent after which select Subsequent once more.
   3. End by selecting Run circulation.

Determine 5: Run circulation

Repeat the “Create flows for every desk” process to create a circulation for every of your tables to create connections with the opposite ServiceNow objects:

1. Circulation title and Amazon S3 prefix: cmdb_ot_entity, ServiceNow object: OT Asset.
2. Circulation title and Amazon S3 prefix: cmdb_ot_entity_type, ServiceNow object: OT Asset Kind.

Arrange and run AWS Glue Crawler to determine the schema

1. Navigate to AWS Glue console.
2. Within the left menu, below Information Catalog, select Crawlers.
3. Within the Crawlers dialog field, see Determine 6, select Create crawler.

Determine 6: AWS Glue crawlers

4. For Crawler title, use ServiceNow Crawler and select Subsequent.

Determine 7: Crawler properties

5. Select Add an information supply.
6. Within the Add information supply dialog field, see Determine 8, enter the next:
   1. For Information supply, select S3.
   2. For S3 path, select .
   3. Choose Add an S3 information supply.

Determine 8: Crawler information supply

7. Select Subsequent.
8. Underneath IAM function, choose Create new IAM function, see Determine 9.

Determine 9: Crawler IAM Function

9. Select a reputation for the function. On this instance, we use AWSGlueServiceRole-ServiceNowCrawler.
10. Choose Subsequent.
11. Underneath Goal database, select an AWS Glue Database. This instance makes use of the default database.

Determine 10: AWS Glue Database Choice

12. Select Subsequent.
13. Select Create.
14. Run the crawler. It ought to take round two minutes to finish.

The ServiceNow parquet information has now been efficiently imported into Amazon S3.

Remodel the JSON information

On this part, you’ll arrange the AWS Glue job to remodel the parquet information to JSON format appropriate for AWS IoT SiteWise and import the info into AWS IoT SiteWise.

Create AWS Glue Job

1. Navigate to AWS Glue console.
2. Within the menu to the left, below ETL Jobs, select Visible ETL.
3. In Create Job, choose Visible ETL.

Determine 11: AWS Glue studio

4. Create a Supply node. Choose the blue plus (+) button, see Determine 12, and choose Amazon S3.

Determine 12: Visible ETL

5. For Title, select any title for the node, see Determine 13. On this instance, we’ll use cmdb_ot_entity.
6. For S3 supply kind, choose Information Catalog desk.
7. For Database, select the goal database you beforehand chosen when organising your AWS Glue Crawler.
8. For Desk, select the primary desk cmbd_ot_entity. Repeat this step for every of the tables: cmdb_ci_ot and cmdb_ot_entity_type.

Determine 13: Including supply node

Map belongings to the AWS IoT SiteWise import format

1. Create a brand new Supply node by choosing the blue “+” button as proven in Determine 12.
2. Add a Remodel node and choose SQL Question.
3. For Title, use “belongings”.
4. For Node mother and father, select the supply nodes cmdb_ot_entity and cmdb_ci_ot.
5. For Enter sources and SQL Aliases, select cmdb_ot_entity and cmdb_ci_ot for every, as proven in Determine 14.

Determine 14: belongings remodel

6. For SQL Question, copy and paste the next question:

SELECT DISTINCT
    dad or mum.sys_id as assetExternalId,
    dad or mum.title as assetName,
    dad or mum.ot_asset_type as assetModelExternalId,
    COLLECT_LIST(
        CASE 
            WHEN youngster.sys_id IS NOT NULL THEN 
                STRUCT(
                    array_join(array(dad or mum.ot_asset_type, youngster.ot_asset_type), '-') as externalId,
                    youngster.sys_id as childAssetExternalId
                )
        END
    ) as assetHierarchies,
    (
        CASE 
            WHEN ot.sys_id IS NOT NULL THEN 
                array(
                    STRUCT('title' as externalId, ot.title as attributeValue),
                    STRUCT('serial_number' as externalId, ot.serial_number as attributeValue),
                    STRUCT('producer' as externalId, ot.producer as attributeValue),
                    STRUCT('model_number' as externalId, ot.model_number as attributeValue),
                    STRUCT('firmware_version' as externalId, ot.firmware_version as attributeValue),
                    STRUCT('hardware_version' as externalId, ot.hardware_version as attributeValue),
                    STRUCT('asset_tag' as externalId, ot.asset_tag as attributeValue),
                    STRUCT('class' as externalId, ot.class as attributeValue),
                    STRUCT('atmosphere' as externalId, ot.atmosphere as attributeValue),
                    STRUCT('short_description' as externalId, ot.short_description as attributeValue)
                )
            ELSE array()
        END
    ) as assetProperties
FROM cmdb_ot_entity as dad or mum
LEFT JOIN cmdb_ci_ot as ot
    ON dad or mum.ot_asset = ot.sys_id
LEFT JOIN cmdb_ot_entity as youngster
    ON dad or mum.sys_id = youngster.dad or mum
GROUP BY dad or mum.sys_id, dad or mum.title, dad or mum.ot_asset_type, ot.sys_id, ot.title, ot.serial_number, ot.producer, ot.model_number, ot.firmware_version, ot.hardware_version, ot.asset_tag, ot.class, ot.atmosphere, ot.short_description

Map the asset mannequin

7. Create a brand new Supply node by choosing the blue “+” button as proven in Determine 12.
8. Add a brand new Remodel node and choose SQL Question.
9. For Title, use “assetModels”.
10. For Node Mother and father, select the supply node cmdb_ot_entity_type.
11. For Enter sources and SQL Aliases, use cmdb_ot_entity_type for every, as proven in Determine 15.

Determine 15: assetModel remodel

12. For SQL Question, copy and paste the next question:

SELECT DISTINCT
    dad or mum.sys_id as assetModelExternalId,
    dad or mum.label as assetModelName,
    (
        CASE 
            WHEN dad or mum.ot_table IS NOT NULL THEN 
                from_json(
                '[{"dataType":"STRING","externalId":"name","name":"Name","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"serial_number","name":"Serial Number","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"manufacturer","name":"Manufacturer","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"model_number","name":"Model Number","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"firmware_version","name":"Firmware Version","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"hardware_version","name":"Hardware Version","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"asset_tag","name":"Asset Tag","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"category","name":"Category","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"environment","name":"Environment","type":{"attribute":{"defaultValue":"-"}},"unit":"-"},{"dataType":"STRING","externalId":"short_description","name":"Short Description","type":{"attribute":{"defaultValue":"-"}},"unit":"-"}]',
                    'array<struct<dataType:string,externalId:string,title:string,kind:struct<attribute:struct<defaultValue:string>>,unit:string>>'
                )
            ELSE array()
        END
    ) as assetModelProperties,
    COLLECT_LIST(
        CASE 
            WHEN youngster.sys_id IS NOT NULL THEN 
                STRUCT(
                    array_join(array(dad or mum.sys_id, youngster.sys_id), '-') as externalId,
                    youngster.title as title,
                    youngster.sys_id as childAssetModelExternalId
                )
        END
    ) as assetModelHierarchies
FROM cmdb_ot_entity_type as dad or mum
LEFT JOIN cmdb_ot_entity_type as youngster
    ON dad or mum.sys_id = youngster.parent_type
GROUP BY dad or mum.sys_id, dad or mum.title, dad or mum.label, dad or mum.ot_table

Mix the belongings and assetModels

13. Create a brand new Supply node by choosing the blue “+” button as proven in Determine 12.
14. Add a brand new node Remodel and choose SQL Question.
15. For Title, use “assetModelHierarchy”.
16. For Node mother and father, select the supply nodes belongings and assetModels.
17. For Enter sources and SQL aliases, use belongings and assetModels for every, as proven in Determine 16.

Determine 16: assetModelHierarchy remodel

18. For SQL Question, copy and paste the next question:

SELECT (
    SELECT COLLECT_LIST(STRUCT(assetModels.*)) as assetModels
    FROM assetModels
) as assetModels,
(
    SELECT COLLECT_LIST(STRUCT(belongings.*)) as belongings
    FROM belongings
) as belongings

Now, add the goal of the remodel so you’ll be able to retailer the results of our AWS Glue Job for use for importing into AWS IoT SiteWise.

Add the goal of the remodel

19. Create a brand new Supply node by choosing the blue “+” button as proven in Determine 12.
20. Add a brand new node Remodel and choose Amazon S3 from the targets.
21. For Title, use something. This instance usesAmazon S3.
22. For Node Mother and father, select the supply node assetModelHierarchy.
23. For Format, select JSON.
24. For Compression Kind, select None.
25. For S3 Goal Location, choose .

Determine 17: Including goal node

As soon as full, it is best to see the ETL that’s proven in Determine 18. Select Save.

Then choose Run and look ahead to it to complete.

Determine 18: Asset hierarchy

Import into AWS IoT SiteWise

On this part, you’ll affirm the creation of the JSON file in Amazon S3 and import the ServiceNow belongings into AWS IoT SiteWise.

1. First, affirm JSON file was created by doing the next:
   1. Open the Amazon S3 console.
   2. Choose .
   3. Choose the run--part-r-00000 file, then select Actions.
   4. Choose Rename Object, then rename it to sitewise-import.json. With a purpose to import it to AWS IoT SiteWise, the thing will need to have the json extension added to the title.
2. To import into AWS IoT SiteWise, open the AWS IoT SiteWise console.
   1. Within the navigation pane, select Bulk Operations.
   2. Choose New Import

      Determine 19: AWS IoT SiteWise bulk operations

   3. Within the Import metadata dialog field, for S3 URI, choose then the sitewise-import.json file.

      Determine 20: S3 import

   4. Choose Import and look ahead to the import to complete.

Validate your work

Now you can view the totally different fashions and mannequin properties as proven in Determine 21. You may as well view the totally different belongings and asset properties as proven in Figures 22, 23, and 24. Your ServiceNow hierarchy is now efficiently replicated into AWS IoT SiteWise.

Determine 21: AWS IoT SiteWise fashions

Determine 22: AWS IoT SiteWise mannequin properties

Determine 23: AWS IoT SiteWise belongings

Determine 24: AWS IoT SiteWise asset properties

Cleanup

To scrub up the work outlined on this weblog, navigate to the Amazon AppFlow console to delete the flows and ServiceNow connection. Delete any consumer and consumer credentials created for ServiceNow. In AWS Glue, delete the crawler, job, and tables from the AWS Glue Information Catalog. Take away the belongings and asset fashions from AWS IoT SiteWise. Lastly, delete each the parquet and reworked JSON information out of your Amazon S3 buckets.

Conclusion

This weblog offered a course of to combine ServiceNow asset information with AWS IoT SiteWise, a observe that enables organizations to take care of constant asset data throughout their IT and OT asset administration options. To totally automate this integration, schedule your Amazon AppFlow flows to run at common intervals and configure your AWS Glue job with a schedule set off. When organising the AWS Glue job, you may as well add the ‘.json’ extension to the output file by way of the ETL script. Each options get rid of handbook information entry and guarantee consistency between IT and OT methods.

Attempt implementing this answer and tell us about your expertise within the feedback part beneath. Wish to be taught extra about AWS IoT SiteWise? See the AWS IoT SiteWise Developer Information for extra data.

Concerning the authors

Maria El Khoury is a Options Architect at AWS supporting manufacturing clients of their digital transformation journey. With a background of constructing IoT and pc imaginative and prescient options, Maria is very excited by making use of AWS within the fields of Industrial IoT and provide chain.

Brent Van Wynsberge is a Options Architect at AWS supporting enterprise clients. He accelerates the cloud adoption journey for organizations by aligning technical targets to enterprise outcomes and strategic targets. Brent is an IoT fanatic, particularly within the software of IoT in manufacturing, he’s additionally excited by DevOps, information analytics and containers.