Импорт списка сотрудников из AD в МФУ Canon-4035 с помощью Oracle

Недавно нужно было каким-то образом реализовать импорт пользователей в Universal Login Manager МФУ Canon-4035(авторизация по проксимити карточке, учет заданий сотрудника/департамента и другие полезные вещи). Пользователей можно импортировать с помощью .csv файла, который имеет следующую структуру

loginname, mail, cardid, homefolder, pincode, password, deptid, roles,displayindex
Loginname — логин, с помощью которого пользователь регистрируется в системе
mail — электронный адрес сотрудника
cardid — номер проксимити карточки, с помощью которой можно авторизироватся в системе
pincode — пин-код пользователя
password — пароль
deptid- департамент, которому принадлежит сотрудник
roles — роль сотрудника(administrator, regular user, power user, etc)
displayindex — Номер дисплея пользователя
Задача стояла в том, чтобы максимально быстро «залить» в систему даные сотрудников, максимально автоматизировав процесс.
Подумав некоторое время было придумано следующее решение. Поскольку наиболее четкая структура пользователей находится в AD, то было принято решение брать информацию(электронные адреса/логины/департаменты) именно оттуда.

Итак, этап 1 — Экспорт данных сотрудников из AD.
Для этого используем утилитой csvde.
На контроллере домена в командной строке запускаем следующую строчку:

cmd>csvde -f d:\exportnew_1.csv -d "ou=1it,dc=domen,dc=com" -r "((objectCategory=person))" -l "displayName, mail"

— если убрать ou=1it, то экспорт будет осуществляется не по конкретной организационной единице, а по всему домену.
Далее полученный csv-файл немного видоизменяем: удаляем первую строчку и поиском/заменой удаляем CN= (хотя это можно было сделать с помощью ф-и Oracle)

Этап 2 — обработка данных csv-файла в БД Oracle и преобразование в необходимые выходные данные.
Поскольку я изучаю Oracle, мне было интересно обработать полученные данные именно в БД Oracle. Поэтому подготавливаем БД для импорта данных из csv-файла:
Создаем папки для экспорта, логирования, и неправильных загрузок

CMD> mkdir d:\test\export d:\test\log d:\test\bad

В папку d:\test\export ложим наш csv-файл.

SQL>create directory dat_dir as 'd:\test\export'; -- создаем директорию для экспорта
SQL>create directory log_dir as 'd:\test\export\log'; --папка логирования
SQL>create directory bad_dir as 'd:\test\export\bad'; --папка плохих загрузок

Создаем таблицу в которую мы будем считывать данные из csv-файла

create table dep (uname nvarchar2(40), ou varchar2(20), dc VARCHAR2(15), dc_1 VARCHAR2(40), x VARCHAR2(80), mail VARCHAR2(40))
ORGANIZATION EXTERNAL
(
type oracle_loader
default directory dat_dir
access PARAMETERS
(
records delimited by newline
badfile bad_dir:'dept.bad'
logfile log_dir:'dept.log'
fields terminated by ','
missing field values are null
(uname,ou,dc,dc_1,x, mail)
)
location ('dept.csv')
)
parallel
reject limit UNLIMITED;

Переименовываем файл exportnew_1.csv в dept.csv. Создаем таблицу, заполняя ее данными из файла dept.scv

create table canon_user as(
select d.uname, substr(d.mail,0,length(d.mail)-11) loginname, d.mail mail,
decode(d.ou,'1Administration','03286','1Custom','17016','1Fin','11006','1Fincontrol','11006','1HR','33036','1IT','196564','1Law','75774','1Metrograd','14104','1Purchase','26917','1Sale','60669','9975') pincode,
decode(d.ou,'1Administration','03286','1Custom','17016','1Fin','11006','1Fincontrol','11006','1HR','33036','1IT','196564','1Law','75774','1Metrograd','14104','1Purchase','26917','1Sale','60669','9975') password,
decode(d.ou,'1Administration','2','1Custom','13','1Fin','5','1Fincontrol','5','1HR','8','1IT','10','1Law','12','1Metrograd','28','1Purchase','6','1Sale','3','55') deptid,
d.ou
from dep d
where d.mail is not null
and d.ou not in ('0SPrk','Disable Users','!CONTACTS','Users','0Ecan','1Security','1Skd','1Transport')
);

Здесь каждой организационной единице присваивается пинкод, он же является и паролем(по желанию, они могут быть разными), логином. Мы исключили некоторые департаменты.
Дальше формируем финальный вариант набора данных

select 'loginname','mail','cardid','homefolder','pincode','password','deptid','roles','displayindex'
from dual
union
select c.loginname, c.mail,'','', c.pincode, c.password, c.deptid,'GeneralUser', '1'
from canon_user c
order by 7;

Экспортируем полученную таблицу в scv-файл. Открываем текстовым редактором, функцией поиска/замены удаляем из файла ' и ". Сохраняем.
Данный файл можно импортировать в Universal Login Manager.
Поскольку на организации есть система СКУД на базе Fortnet, то можно было сопоставить ФИО из AD и в системе СКУД, но формат карточек, получаемые на считывателе СКУД и МФУ был разный, поэтому, первый раз сотрудник с помощью проксимити карты, логина и пароля обучал МФУ. Последующий раз он авторизивровался с помощью одной карточки.
Мануал по Universal Login Manager