ETL Integration Intune and Servicenow user_principal_name mapping assigned_to

[Abigail]

Hello,

I need to map the user_principal_name from Intune, which returns an email, to the Assigned To field in the Handheld Devices table.

Do you have any ideas on how I can approach this?

I'm using ETL Integration Hub.

Thanks

[KKM]

Hi Abigail,

Yes, you're using ETL Integration Hub, you can map user_principal_name (UPN) from Intune to the Assigned To field in the Handheld Devices table in ServiceNow. Here’s how you can approach it:

1. Understand the Mapping
Intune UPN (e.g., user@company.com) needs to match a user record in ServiceNow.
Assigned To in Handheld Devices is typically a reference field pointing to sys_user.

2. Approach Using ETL Transformation
Extract Data from Intune
Ensure your ETL pulls user_principal_name from Intune.
Transform Data in ETL
Use a lookup transform to find the corresponding user in sys_user:
sys_user.email = user_principal_name
If found, map the sys_id of the user to Assigned To.
Load Data into Handheld Devices
Populate assigned_to with the sys_id of the matched user.

3. Steps to Implement in ETL Integration Hub
(A) Configure Data Source
Make sure Intune UPN is part of the extracted dataset.
(B) Create a Transformation Map
Target Table: Handheld Devices
Source Field: user_principal_name (from Intune)
Target Field: assigned_to (Reference field to sys_user)
(C) Use a Lookup Rule in Transformation
Add a lookup step to find the sys_id of the matching user:
Lookup Table: sys_user
Lookup Condition: email == user_principal_name
Return Field: sys_id
Store in Target Field: assigned_to
(D) Handle Missing Users
If no matching sys_user is found:
Option 1: Skip the record.
Option 2: Log an error for manual review.
Option 3: Assign to a default user (e.g., IT Admin).

4. Validate & Test
Run the ETL job in a test environment.
Check that Assigned To is correctly populated in Handheld Devices.
Handle any unmatched users with error logging.

5. Alternative Approach: Use a Scripted Transform
If ETL lookup isn't working as expected, you can write a scripted transform to achieve the same:

var user = new GlideRecord('sys_user');
user.addQuery('email', source.user_principal_name);
user.query();

if (user.next()) {
target.assigned_to = user.sys_id;
} else {
target.assigned_to = 'default_sys_id'; // Optional: Set a default user
}

Final Thoughts

Always test with sample data before applying to production.

Kindly mark it as "Accepted Solution"/"helpful", as it resolves your query. Please press like button for the resolution provided.

With Regards,
Krishna Kumar M - Talk with AIT3ch
LinkedIn: https://www.linkedin.com/in/mkrishnak4/
YouTube: https://www.youtube.com/@KrishAIT3CH
Topmate: https://topmate.io/mkrishnak4 [ Connect for 1-1 Session]