Integrating ServiceNow OT Asset Workspaces with AWS IoT SiteWise Asset Models

In today’s digital enterprise landscape, organizations increasingly rely on asset management solutions to streamline their operations. Companies often find themselves managing the same physical assets across multiple IT and operational technology (OT) systems. One of the services that help IT teams track and manage enterprise IT assets is ServiceNow. This service handles everything from hardware and software inventory to service requests, license compliance, and the complete lifecycle of technology resources. For OT, AWS IoT SiteWise is a managed service that enables companies to collect, organize, and analyze industrial equipment data at scale. This service provides a unified repository of live and historical operational data, empowering organizations to make data-driven decisions that enhance production efficiency and optimize asset maintenance.

A common challenge that organizations face using ServiceNow and AWS IoT SiteWise together is maintaining consistent asset information across their systems. When an asset hierarchy is updated in ServiceNow, operations teams must manually replicate these changes in AWS IoT SiteWise, leading to duplicate work and potential inconsistencies. This process is also time-consuming, error-prone, and creates unnecessary overhead to manage the same assets in both environments.This blog post presents an approach to synchronize asset data between ServiceNow and AWS IoT SiteWise. By implementing this integration pattern, you can eliminate manual updates, reduce errors, and maintain consistent asset hierarchies across your IT and OT platforms.

Solution overview

This solution uses AWS services to create an automated integration between ServiceNow and AWS IoT SiteWise. When changes occur in ServiceNow’s asset management system, they can automatically flow through to AWS IoT SiteWise, ensuring both systems remain synchronized.

Figure 1: Architecture

Figure 1 shows a two-phase data flow. In the first phase (Ingest), data moves from ServiceNow through Amazon AppFlow to in Amazon Simple Storage Service (Amazon S3). In the second phase (Import), the data flows through AWS Glue and back to Amazon S3 before reaching AWS IoT SiteWise. Both phases serve different purposes:

Ingest phase:

• Amazon AppFlow pulls asset data from ServiceNow tables: Operations Technology (OT), OT Entity, OT Entity Type.
• The data is then stored in Amazon S3 and in parquet format.

Import phase:

• AWS Glue transforms the parquet files to a JSON format that AWS IoT SiteWise can import.
• Transformed JSON files are stored in Amazon S3.
• AWS IoT SiteWise imports the asset information to create or update asset models and hierarchies.

Implementation overview

This post presents the following stages to implement this integration:

1. Configure the ServiceNow connector in Amazon AppFlow to ingest asset data into Amazon S3.
2. Create AWS Glue jobs to transform the data from parquet to JSON to match the required AWS IoT SiteWise import format.
3. Set up AWS IoT SiteWise asset import from Amazon S3.

Prerequisites

Before implementing this solution, you’ll need:

• A ServiceNow instance with access to your asset tables. In this example, we use:
  • Operations Technology (OT) (cmdb_ci_ot): Contains operational technology device records from ServiceNow. These records include basic attributes like name, serial number, model number, manufacturer, and location information.
  • OT Entity (cmdb_ot_entity): Contains records that define the OT entity instances and their relationships. It also represents how devices connect to each other in the operational hierarchy.
  • OT Entity Type (cmdb_ot_entity_type): Contains records that define the types or categories of OT entities (such as, Area, Process Cell, Unit, and Equipment Module). It also defines the allowed parent-child relationships in the operational hierarchy.
• Three tables work together to provide a complete picture of OT assets:
  • cmdb_ci_ot handles the physical device information (configuration Items).
  • cmdb_ot_entity manages the instances and relationships of these devices.
  • cmdb_ot_entity_type defines the hierarchy structure rules and categories.
• An AWS account with permissions to use Amazon AppFlow, Amazon S3, AWS Glue, and AWS IoT SiteWise.
• ServiceNow credentials for a system-only user with permission to read your asset tables.

Implementation

Configure the ServiceNow connector

In this section, you will set up Amazon AppFlow to pull data from ServiceNow and configure AWS Glue to catalog the data.

Create a ServiceNow connection in Amazon AppFlow

1. Navigate to the Amazon AppFlow console.
2. In the left menu, under Connections, choose ServiceNow from the Connectors dropdown.
3. Choose Create connection.
4. In the Connect to ServiceNow pop up, see Figure 2, enter the following:
   1. Select either Basic Auth or OAuth2 as needed.
   2. Fill in the necessary information according to the user guide.
      1. If you choose OAuth2 fill in the Client ID, Client secret and Instance URL for your ServiceNow Instance.
      2. If you choose Basic Auth fill in the Username, Password and Instance URL for your ServiceNow Instance.
   3. Click connect once all information is filled in.

Figure 2: Connect to ServiceNow

Create flows for each table

1. Navigate to the Amazon AppFlow console.
2. In the left menu, under Flows, choose Create flow.
3. Enter a Flow Name (for example: cmdb_ci_ot), see Figure 3, and select Next.

Figure 3: Create flow

4. In the Source details dialog box, see Figure 4, enter the following:
   1. For Source Name, select ServiceNow.
   2. Be sure that the connection you created earlier is selected under ServiceNow connection. The reference will have the name of your ServiceNow instance, this example uses “dev287617”.
   3. For ServiceNow object, select Operational Technology (OT).
5. Navigate to the Destination details dialog box, see Figure 4, and enter the following:
   1. For Destination name, choose Amazon S3.
   2. Under Bucket details, either choose your destination bucket or create one through the Amazon S3 console. This example uses the bucket prefix cmdb_ci_ot.
6. Choose Next.

Figure 4: Flow source and destination

7. In the Source to destination field mapping dialog box, see Figure 5, enter the following:
   1. Under Source field name, choose Map all fields directly.
   2. Choose Next and then choose Next again.
   3. Finish by choosing Run flow.

Figure 5: Run flow

Repeat the “Create flows for each table” procedure to create a flow for each of your tables to create connections with the other ServiceNow objects:

1. Flow name and Amazon S3 prefix: cmdb_ot_entity, ServiceNow object: OT Asset.
2. Flow name and Amazon S3 prefix: cmdb_ot_entity_type, ServiceNow object: OT Asset Type.

Set up and run AWS Glue Crawler to identify the schema

1. Navigate to AWS Glue console.
2. In the left menu, under Data Catalog, choose Crawlers.
3. In the Crawlers dialog box, see Figure 6, choose Create crawler.

Figure 6: AWS Glue crawlers

4. For Crawler name, use ServiceNow Crawler and choose Next.

Figure 7: Crawler properties

5. Choose Add a data source.
6. In the Add data source dialog box, see Figure 8, enter the following:
   1. For Data source, choose S3.
   2. For S3 path, choose .
   3. Select Add an S3 data source.

Figure 8: Crawler data source

7. Choose Next.
8. Under IAM role, select Create new IAM role, see Figure 9.

Figure 9: Crawler IAM Role

9. Choose a name for the role. In this example, we use AWSGlueServiceRole-ServiceNowCrawler.
10. Select Next.
11. Under Target database, choose an AWS Glue Database. This example uses the default database.

Figure 10: AWS Glue Database Selection

12. Choose Next.
13. Choose Create.
14. Run the crawler. It should take around two minutes to complete.

The ServiceNow parquet data has now been successfully imported into Amazon S3.

Transform the JSON files

In this section, you will set up the AWS Glue job to transform the parquet files to JSON format suitable for AWS IoT SiteWise and import the data into AWS IoT SiteWise.

Create AWS Glue Job

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

Figure 11: AWS Glue studio

4. Create a Source node. Select the blue plus (+) button, see Figure 12, and select Amazon S3.

Figure 12: Visual ETL

5. For Name, choose any name for the node, see Figure 13. In this example, we will use cmdb_ot_entity.
6. For S3 source type, select Data Catalog table.
7. For Database, choose the target database you previously selected when setting up your AWS Glue Crawler.
8. For Table, choose the first table cmbd_ot_entity. Repeat this step for each of the tables: cmdb_ci_ot and cmdb_ot_entity_type.

Figure 13: Adding source node

Map assets to the AWS IoT SiteWise import format

1. Create a new Source node by selecting the blue “+” button as shown in Figure 12.
2. Add a Transform node and select SQL Query.
3. For Name, use “assets”.
4. For Node parents, choose the source nodes cmdb_ot_entity and cmdb_ci_ot.
5. For Input sources and SQL Aliases, choose cmdb_ot_entity and cmdb_ci_ot for each, as shown in Figure 14.

Figure 14: assets transform

6. For SQL Query, copy and paste the following query:

SELECT DISTINCT
    parent.sys_id as assetExternalId,
    parent.name as assetName,
    parent.ot_asset_type as assetModelExternalId,
    COLLECT_LIST(
        CASE 
            WHEN child.sys_id IS NOT NULL THEN 
                STRUCT(
                    array_join(array(parent.ot_asset_type, child.ot_asset_type), '-') as externalId,
                    child.sys_id as childAssetExternalId
                )
        END
    ) as assetHierarchies,
    (
        CASE 
            WHEN ot.sys_id IS NOT NULL THEN 
                array(
                    STRUCT('name' as externalId, ot.name as attributeValue),
                    STRUCT('serial_number' as externalId, ot.serial_number as attributeValue),
                    STRUCT('manufacturer' as externalId, ot.manufacturer 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('category' as externalId, ot.category as attributeValue),
                    STRUCT('environment' as externalId, ot.environment as attributeValue),
                    STRUCT('short_description' as externalId, ot.short_description as attributeValue)
                )
            ELSE array()
        END
    ) as assetProperties
FROM cmdb_ot_entity as parent
LEFT JOIN cmdb_ci_ot as ot
    ON parent.ot_asset = ot.sys_id
LEFT JOIN cmdb_ot_entity as child
    ON parent.sys_id = child.parent
GROUP BY parent.sys_id, parent.name, parent.ot_asset_type, ot.sys_id, ot.name, ot.serial_number, ot.manufacturer, ot.model_number, ot.firmware_version, ot.hardware_version, ot.asset_tag, ot.category, ot.environment, ot.short_description

Map the asset model

7. Create a new Source node by selecting the blue “+” button as shown in Figure 12.
8. Add a new Transform node and select SQL Query.
9. For Name, use “assetModels”.
10. For Node Parents, choose the source node cmdb_ot_entity_type.
11. For Input sources and SQL Aliases, use cmdb_ot_entity_type for each, as shown in Figure 15.

Figure 15: assetModel transform

12. For SQL Query, copy and paste the following query:

SELECT DISTINCT
    parent.sys_id as assetModelExternalId,
    parent.label as assetModelName,
    (
        CASE 
            WHEN parent.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,name:string,type:struct<attribute:struct<defaultValue:string>>,unit:string>>'
                )
            ELSE array()
        END
    ) as assetModelProperties,
    COLLECT_LIST(
        CASE 
            WHEN child.sys_id IS NOT NULL THEN 
                STRUCT(
                    array_join(array(parent.sys_id, child.sys_id), '-') as externalId,
                    child.name as name,
                    child.sys_id as childAssetModelExternalId
                )
        END
    ) as assetModelHierarchies
FROM cmdb_ot_entity_type as parent
LEFT JOIN cmdb_ot_entity_type as child
    ON parent.sys_id = child.parent_type
GROUP BY parent.sys_id, parent.name, parent.label, parent.ot_table

Combine the assets and assetModels

13. Create a new Source node by selecting the blue “+” button as shown in Figure 12.
14. Add a new node Transform and select SQL Query.
15. For Name, use “assetModelHierarchy”.
16. For Node parents, choose the source nodes assets and assetModels.
17. For Input sources and SQL aliases, use assets and assetModels for each, as shown in Figure 16.

Figure 16: assetModelHierarchy transform

18. For SQL Query, copy and paste the following query:

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

Now, add the target of the transform so you can store the result of our AWS Glue Job to be used for importing into AWS IoT SiteWise.

Add the target of the transform

19. Create a new Source node by selecting the blue “+” button as shown in Figure 12.
20. Add a new node Transform and select Amazon S3 from the targets.
21. For Name, use anything. This example usesAmazon S3.
22. For Node Parents, choose the source node assetModelHierarchy.
23. For Format, choose JSON.
24. For Compression Type, choose None.
25. For S3 Target Location, select .

Figure 17: Adding target node

Once complete, you should see the ETL that’s shown in Figure 18. Choose Save.

Then select Run and wait for it to finish.

Figure 18: Asset hierarchy

Import into AWS IoT SiteWise

In this section, you will confirm the creation of the JSON file in Amazon S3 and import the ServiceNow assets into AWS IoT SiteWise.

1. First, confirm JSON file was created by doing the following:
   1. Open the Amazon S3 console.
   2. Select .
   3. Select the run--part-r-00000 file, then choose Actions.
   4. Select Rename Object, then rename it to sitewise-import.json. In order to import it to AWS IoT SiteWise, the object must have the json extension added to the name.
2. To import into AWS IoT SiteWise, open the AWS IoT SiteWise console.
   1. In the navigation pane, choose Bulk Operations.
   2. Select New Import

      Figure 19: AWS IoT SiteWise bulk operations

   3. In the Import metadata dialog box, for S3 URI, select then the sitewise-import.json file.

      Figure 20: S3 import

   4. Select Import and wait for the import to finish.

Validate your work

You can now view the different models and model properties as shown in Figure 21. You can also view the different assets and asset properties as shown in Figures 22, 23, and 24. Your ServiceNow hierarchy is now successfully replicated into AWS IoT SiteWise.

Figure 21: AWS IoT SiteWise models

Figure 22: AWS IoT SiteWise model properties

Figure 23: AWS IoT SiteWise assets

Figure 24: AWS IoT SiteWise asset properties

Cleanup

To clean up the work outlined in this blog, navigate to the Amazon AppFlow console to delete the flows and ServiceNow connection. Delete any user and user credentials created for ServiceNow. In AWS Glue, delete the crawler, job, and tables from the AWS Glue Data Catalog. Remove the assets and asset models from AWS IoT SiteWise. Finally, delete both the parquet and transformed JSON files from your Amazon S3 buckets.

Conclusion

This blog presented a process to integrate ServiceNow asset data with AWS IoT SiteWise, a practice that allows organizations to maintain consistent asset information across their IT and OT asset management solutions. To fully automate this integration, schedule your Amazon AppFlow flows to run at regular intervals and configure your AWS Glue job with a schedule trigger. When setting up the AWS Glue job, you can also add the ‘.json’ extension to the output file via the ETL script. Both solutions eliminate manual data entry and ensure consistency between IT and OT systems.

Try implementing this solution and let us know about your experience in the comments section below. Want to learn more about AWS IoT SiteWise? See the AWS IoT SiteWise Developer Guide for more information.

About the authors

Maria El Khoury is a Solutions Architect at AWS supporting manufacturing customers in their digital transformation journey. With a background of building IoT and computer vision solutions, Maria is especially interested in applying AWS in the fields of Industrial IoT and supply chain.

Brent Van Wynsberge is a Solutions Architect at AWS supporting enterprise customers. He accelerates the cloud adoption journey for organizations by aligning technical objectives to business outcomes and strategic goals. Brent is an IoT enthusiast, specifically in the application of IoT in manufacturing, he is also interested in DevOps, data analytics and containers.