Синхронизация пользователей из внешней базы данных по ODBC (UnSpot ODBC SCIM Adapter)

Если списки сотрудников ведутся в корпоративной системе, у которой нет готовой интеграции с UnSpot, — их можно синхронизировать напрямую из базы данных. Приложение UnSpot ODBC SCIM Adapter устанавливается на компьютер или сервер под управлением Windows, по расписанию читает данные сотрудников из любой СУБД, доступной через ODBC (Microsoft SQL Server, PostgreSQL, Oracle, MySQL и другие), и передаёт их в UnSpot по стандарту SCIM 2.0. Дополнительно приложение умеет синхронизировать оргструктуру компании и статусы в расписании сотрудников (удалённая работа, отпуск, больничный и т.п.). В этой статье описано, как устроена синхронизация, какие данные нужно подготовить в базе, как установить и настроить приложение.

Как работает приложение

Приложение состоит из двух частей в одном исполняемом файле: окна настройки и службы Windows UnSpotOdbcScimService, которая и выполняет синхронизацию. Вы один раз задаёте параметры в окне настройки и запускаете службу — дальше она работает в фоне без участия пользователя: раз в заданный интервал (от 1 до 24 часов) подключается к вашей базе данных через ODBC, забирает подготовленные данные и передаёт их в UnSpot по HTTPS.

За один цикл служба последовательно синхронизирует до трёх наборов данных:

• Пользователи (всегда) — карточки сотрудников: создание, обновление и блокировка по протоколу SCIM 2.0.
• Оргструктура (если включена) — дерево подразделений компании с руководителями, тоже по SCIM.
• Статусы расписания (если включены) — отметки «удалённая работа», «отпуск», «больничный» и другие нерабочие статусы в расписании сотрудников, через External API UnSpot.

Источником данных служат три представления (view) или таблицы с фиксированными именами и колонками, которые вы создаёте в своей базе поверх реальных данных: users_for_unspot, org_structure_for_unspot и user_schedule_for_unspot. Благодаря этому приложению не важно, как устроена ваша схема данных: любую структуру можно привести к требуемому виду обычным SQL-представлением.

Как работает синхронизация

Пользователи

Каждый цикл — это полная сверка: приложение забирает из UnSpot текущий список пользователей по SCIM и сравнивает его со строками представления users_for_unspot. Сопоставление идёт в два прохода: сначала по идентификатору (колонка id ↔ поле externalId в UnSpot), затем оставшиеся — по email без учёта регистра. По результатам сверки:

• Новые сотрудники (есть в базе, нет в UnSpot) — создаются. Email становится логином, значение id записывается в externalId.
• Изменившиеся — обновляются. Сравниваются только те поля, которые отмечены в настройках приложения: имя, фамилия, email и включённые дополнительные поля (отдел, руководитель, должность, телефон, номер пропуска, табельный номер).
• Исчезнувшие из выборки (уволенные или отфильтрованные) — удаляются из UnSpot: карточка сотрудника архивируется, пропадает из списков и теряет доступ. Активные брони снимаются, но данные и история остаются в системе.
• Вернувшиеся в выборку — если удалённый ранее сотрудник снова появился в данных, его учётная запись восстанавливается из архива по email автоматически. Настройки доступа задаются заново (группы по умолчанию, новый пароль), а если включены приветственные письма — сотрудник получит новое приглашение.

Пользователи, заведённые в UnSpot вручную (не через синхронизацию), приложение не трогает и не удаляет. Если email такого пользователя совпал с email сотрудника из базы, карточка «подхватывается» синхронизацией: в неё записывается externalId, и дальше она обновляется из базы как обычно.

Руководитель указывается в данных как email. Если на момент создания сотрудника его руководителя ещё нет в UnSpot, приложение сначала создаст всех сотрудников, а затем вторым проходом проставит руководителей — поэтому иерархия любой глубины корректно загружается за один цикл, порядок строк в выборке не важен.

Оргструктура

Если в настройках отмечена «Оргструктура», после пользователей приложение отправляет в UnSpot дерево подразделений из представления org_structure_for_unspot. Синхронизация односторонняя и полная: переданный список целиком заменяет оргструктуру в UnSpot. Каждая строка — это путь подразделения от корня с разделителем «/» (например, Компания/Департамент продаж/Отдел B2B), при необходимости — с описанием и email руководителя подразделения.

Статусы расписания

Если отмечены «Статусы расписания», приложение дополнительно передаёт в UnSpot дневные статусы сотрудников из представления user_schedule_for_unspot: удалённая работа, отпуск, больничный, командировка и любые другие нерабочие отметки, а также сброс статуса (возврат к обычному рабочему дню). Статусы отображаются в расписании сотрудника и видны коллегам. Этот поток идёт не по SCIM, а через External API UnSpot (метод обновления статуса в расписании) и требует отдельного токена — см. справочник «External API: обновление статуса в расписании».

Особенности этого потока:

• Передаются только статусы с датой в окне ±45 дней от текущего дня.
• Выборка инкрементальная: после первого успешного прогона приложение запоминает момент синхронизации и дальше берёт только строки, у которых updated_at не старше этой отметки. Отметка берётся по часам машины с приложением, а сравнение выполняет СУБД — следите, чтобы часы обеих машин были синхронизированы, иначе часть изменений может не попасть в выборку.
• Статусы отправляются по одному с паузой в 1 секунду — так устроено ограничение частоты запросов External API (1 запрос в секунду).
• Если UnSpot ответил кодом 401 (неверный или истёкший токен) или 429 (превышен лимит запросов), передача останавливается, отметка времени не сдвигается, и в следующем цикле приложение повторит незавершённую порцию.
• Учитывайте: установка статуса на дату — как и его сброс — автоматически снимает собственные брони рабочих столов сотрудника на эту дату (будущая бронь удаляется, уже начавшаяся останавливается). Броней переговорных комнат и парковочных мест это не затрагивает.

Требования

• Компьютер или сервер под управлением 64-разрядной Windows, который работает постоянно (или хотя бы регулярно включён — синхронизация выполняется, пока запущена служба).
• .NET 9. Установщик setup.exe сам проверит наличие среды выполнения и предложит установить её. Приложению нужен вариант .NET Desktop Runtime — при ручной установке среды выбирайте именно его.
• Права локального администратора — для установки, регистрации службы Windows и управления ею.
• 64-разрядный ODBC-драйвер вашей СУБД, установленный на этой машине (для Microsoft SQL Server — Microsoft ODBC Driver 18 for SQL Server или 17.x), и настроенный системный источник данных (System DSN). Пользовательские DSN службе недоступны.
• Доступ к базе на чтение: рекомендуется отдельная учётная запись СУБД только для чтения с правом SELECT на три представления. При Windows-аутентификации права нужно выдавать учётной записи, от которой работает служба (по умолчанию — системной NT AUTHORITY\SYSTEM), а не вашему пользователю.
• Исходящий доступ по HTTPS (порт 443) к домену вашей компании в UnSpot.
• Токен SCIM 2.0, а для статусов расписания — отдельный токен «Импорт расписания сотрудника». Оба создаются в консоли администратора UnSpot ролью Владелец или Администратор интеграций (см. Подключение на стороне UnSpot).
• Дистрибутив приложения (setup.exe и Setup.msi) — предоставляет команда UnSpot.

Подготовка данных в базе

Приложение читает данные из трёх объектов с фиксированными именами: users_for_unspot, org_structure_for_unspot и user_schedule_for_unspot. Это могут быть обычные таблицы, которые наполняет ваша система, или представления (VIEW) поверх реальных таблиц — второй вариант обычно удобнее: структура вашей базы может быть любой, а логика отбора (например, «только действующие сотрудники») задаётся прямо в представлении. Все значения, кроме дат, должны быть строковыми — числовые и другие типы приводите к строке в представлении. В выборках держите только актуальные данные: отсутствие сотрудника или подразделения в выборке для UnSpot означает его блокировку или удаление.

Представление users_for_unspot

Список сотрудников — по строке на человека. Колонки id, email, first_name и last_name обязательны всегда; остальные нужны, только если соответствующее поле отмечено в настройках приложения (если поле отмечено, а колонки нет — синхронизация завершится ошибкой). В обязательных колонках не должно быть NULL — такое значение прерывает весь цикл синхронизации с ошибкой (используйте NOT NULL или COALESCE в представлении). Строка, в которой обязательная колонка содержит пустую строку, просто пропускается.

Пример представления для Microsoft SQL Server (подстройте под свою схему):

CREATE VIEW users_for_unspot AS
SELECT
    CAST(e.employee_guid AS varchar(64)) AS id,
    LOWER(e.email)                       AS email,
    e.first_name                         AS first_name,
    e.last_name                          AS last_name,
    d.name                               AS department,
    m.email                              AS manager,
    e.position                           AS position,
    e.work_phone                         AS phone,
    e.badge_number                       AS number_pass,
    e.tab_number                         AS employee_id
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
LEFT JOIN employees m   ON m.id = e.manager_id
WHERE e.is_active = 1 AND e.email IS NOT NULL;

CREATE VIEW users_for_unspot AS
SELECT
    CAST(e.employee_guid AS varchar(64)) AS id,
    LOWER(e.email)                       AS email,
    e.first_name                         AS first_name,
    e.last_name                          AS last_name,
    d.name                               AS department,
    m.email                              AS manager,
    e.position                           AS position,
    e.work_phone                         AS phone,
    e.badge_number                       AS number_pass,
    e.tab_number                         AS employee_id
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
LEFT JOIN employees m   ON m.id = e.manager_id
WHERE e.is_active = 1 AND e.email IS NOT NULL;

Представление org_structure_for_unspot

Дерево подразделений — по строке на подразделение. Нужно, только если в настройках отмечена «Оргструктура». Все три колонки должны существовать в таблице (они всегда попадают в запрос) — «необязательна» ниже означает, что допустимо пустое значение. Строка, в которой path — пустая строка, пропускается; NULL в path недопустим (прерывает цикл с ошибкой).

Внутри одного родительского подразделения названия прямых дочерних подразделений должны быть уникальны: подразделение идентифицируется своим путём, поэтому два разных «Отдел продаж» под одним родителем в формате пути неразличимы и сольются в одно. Одинаковые названия на разных уровнях (например, отдел и его дочерний под-отдел с тем же именем) допустимы. Если сотрудникам передаётся поле department с иерархией, следите, чтобы пути в department совпадали с путями из этой таблицы — иначе появятся дублирующиеся ветки.

Представление user_schedule_for_unspot

Дневные статусы сотрудников — по строке на сотрудника и дату (пара user_email + date должна быть уникальна). Нужно, только если отмечены «Статусы расписания». Все колонки, кроме updated_at, всегда попадают в запрос и должны существовать в таблице; updated_at начинает использоваться со второго прогона, поэтому её отсутствие проявится не сразу — создавайте её сразу.

Обратите внимание: в отличие от пользователей и оргструктуры, удаление строки из этой таблицы ничего не меняет в UnSpot. Чтобы сбросить ранее установленный статус, передайте на эту дату строку с пустыми status_type, status_name и status_code и обновлённым updated_at — UnSpot вернёт сотруднику обычный рабочий день. И не заполняйте status_code без status_name: такие строки не отфильтровываются и будут раз за разом отклоняться сервером с ошибкой.

Подключение на стороне UnSpot

Подключение SCIM 2.0

Синхронизация пользователей и оргструктуры работает через подключение SCIM 2.0. Понадобится роль Владелец или Администратор интеграций.

1. Откройте Администрирование → Интеграции → Синхронизации и на карточке SCIM 2.0 нажмите «Подключить».
2. Скопируйте URL (вида https://ваш-домен.unspot.ru/api/scim) и секретный токен. Токен показывается только в момент создания подключения — сохраните его сразу.
3. Выберите срок работы токена: «Без ограничения» или от 1 до 24 месяцев. Если срок истечёт, синхронизация остановится с ошибкой — заранее настройте исходящую email-подписку на системные предупреждения, чтобы получить уведомление об истечении, либо выберите «Без ограничения».
4. При желании включите тумблер «Отправлять приветственные письма» — тогда каждый созданный синхронизацией сотрудник получит письмо с приглашением в UnSpot.

Полное описание возможностей SCIM-интерфейса UnSpot — в справочнике SCIM API.

Токен для статусов расписания

Для синхронизации статусов расписания нужен отдельный токен External API:

1. Откройте Администрирование → Интеграции → Настройки API.
2. Создайте подключение типа «Импорт расписания сотрудника» и скопируйте его токен.
3. URL для этого потока — https://ваш-домен.unspot.ru/api/external/v1/user-schedule.

Установка приложения

В дистрибутиве два файла установки: setup.exe и Setup.msi. Оба ставят одно и то же приложение, но рекомендуется запускать setup.exe — он предварительно проверяет, установлен ли .NET нужной версии, и при необходимости предложит его установить. Приложение устанавливается в C:\Program Files\Umbrella IT\UnSpotOdbcScim.

После установки запустите приложение от имени администратора. При первом запуске оно автоматически зарегистрирует службу Windows UnSpotOdbcScimService — её видно в оснастке «Службы» Windows. Если регистрация не удалась, приложение сообщит об ошибке в статусной строке («ошибка регистрации сервиса»).

Создание источника данных ODBC

Служба подключается к базе по строке подключения ODBC. Удобнее всего создать именованный источник данных (DSN):

1. В приложении нажмите кнопку справа от поля «Источник данных ODBC» — откроется «Администратор источников данных ODBC» Windows (программа odbcad32.exe).
2. Перейдите на вкладку «Системный DSN» и добавьте подключение к вашей СУБД. Источник обязательно должен быть системным: пользовательские DSN не видны службе Windows.
3. В поле «Источник данных ODBC» приложения укажите строку подключения: DSN=имяподключения. Для SQL Server с Windows-аутентификацией может понадобиться вариант DSN=имяподключения;Trusted_Connection=Yes;.

Доступ к базе рекомендуется настраивать через отдельную учётную запись СУБД только для чтения: создайте выделенный логин, выдайте ему право SELECT на три представления и укажите логин и пароль в настройках DSN (или в строке подключения). Так у синхронизации будет ровно тот доступ, который ей нужен.

Альтернатива — Windows-аутентификация (Trusted_Connection=Yes). В этом случае помните, что служба работает от системной учётной записи NT AUTHORITY\SYSTEM, поэтому права на чтение представлений нужно выдать именно ей. Пример из поставки для Microsoft SQL Server (выполняется администратором сервера; база в примере называется UnSpot):

ALTER SERVER ROLE sysadmin ADD MEMBER [NT AUTHORITY\SYSTEM];
USE [UnSpot];
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM];
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

ALTER SERVER ROLE sysadmin ADD MEMBER [NT AUTHORITY\SYSTEM];
USE [UnSpot];
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM];
ALTER ROLE db_owner ADD MEMBER [NT AUTHORITY\SYSTEM];

Этот пример выдаёт широкие права — для работы синхронизации достаточно права чтения (SELECT) трёх представлений, поэтому в промышленной среде сузьте права до необходимого минимума.

Настройка приложения

Окно настройки состоит из пяти секций:

1. Источник данных ODBC — строка подключения к базе (см. предыдущий раздел).
2. UnSpot — поля SCIM URL и Token из карточки SCIM 2.0.
3. Данные для синхронизации — набор синхронизируемых полей. Email, Фамилия и Имя отмечены всегда (это обязательный минимум). Дополнительно можно включить: Отдел, Руководитель, Должность, Телефон, Номер пропуска, Табельный номер, Оргструктура, Статусы расписания. Для каждого отмеченного поля в представлении должна существовать соответствующая колонка.
4. Синхронизация статусов расписания — поля URL API UnSpot и API-токен (активны только при включённых «Статусах расписания»). В заголовке секции показывается время последней успешной синхронизации статусов.
5. Сервис синхронизации — интервал между синхронизациями (от 1 до 24 часов, по умолчанию 24) и флажок «запуск при старте Windows», включающий отложенный автозапуск службы при загрузке системы.

Заполнив параметры, нажмите «Сохранить параметры», затем «Запустить сервис». Первая синхронизация начнётся сразу после запуска службы, дальше — по заданному интервалу. Текущее состояние службы видно в статусной строке («сервис запущен» / «сервис остановлен»).

Пока служба запущена, параметры изменить нельзя — кнопка «Сохранить параметры» недоступна. Чтобы изменить настройки: остановите службу, внесите изменения, сохраните и запустите службу снова. Обратите внимание: кнопка «Запустить сервис» активна только когда заполнены все обязательные поля (строка ODBC, SCIM URL и Token, интервал, а при включённых статусах — их URL и токен); иначе в статусной строке появится подсказка «заполните параметры».

Логи и диагностика

Кнопка Logs в окне приложения открывает папку с журналом работы службы: файл logs\service-log.txt в каталоге приложения. В журнале видно каждый цикл синхронизации: сколько строк выбрано из базы, сколько пользователей создано, обновлено и заблокировано, а также ошибки подключения к базе или к UnSpot. Журнал ротируется раз в день, хранятся 7 архивных файлов.

По умолчанию пишутся события уровня Info. Для разбора проблем можно включить подробный уровень Debug: в файле NLog.config в каталоге приложения замените minlevel="Info" на minlevel="Debug" в строке логирования Release — тогда в журнал попадут выполняемые SQL-запросы и содержимое запросов к UnSpot.

Удаление приложения

Приложение удаляется стандартно — через «Параметры → Приложения» (или «Установка и удаление программ»). При удалении автоматически останавливается и удаляется служба UnSpotOdbcScimService, а также очищаются файл настроек и журналы.

Связанные статьи

• Синхронизация пользователей из on-premise AD в UnSpot SaaS (сервис AD-SCIM)
• Синхронизация пользователей из 1С (UnSpot SCIM Adapter)
• Справочник SCIM API UnSpot
• External API: обновление статуса в расписании
• Обзор интеграций