Back Story
I’m often presented with asks to enable technical users and engineers in other IT departments for the ability to allow them to easily integrate with the ServiceNow platform without a complex project workpiece being needed to design the integration. Most recently, this was with a Microsoft Power Automation piece where there was a pre-bundled connector and just required some credentials. With my security-aware hat on, I wanted to do some digging on what this connector was doing before providing any access as a bit of due diligence any developer should do before integrating.
Simply enough, this shipped connector was going to be using the Table API, one we’re all familiar with and have likely provided access to before. I consider the Table API a bit of a slapdash approach as there is no way for me to (easily) do validation on the data being inserted or updated via the API and there’s an unnerving element of trust being placed in the application to be integrated on dealing with data correctness, error handling etc.
Now you’re probably thinking, “but Kieran, you can just create a Scripted REST API (SRAPI) to deal with data validation, manipulation and upsertion into a table”. Yes, you can, and I have done that exact thing countless times….far too many times. Everytime you create a SRAPI you have to account for the customisation and the support needed for that custom endpoint. Make it too generalised, and it’s not going to be functional to every persons needs, make an endpoint per application/user and you quickly end up with 50+ SRAPIs doing similar things.
What is the Import Set API?
The Import Set API allows for you to push data into an import set table and either asynchronously or synchronously transform it. The API will then either return the results or a sys_id for you to check the results in a separate call.
Import Set Table
To leverage the Import Set API, you need an import set table in place to push data to. How you create this table is up to you as there are a few options:
- Manually (as below) - Provides the benefit of specifying the field names
- Load Data - quick and easy if you have the key:value pair format in a spreadsheet already
- Web Service - Use the SOAP inbound web service creator to auto-create a related import set table
The above is in a scoped application where I have manually created a table that extends the import set row table. Using a scoped app allows me to use simple column names rather than ones with u_ prefix. This makes life a bit easier as it allows for more human-readable payloads later on.
If you want to use a different value as the key for the import field, you can add the import_attribute_name attribute to the dictionary entry. For example, instead of a field being u_email, you can use import_attribute_name=email to effectively rename it for import purposes.
For this example, the transform map for the table is super simple for demonstration purposes. But you can still leverage transform scripts with no issue. For example, your external system might not be able to present a sys_id value but a field-level script can do the necessary GlideRecord based on the inbound data.
Import Set API Access
For the web services user account they’ll need the following permissions to successfully use the Import Set API:
- import_transformer
- snc_platform_rest_api_access - Only needed if you have strict REST API security enabled. I recommend this!
- Access to the import table if you have specific write ACLs (Otherwise access is inherited from the sys_import_set_row table which uses the import_transformer role)
Single Record Import
To insert and transform a single record, a POST request with either a JSON or XML body can be sent to /api/now/import/{staging_table_name} and the data load will occur synchronously.
Endpoint: /api/now/import/x_295070_powerauto_incident
Payload:
{
"short_description" : "Disk Failure Occured",
"description" : "Disk replacement required for server 123",
"impact" : 2,
"urgency" : 2,
"contact_email" : "mary.smith@example.com"
}Response:
{
"import_set": "ISET0010572",
"staging_table": "x_295070_powerauto_incident",
"result": [
{
"transform_map": "Incident Staging IMP TTM",
"table": "incident",
"display_name": "number",
"display_value": "INC0011139",
"record_link": "https://dev52040.service-now.com/api/now/table/incident/a9cdc1e72ff089106d30206df699b675",
"status": "inserted",
"sys_id": "a9cdc1e72ff089106d30206df699b675"
}
]
}Multiple Record Import
With the Quebec release, the Import Set API got a bit of an upgrade with the ability to insert multiple records at the same time. This is done by adding /insertMultiple onto the URI. The other change is that the data will be transformed asynchronously.
Endpoint: /api/now/import/x_295070_powerauto_incident/insertMultiple
Payload:
{"records" : [{
"short_description" : "Disk Failure Occured",
"description" : "Disk replacement required for server 123",
"impact" : 2,
"urgency" : 2,
"contact_email" : "mary.smith@example.com"
},{
"short_description" : "Disk Failure Occured",
"description" : "Disk replacement required for server 856",
"impact" : 2,
"urgency" : 2,
"contact_email" : "sam.collins@example.com"
}]
}Response:
{
"import_set_id": "7b2399272f3001106d30206df699b68c",
"multi_import_set_id": "0c3399272f3001106d30206df699b68d"
}You’ll note here, we get a much simpler body. If you want the behaviour to be synchronous for the /insertMultiple API, you’ll need to create a record in the sys_rest_insert_multiple table and set the mode to synchronous. Note, this could potentially result in long time API calls and a knock on performance impact. A better option is to use the GET Import Set API operation to get the result for that row using the import_set_id field as the query parameter. Two API calls is more performative than a single API call that holds a transaction for a long time. Remember, your instance can only handle so many API calls at a time, so the longer an API call is, the higher chance you have of hitting that concurrency limit.
Custom Response Message
One of the arguments for creating a SRAPI is the ability to set error messages and custom response payloads. The Import Set API takes care of this potential argument and allows you to extend the response per row.
To do this, use an onComplete transform script and set either status_message , error_message, or a custom value on the response object. The below script demos using these variables:
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
status_message = "Imported Row Successfully";
response.contact_sys_id = target.getValue('caller_id');
response.category = target.getDisplayValue('category');
})(source, map, log, target);Now the response looks like this:
{
"import_set": "ISET0010574",
"staging_table": "x_295070_powerauto_incident",
"result": [
{
"transform_map": "Incident Staging IMP TTM",
"table": "incident",
"display_name": "number",
"display_value": "INC0011144",
"record_link": "https://dev52040.service-now.com/api/now/table/incident/18c815a32f7001106d30206df699b642",
"status": "inserted",
"sys_id": "18c815a32f7001106d30206df699b642",
"status_message": "Imported Row Successfully", //Added by script
"contact_sys_id": "8d7d05a72ff089106d30206df699b6b8", //Added by script
"category": "Inquiry / Help" //Added by script
}
]
}The error_message variable can only be used in an onBefore as part of aborting the current row import
(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
error = true;
error_message = "No Assignment Group";
})(source, map, log, target);{
"import_set": "ISET0010574",
"staging_table": "x_295070_powerauto_incident",
"result": [
{
"transform_map": "Incident Staging IMP TTM",
"table": "incident",
"status": "error",
"error_message": "No Assignment Group; Target record not found"
}
]
}Import Set API Security - What I do
The following is what I like to do when using the Import Set API as part of security measures to reduce any potential risk:
- Add a custom write ACL to the import set table created to restrict access to only specific users. Without this, anyone with the import_transformer could write to any of the import tables. This might not be desired if you’re triggering a sensitive activity on one of the tables or providing third party access.
- Add a rate limit rule to each table and user to reduce any potentials for floods of tickets. This saves needing to do any checks in an onBefore script and returns an HTTP429 error to the caller.
- Where possible, validate the data input in onBefore script(s) before allowing any inserts or updates.
Takeaways
I personally love this API, even more so as of Quebec with the insert multiple ability. It’s quick, secure and highly scalable and allows for a lot of worries to be easily mitigated. It also allows for a fairly low-code approach which is beneficial to newer ServiceNow professionals.
This API is my default go-to when architecting an integration that requires data inserts. It also works with robust transform maps which makes the ordeal of dealing with CI data via APIs a little bit sweeter.
If you have any questions or use cases, drop them in the comments below!
If you found this article useful, I’d appreciate a thumbs up.
Something that was not obvious to me, is after you have defined your table which derives from "sys_import_set_row", AND you have defined a Transform Map which links that table to your target table, AND you have done "auto map matching fields" or defined field maps manually... THEN when the API is utilized, the Transform Map kicks in automatically and stuffs a record into your final target table. I was thinking I would need to run the transform manually or configure or write something extra to hook that up, but it happens automatically. Thanks.
Additionally, when I tried to follow your example, and insert multiple records (ROME version), it wasn't working, and I had a heck of a time figuring out why. In reviewing the documentation here I noticed this:
"You can modify mapping settings by adding an entry in the Rest Insert Multiples [sys_rest_insert_multiple] table and changing the Column mapping from Label to Column name."
Very strange IMO that this is necessary, but I was able to get that working as follows:
@yt ccq You have to manually build a query for it (Bear in mind that I set it to synchrounous instead of async for my table. What I did was something along this:
/api/now/table/sys_import_set_row?sysparm_query=sys_import_set={sys_id of import set from payload}%5Esys_transform_map%3D{sys_id of transform map you want to filter by}&sysparm_display_value=true&sysparm_exclude_reference_link=true&sysparm_fields=sys_import_state_comment%2Csys_import_set%2Csys_import_state using both rest api explorer and manually typing some stuff. Giving me this when I query the import_set_id from the payload, I also added a status_message in an onAfter transform script so it gives them some info in sys_import_state_comment:
