v10.17 (build: May 28 2024) |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Data synchronization toolThe stcsvsync tool creates an SQL script for synchronize settings based on data provided in .csv table and several types of conversion.Tool can be downloaded here
General rules
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. Common command line parameters
-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, H - Company structure, 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 user;[NB_domain];[fio];[department];[contacts];[profile] Command line parameters
Company Structure
(see referenced topic at "Global settings" here)
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.
> stcsvsync -i example.csv -t H -ci A -cp B -cn C > stcsvsync -i example.csv -t H -ci A -cp B -cn C -cu D -cnb E > stcsvsync -i example.csv -t H -ci A -cp B -cn C -cc F -cfq G > 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
Lines 2-6 tells only about groups (departments). Do notice how different results are produced for line 9 and line 10. Synchronizing managers
(see referenced topic at "Global settings" here)
The tool expects that there is a template user in the settings, whose rights will be copied when creating new users. ATTENTION: > 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 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 Employee profiles
(see referenced topic at "Global settings" here)
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 Vacations and sick/leave
(see referenced topic at "Global settings" here)
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-vacation-begin] are mandatory! Period end date [--column-vacation-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. Running SQL-scripts
You can run scripts in any familiar way, if you have one. > sqlcmd -d stkh -i script.sql -o output.txt -U dbadmin -P "*******" > sqlcmd -d stkh -i script.sql -o output.txtExample for PGSQL-tool psql: > 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). |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
© Scopd |