# Integration Pattern: LDAP-Sourced Worker Identity Sync — SFTP Flat File to ServiceNow User Table ## Pattern Name **LDAP-Sourced Worker Identity Sync — SFTP Flat File to ServiceNow User Table** _Also known as: Scheduled Flat File Ingestion via SFTP-to-Import Set Pipeline, Batch Identity Feed_ --- ## Intent Ingest worker identity records exported from an LDAP directory into ServiceNow's user table via a scheduled flat file pickup from an SFTP server — creating, updating, and deactivating user records to keep ServiceNow's identity data synchronized with the authoritative directory source. --- ## Motivation ServiceNow requires accurate, current user records for assignment, approval routing, access control, and self-service. Maintaining these manually is unsustainable at enterprise scale. Direct LDAP connectivity or SCIM provisioning may be unavailable, technically constrained, or organizationally out of scope. A scheduled flat file feed from the directory team provides a controlled, auditable, low-coupling mechanism for keeping ServiceNow user records synchronized without requiring real-time integration or privileged directory access from the ServiceNow instance. --- ## Applicability Use this pattern when: - An authoritative directory (Active Directory, LDAP) is the system of record for worker identity but direct connector integration is unavailable or not yet implemented - The directory or upstream HR team can produce a scheduled flat file export of worker attributes - A secure file transfer mechanism (SFTP) is available as the handoff channel between the directory team and ServiceNow - Batch/nightly synchronization cadence is acceptable for user record currency - The integration must operate across a network boundary requiring an on-prem relay --- ## Participants |Participant|Role|Technology (Example Implementation)| |---|---|---| |Directory Source|Authoritative source of worker identity attributes|Active Directory / LDAP| |File Export Process|Scheduled export of directory records to flat file|Automated export script / HR platform intermediary| |SFTP Server|Secure file handoff point between directory team and ServiceNow|On-premises SFTP server| |MID Server|On-prem relay; retrieves file from SFTP on behalf of ServiceNow instance|ServiceNow MID Server| |Scheduled Job|Triggers file retrieval and import on a recurring schedule|ServiceNow Scheduled Script Execution or Scheduled Import| |Staging Layer|Receives raw flat file rows for pre-transformation holding|ServiceNow Import Set table| |Transformation Layer|Maps flat file columns to sys_user fields; handles reference resolution and status logic|ServiceNow Transform Map (with scripted field mappings)| |User Table|ServiceNow's authoritative user record store|`sys_user`| --- ## Structure ``` Active Directory / LDAP │ │ Scheduled directory export ▼ Flat File (CSV) (worker attributes: emp ID, name, email, dept, manager, title, location, active status) │ │ Automated file drop ▼ SFTP Server (on-prem) /inbound/servicenow/workers/ │ │ ECC Queue (outbound pickup instruction) ▼ MID Server (on-prem, inside network perimeter) │ │ SFTP file retrieval ▼ MID Server │ │ ECC Queue (inbound — file contents) ▼ ServiceNow Instance ┌──────────────────────────────────────────┐ │ │ │ Import Set Table │ │ (raw flat file rows, one per worker) │ │ │ │ │ ▼ │ │ Transform Map │ │ (field mapping + scripted transforms) │ │ — coalesce on employee ID / email │ │ — manager reference resolution │ │ — active/inactive status mapping │ │ │ │ │ ▼ │ │ sys_user │ │ (create / update / deactivate) │ │ │ └──────────────────────────────────────────┘ ``` --- ## Flow ### 1. Directory Export - An automated export process runs against Active Directory / LDAP on a scheduled basis (typically nightly) - Produces a flat file (CSV most common) containing one row per active and recently inactive worker - File includes identity attributes: employee ID, first/last name, email, UPN, department, cost center, job title, manager employee ID, location, and active status flag - File dropped into a designated inbound folder on the SFTP server ### 2. Schedule Trigger - A ServiceNow **Scheduled Script Execution** or **Scheduled Import** fires on a matching cadence (typically nightly, offset to run after the upstream export completes) - Script instructs ServiceNow to retrieve the file via MID Server ### 3. File Retrieval via MID Server - ServiceNow writes an outbound file retrieval instruction to the **ECC Queue** - **MID Server**, operating inside the network perimeter, polls the ECC Queue, picks up the instruction, and connects to the SFTP server - MID Server retrieves the flat file from the designated folder and returns its contents via the inbound ECC Queue - ServiceNow instance reads the inbound ECC Queue entry and initiates the import process ### 4. Import Set Landing - Flat file rows loaded into a **source-specific Import Set table** - Each row represents one worker record as exported from the directory - Raw source values preserved at this layer; no transformation applied - Load timestamp recorded per Import Set run for traceability ### 5. Transform Map Execution - **Transform Map** fires against the Import Set table, mapping flat file columns to `sys_user` fields - **Coalesce field** (employee ID or email) used to determine whether each row is a **create** (new user) or **update** (existing user) — coalesce is the sys_user equivalent of IRE identification for non-CMDB tables - **Scripted transform entries** handle: - **Manager resolution** — manager's employee ID looked up against `sys_user` to populate the manager reference field - **Department / cost center** — string values mapped to reference fields (groups, departments) where applicable - **Active status** — directory active flag mapped to `sys_user.active`; records marked inactive in the source flip `active=false` in ServiceNow - **Role / group assignment** — optionally, job title or department attributes used to drive group membership via transform scripts ### 6. User Record Maintenance - **New workers** — `sys_user` record created with all available attributes populated - **Existing workers** — `sys_user` record updated with changed attributes; unchanged fields left as-is - **Departed / inactive workers** — `active` field set to `false`; user record retained for historical reference (assignments, approvals, audit trail) but user loses login access --- ## Tradeoffs ### Advantages - **Low coupling** — Directory team and ServiceNow team share only a file format contract and an SFTP folder; neither system has privileged access to the other - **Controlled export** — Directory team governs exactly which attributes and which records are included in the export; no risk of ServiceNow over-querying the directory - **Auditability** — Import Set records provide a row-level history of every file processed and every field value received - **Network boundary traversal** — MID Server enables file retrieval from on-premises SFTP without exposing the server to the public internet - **Operational simplicity** — Well-understood pattern with no exotic dependencies; CSV + SFTP + Import Set is a known quantity for ServiceNow administrators - **Resilience** — If the nightly export fails to produce a file, the scheduled import either finds nothing to process or can be configured to alert — no partial or corrupt state written to sys_user ### Limitations - **Batch latency** — Nightly cadence means new hires, terminations, and attribute changes are not reflected in ServiceNow until the next scheduled run; not suitable where same-day user provisioning is required - **File format fragility** — Any change to the export file's column names, ordering, delimiter, or encoding requires a matching Transform Map update; there is no schema negotiation between producer and consumer - **Manager resolution dependency** — Manager reference resolution requires the manager's record to already exist in sys_user; ordering or timing issues can leave manager fields unpopulated for new hires whose managers were also recently added - **No real-time deprovisioning** — Terminated workers retain active accounts in ServiceNow until the next nightly run; may not meet security policy requirements for immediate access revocation - **SFTP as a fragile handoff** — File pickup failures (SFTP connectivity, permissions, missing file) require explicit monitoring; ServiceNow will not alert on a missing file without custom logic - **MID Server dependency** — File retrieval fails silently if the MID Server is unavailable; requires independent health monitoring --- ## Comparison to Alternative Approaches |Approach|Coupling|Latency|Complexity|Notes| |---|---|---|---|---| |**This pattern (SFTP flat file)**|Low|Nightly batch|Low–Medium|Highest operational resilience; lowest integration complexity| |**Direct LDAP connector**|Medium|Near-real-time|Medium|Requires network access and directory read credentials from ServiceNow| |**SCIM provisioning**|High|Real-time|Medium–High|Gold standard for identity sync; requires SCIM endpoint support on both sides| |**HR system direct feed**|Medium|Batch or event|Medium|Preferred if HR system (Workday, SAP) is more authoritative than directory| --- ## Related Patterns - **Federated Device Inventory Ingestion** — Same MID Server / Import Set / Transform Map mechanics applied to CMDB CI population rather than user records - **Batch API-to-Warehouse ELT** — Similar scheduled batch extraction pattern targeting a data warehouse; API-based rather than file-based - **SCIM Identity Provisioning** — Event-driven successor pattern; replaces batch file feed with real-time push-based user lifecycle management - **HR-Driven Joiner/Mover/Leaver** — Extension of this pattern; uses worker data to trigger onboarding/offboarding workflows and role assignments in addition to user record sync --- ## Implementation Notes - Define the **coalesce field** carefully — employee ID is preferable to email as a stable unique identifier; email addresses change (name changes, domain migrations) while employee IDs typically do not - Implement a **file arrival check** at the start of the scheduled job — if no file is present in the SFTP folder, log and alert rather than silently completing with zero rows processed; a missing file should be treated as a potential upstream failure - **Archive processed files** to a dated subfolder on the SFTP server rather than deleting them — provides a recovery path if a bad file is processed and the import needs to be reversed - Handle the **manager self-reference edge case** — top-level executives may have themselves or a circular reference as manager in the directory export; transform scripts should detect and null out invalid manager references rather than failing the row - Establish a **full file vs. delta file strategy** with the directory team — full files (all active workers every night) are simpler to process and self-healing; delta files (only changed records) are smaller but require careful handling of deletes and require the upstream export to reliably detect all changes - Monitor **Import Set row counts** run-over-run — a sudden drop in row count (e.g., from 10,000 to 500) likely indicates an upstream export failure and should trigger an alert before the transform runs and mass-deactivates users - Log transform errors to a dedicated integration error table and route to an owning team — a failed manager resolution or bad data type should not silently skip the row without visibility