Data synchronization tool

• Creating company structure (Hierarchy) [-t H]
• Synchronizing Managers (Subordination) [-t S]
• Employee profiles (Dossier) [-t D]
• Holidays / sick leave (Vacation) [-t V]
• Import records from Physical Access Control System (PACS) [-t P]
• Assigning settings profiles to users and computers (GroupSettings) [-t G]
• Meetings, appointments, interviews (Meetings) [-t M]
• Running SQL-scripts

Input data must be provided as .csv (divider - semicolon ";"). File encoding UTF-8.

Output data will be in UTF-8 encoding.

Errors and warnings will be sent to STDERR, output data without setting output file - to STDOUT.

Tip: if you just starting to use the tool, avoid [-of -o] parameters. In that case output format will be set as TEXT, and you'll get visually appealed data on screen, and can evaluate results before making database changes.

-i [--input] - input file. Base functions won't be available if not set.

-o [--output] - output file. Result will be seen at terminal window if not set.

-of [--output-format] - format for output data TEXT (default), MSSQL, PGSQL.

-t [--type] - type of data conversion. D - Employee Dossier, G - Group Settings, H - Company structure, M - Meetings, P - PACS, S - Managers, V - Holidays / sick leave.

-tt [--template] - get .csv template for choosen conversion type [--type].

How to get template .csv file for company structure:

> stcsvsync -t H -tt

id;parent;name;[user;[NB_domain]];[computer;[FQ_domain]]

Template for employee profiles:

> stcsvsync -t D -tt -o dos_template.csv
> cat dos_template.csv

user;[NB_domain];[fio];[department];[contacts];[profile]

ATTENTION: target SQL-script will completely delete existing company structure, and deploy new structure afterwards!

Hierarchy nodes can represent groups (departments), users and computers

You can get different kind of company structure, based on single input file.

1. Company Structure (departments only)

> stcsvsync -i example.csv -t H -ci A -cp B -cn C

2. Departments + users

> stcsvsync -i example.csv -t H -ci A -cp B -cn C -cu D -cnb E

3. Deparments + computers

> stcsvsync -i example.csv -t H -ci A -cp B -cn C -cc F -cfq G

4. Departments + user + computer (full structure)

> stcsvsync -i example.csv -t H -ci A -cp B -cn C -cu D -cnb E -cc F -cfq G

Example of full organization structure built from example.csv

> stcsvsync -i example.csv -t H -ci A -cp B -cn C -cu D -cnb E -cc F -cfq G

Company
  COMPANY\user7
  COMPUTER7.company.com
  Department 1
    COMPANY\user8
    COMPUTER8.company.com
    Sub department 1.1
      COMPANY\user5
      COMPANY\user6
      COMPUTER5.company.com
      COMPUTER6.company.com
  Department 2
    COMPANY\user9
    COMPUTER9.company.com
  Sub department 1.2
    COMPANY\user4
    COMPUTER4.company.com
    Sub department 1.2.1
      COMPANY\user1
      COMPANY\user2
      COMPANY\user3
      COMPUTER1.company.com
      COMPUTER2.company.com
      COMPUTER3.company.com

Lines 2-6 tells only about groups (departments).
Lines 7-9 besides hierarchy tells about user and computer.
Lines 10-15 don't contain any departments info, but only add users and computers.

Do notice how different results are produced for line 9 and line 10.
 Line 10 add user user4 and computer computer4 to parent Node 5 (Sub department 1.2).
 Line 9, despite having same parent 5 (Sub department 1.2), adds user3 and computer3 to Node 6 (Sub department 1.2.1), which is descendant of Node 5.
You should keep this in mind when constructing the initial table.

The tool expects that there is a template user in the settings, whose rights will be copied when creating new users.
Create this user without using the "\" character in his name (see the following note below)

ATTENTION:
 target SQL-script will delete users, whose names contain "\" character. For example DOMAIN\username.
 Tool has no way to check if template user exists or not [--db-user-template]. Otherwise, the newly created user will have empty set of rights, and you will have to set the rights manually trough the "Global settings".

> stcsvsync -i example.csv -t S -ci A -cp B -cn C -cu D -cnb E -cb H -dbu template_user

user8 is a Manager for all users and computers of Department 1 and all subsequent groups, users and computers
user5 is a Manager for all users and computers of Sub department 1.1 and all subsequent ...
user1 is a Manager for all users and computers of Sub department 1.2.1 and all subsequent ...

You can change default behavior with key [--no-depth]

> stcsvsync -i example.csv -t S -ci A -cp B -cn C -cu D -cnb E -cb H -nd -dbu template_user

user8 is a Manager for user8
user5 is a Manager for user5, user6
user1 is a Manager for user1, user2, user3

Table userinfo.csv

Command line examples:

> stcsvsync -i userinfo.csv -t D -cu A -cnb B -cf "%D %C" -cd E -ct "%F, %G" -cpr "profile1"

For FIO* [--column-fio] we use formula "%D %C". Interpreted as follows: value from 4th column, space, value from 3rd column.

*Term "FIO" means "full name" - first name, middle name, last name.

Department [--column-department] we use column number E (which means column 5). Formula "%E" will give same result.

Contacts [--column-contacts] formula "%F, %G". Value from 6th column, comma, space, value from 7th column.

Profile [--column-contacts] constant "profile1"

Output:

COMPANY\user6
        FIO:        Audrey Morgan
        Department: sales
        Contacts:   audrey@company.com, 111-222-33-49
        Profile:    profile1
COMPANY\user8
        FIO:        Bennett Ross
        Department: management
        Contacts:   bennett@company.com, 111-222-33-51
        Profile:    profile1
COMPANY\user9
        FIO:        Ella Groves
        Department: management
        Contacts:   ella@company.com, 111-222-33-52
        Profile:    profile1
COMPANY\user3
        FIO:        Harry Harrison
        Department: it
        Contacts:   harry@company.com, 111-222-33-46
        Profile:    profile1
COMPANY\user2
        FIO:        Jane White
        Department: it
        Contacts:   jane@company.com, 111-222-33-45
        Profile:    profile1
COMPANY\user1
        FIO:        John Smith
        Department: it
        Contacts:   john@company.com, 111-222-33-44
        Profile:    profile1
COMPANY\user5
        FIO:        Miles Davis
        Department: sales
        Contacts:   miles@company.com, 111-222-33-48
        Profile:    profile1
COMPANY\user4
        FIO:        Oscar Robbins
        Department: management
        Contacts:   oscar@company.com, 111-222-33-47
        Profile:    profile1
COMPANY\user7
        FIO:        William Jones
        Department: sales
        Contacts:   william@company.com, 111-222-33-50
        Profile:    profile1

ATTENTION target SQL-script will delete all from database table with info about vacations and sick/leave days and creates new records provided by .csv table.

Table vacations.csv

Command line example:

> stcsvsync -i vacations.csv -t V -cu 1 -cvb 2 -cve 3 -cvr 4

DOMAIN\j.smith:  [2024-01-07 (1) 2024-01-17] [2024-01-19 (1) 2024-01-21] [2024-01-25 (1) 2024-02-12]
DOMAIN\w.johnes:  [2024-02-01 (2) 2024-02-17]
DOMAIN\robbins:  [2024-02-12 (3) 2024-02-28]

DOMAIN\username [--column-user], period begin date [--column-value-begin] are mandatory!

Period end date [--column-value-end] can be omited (will mean one-day leave)

Reason [--column-vacation-reason] must be integer. If omited or can't be reinterpreted as integer - will be zero.

Date can be in one of following: dd.mm.yyyy, dd/mm/yyyy, dd-mm-yyyy, yyyy-mm-dd, yyyymmdd. (Here: dd - day of month [1..31], mm - month number [1..12], yyyy - year in 4 digit form)

NOTE: any cases of duplicate dates or overlapping periods will be resolved.

ATTENTION target SQL-script will NOT delete any PACS data, as far as sequential imports are supposed. In case of errors or need to repeat import of last records - contact DB administrator, make sure data to delete to contains current period and current users only!

Table pacs.csv

Command line example:

> stcsvsync -i pacs.csv -t P -cu 1 -cvb 2 -cve 3

DOMAIN\j.smith:
        [2024-01-15 09:00:00 - 2024-01-15 18:00:00] 9h
        [2024-01-16 09:00:00 - 2024-01-16 18:00:00] 9h
        [2024-01-17 00:00:00 - 2024-01-17 18:00:00] 18h
        [2024-01-21 09:00:00 - 2024-01-21 18:00:00] 9h
        
DOMAIN\w.johnes:
        [2024-01-22 08:00:00 - 2024-01-22 17:00:00] 9h
        
DOMAIN\robbins:
        [2024-01-22 08:00:00 - 2024-01-22 17:00:00] 9h

DOMAIN\username [--column-user], datetime begin (entry) [--column-value-begin], datetime end (exit) [--column-value-end] are mandatory!

Settings profile number must be set through id, use -ci with corresponding column number.

Table groupsettings.csv

Command line example:

> stcsvsync -i example.csv -t G -cu A -cnb B -cc C -cfq D -ci E

8        DOMAIN\j.smith
9        DOMAIN\k.anderson
3        DOMAIN\robbins
10       DOMAIN\w.jhones
12       LAPTOP152.domain.local
2        SERVER-FS3.domain.local
11       WSADF34RT.domain.local

Table meetings.csv

Command line example:

> stcsvsync -i meetings.csv -t M -cu 1 -cvb 2 -cve 3 -cvr 4

Datetime can be in one of following: dd.mm.yyyy hh:mm[:ss], dd/mm/yyyy hh:mm[:ss], dd-mm-yyyy hh:mm[:ss], yyyy-mm-dd hh:mm[:ss], yyyymmdd hh:mm[:ss]. (Here: dd - day of month [1..31], mm - month number [1..12], yyyy - year in 4 digit form, hh - hours [0..23], mm - minutes [0..59])

You can run scripts in any familiar way, if you have one.
NOTE: don't forget to include the database name (stkh).

Example for MSSQL-tool sqlcmd:

> sqlcmd -d stkh -i script.sql -o output.txt -U dbadmin -P "*******"

> sqlcmd -d stkh -i script.sql -o output.txt

> psql -d stkh -f script.sql -o output.txt -U postgres

ATTENTION! In case of synchronization of Managers (Subordination), after executing SQL-script you have to execute "Database configuration utility" (it will add new logins for Managers and assign them rights).