Usercube-CSV-Transform
Examples
Define a primary key
Consider the file C:/identitymanagerContoso/Sources/hr_example.csv with the following headers line:
Login,Company,Email,FirstName,LastName
To avoid having too much duplicated information on each line in a CSV file, we need to define a
primary key for the file which will allow the pooling of common information. We choose to
concatenate the values of the columns Login and Company with a - as separator in an Id
column, which will be defined as key for our file.
--input-file C:/identitymanagerContoso/Sources/hr_example.csv --columns-concat "Login Company - ID"
--columns-key ID
Handle multi-valued columns in a generated file
Consider the file C:/identitymanagerContoso/Sources/hr_example123.csv with the following headers line
separated by a ;:
GroupAzure;Members;GroupSharePoint;Members
This file is automatically generated by a script and the suffix (123here) is incremented on each
generation. Thus, we need to use a regex to avoid changing the command line for each new generated
file.
--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ;
The file contains two headers with the same name, each related to one kind of group. Thus, we need to rename one of these headers.
--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ; --headers-edit-name "Members MembersAzure"
In this example, we will consider that the two Members columns contain all members for each group
separated by a , for the first Members column, and by a * for the second one. We need to
transform these columns in Usercube's format for multi-valued attributes.
--input-file C:/identitymanagerContoso/Sources/hr_example(.*?).csv --regex --separator ; --headers-edit-name "Members MembersAzure" --columns-multivalued "MembersAzure ," "Members *"
Arguments
| Argument Name | Details |
|---|---|
| --input-path required | Type String Description Specifies the CSV file to modify. Example Define C:/identitymanagerContoso/Sources/hr_example.csv as input file: --input-file C:/identitymanagerContoso/Sources/hr_example.csv. |
| --output-path optional | Type String Description Specifies the output path, which is the exports' output path by default. Example Define C:/identitymanagerContoso/Test as output folder: --output-path "C:/identitymanagerContoso/Test". |
| --new-name optional, required if --regex is true | Type String Description Specifies the new name for the output file. Example Define new name hr_transformed.csv: --new-name hr_transformed.csv. |
| --input-file-encoding default value: UTF-8 | Type String Description Encoding of the input file. See the list of available encodings. Example --input-file-encoding UTF-16. |
| --headers-edit-index optional | Type String List Description Specifies the headers to edit by index, which is particularly useful to rename empty headers. Each member of the list is written like index newHeader. Example Add or replace header at index 1 with ExampleHeader : --headers-edit-index "1 ExampleHeader". |
| --headers-edit-name optional | Type String List Description Specifies the headers to rename (first found) with the new name. Each member of the list is written like currentHeader newHeader. Example Rename headers CompanyId into Company and int32_1 into int32: --headers-edit-name "CompanyId Company" "int32_1 int32". |
| --headers-remove-index optional | Type Integer Description Specifies the headers to remove by index. This command can be used to remove the second occurrence of a duplicate header by specifying its index. Example Remove header located at index 5: --headers-remove-index 5. |
| --headers-remove-name optional | Type String List Description Specifies the headers to remove by name (first found). Example Remove first occurrences of headers date1 and bool1: --headers-remove-name date1 bool1. |
| --new-headers optional | Type String List Description ONLY for files without headers, specifies the new headers except the ones created by the concatenation of columns. Example Defines header1 and header2 as headers of the file: --new-headers header1 header2. |
| --columns-concat optional | Type String List Description Specifies the columns to concatenate and how. Each member of the list is written like column1Header column2Header. If you want to specify characters between the column values, you can write column1Header column2Header charactersBetween. This operation creates a new column where it puts the result of the concatenation. This column header is the concatenation of the headers, but you can change it by writing the member like column1Header column2Header charactersBetween newColumnHeader. Example Concatenate columns: - Company and Employee with a - between them. ID will be the new column header. - guid1 and bytes1 with _ between them. - int32_2 and int64_2 with nothing in between. --columns-concat "Company Employee - ID" "guid1 bytes1 _" "int32_2 int64_2" . |
| --columns-multivalued optional | Type String List Description Specifies the columns with multi-valued values not splittable with breaks. Each member of the list is written like columnHeader separator. Example Handle columns multivalued1, using separator ,, and multivalued2, using separator *: --columns-multivalued "multivalued1 ," "multivalued2 *". |
| --columns-date optional | Type String List Description Specifies the columns with date values, and their date format, to format them into Usercube's format. Each member of the list is written like columnHeader dateFormat. Example Format date columns date1 and date2, using the format yyyyddMMHHmmss: --columns-date "date1 yyyyddMMHHmmss" "date2 yyyyddMMHHmmss". |
| --columns-bool optional | Type String List Description Specifies the columns with Boolean values to convert them into Usercube's format. Example Format Boolean columns bool1 and bool2: --columns-bool bool1 bool2. |
| --columns-int32 optional | Type String List Description Specifies the columns with Int32 values to convert them into Usercube's format. Example Format Int32 columns int32_1 and int32_2 : --columns-int32 int32_1 int32_2. |
| --columns-int64 optional | Type String List Description Specifies the columns with Int64 values to convert them into Usercube's format. Example Format Int64 columns int64_1and int64_2: --columns-int64 int64_1 int64_2. |
| --columns-guid optional | Type String List Description Specifies the columns with Guid values to convert them into Usercube's format. Example Format Guid columns guid1and guid2: --columns-guid guid1 guid2. |
| --columns-bytes optional | Type String List Description Specifies the columns with Bytes values to convert them into Usercube's format. Example Format Bytes columns bytes1 and bytes2: --columns-bytes bytes1 bytes2. |
| --columns-key optional | Type String List Description Specifies the columns key to delete duplicates (the first line found is the one we keep). A column created by this tool can be specified as a key column through this argument, like the columns created by the --columns-concat for example. Example Define columns RawId and ID as keys: --columns-key RawId ID. |
| --- | --- |
| --regex optional | Type No Value Description The file name is a regex so we find the last generated corresponding file. |
| --separator optional | Type String Description Defines the separator if different than ,. |
Usercube-Export-Bacpac
This tool exports the database to a bacpac file, as a backup.
Examples
The following example generates to <C:/identitymanagerDemo> a bacpac file from the Usercube database with
the given connection string and based on the bacpac template from the SQL folder.
Code attributes enclosed with <> need to be replaced with a custom value before entering the
script in the command line.
./identitymanager-Export-Bacpac.exe --database "<Usercube>" -s "<data source=.;Database=Usercube;Integrated Security=SSPI;Min Pool Size=10;encrypt=false;>" --bacpac-path 0 --template-bacpac-path "<C:/identitymanagerDemo/SQL>"
Arguments
The list of arguments:
| Argument Name | Type | Description |
|---|---|---|
| --database-connection-string (-s) required | String | Connection string of the database. |
| --database required | String | String Name of the database. |
| --template-bacpac-path required | String | Path of the empty bacpac file containing the database schema. NOTE: The template is provided among Usercube's artifacts, and can be generated manually by exporting an empty Usercube database as a bacpac file. |
| --temp-bacpac-path optional | String | String Path of the temporary folder storing the database's data. |
| --bacpac-path required | String | String Path of the generated bacpac file. |
| --without-history default value: false | Boolean | true to exclude history data. |
| --without-job-instances default value: false | Boolean | Boolean true to exclude job and task instances. |
| --without-workflow-instances default value: false | Boolean | Boolean true to exclude workflow instances. |
| --without-campaign-instances default value: false | Boolean | Boolean true to exclude access certification campaign items. |
| --without-temp default value: false | Boolean | Boolean true to exclude the data of temporary tables. |
| --without-all default value: false | Boolean | Boolean true to exclude history data, job and task instances, workflow instances and access certification campaign items. Remember, this option represents the usual use-case. |
| --log-level optional | LogLevel | Level of log information among: Verbose; Debug; Information; Warning; Error; Fatal. |
Usercube-Export-Csv
Examples
Exporting a file respecting the default parameters
Consider the file C:/identitymanagerContoso/Sources/hr_example.csv with , as separator and UTF8
encoding, it can be exported with the command:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.csv --ignore-cookies --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput
The output file will be located in C:/identitymanagerContoso/Temp/ExportOutput/HREXAMPLE.csv and the
content will be a copy of hr_example.csv's one and an UTF8 encoding.
Define a separator
Consider the file C:/identitymanagerContoso/Sources/hr_example.csv with ; as separator.
As , is considered to be the default separator, we must set it:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.csv --ignore-cookies --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --separator ;
The result's content will be the same but with , as separator.
Use a regex file name
Consider that you deal with a generated file that follows the regex:
C:/identitymanagerContoso/Sources/hr_example(.*?).csv, for example
C:/identitymanagerContoso/Sources/hr_example5fH8g1.csv. If several files match with the regex, the
executable uses the last one that was generated.
You can put your regex and precise that it is one with the --regex argument:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example(.*?).csv --ignore-cookies --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --regex
Use the Path Duality and the Not-Launch-Export System
In a larger context, the export might be used for complete or incremental synchronization. That is
why it has two paths: --raw-files-path for complete synchronizations, --path-incremental for
incremental ones.
In the export's scope, it only means one thing, what path must be used depends on
--ignore-cookies: its presence meaning that we are in a complete synchronization context and we
use --raw-files-path; its absence that we are in an incremental one and we use
--path-incremental.
It means that if the user gives --ignore-cookies and not --raw-files-path, or if they give
neither --ignore-cookies nor --path-incremental, the export will not be launched to prevent any
problem (complete data for an incremental synchronization for example). The --force-complete
argument bypasses this security: in the product, it is used for the initialization job, where we
want to perform a complete synchronization, even for CSV connections with only an incremental path.
Arguments
| Argument Name | Details |
|---|---|
| --- | --- |
| --connection-identifier optional | Type String Description Connector's connection identifier. The output file will have this identifier as name. |
| --output-path required | Type String Description Output path for the files generated by the export. |
| --- | --- |
| --ignore-cookies optional | Type No Value Description Specifies the synchronization mode, its presence meaning complete, its absence incremental. |
| --- | --- |
| --regex optional | Type No Value Description The file name is a regex so we find the last generated corresponding file. |
| --separator optional | Type String Description Defines the separator if different than ,. |
Usercube-Export-EasyVista
This tool is made to export entities from an EasyVista instance to CSV files.
The hardcoded entities named Employees can be fetched directly using the URL of the EasyVista
instance. To export other entities, you have to create a view of your data on EasyVista to be able
to fetch an internalquery.
Examples
Exporting entities by specifying attributes
It is possible to export data by specifying the attribute names to fetch, for each exported table:
--url "https://test-fr-vp-01.easyvista-training.com" --login "usercube" --password "usercube2021" --connection-identifier "ConnectionEasyVista" --attributes "table1=[NAME_FR, LOGIN]|table2=[NAME_EN, PROFIL]|Employee=[EMPLOYEE_ID]" --output-path "C:/EasyVistaExport" --account "{account}" --cookie-path "C:/EasyVistaExport" --log-level Verbose --fetching-urls "HTTPS://test-fr-vp-01.easyvista-training.com/api/v1/50011/internalqueries?queryguid={3226F4FE-F3FC-4301-965A-32E546707BD0}&filterguid={9F3146C5-4EE0-4D1A-A4B9-8DC87A63C4E4}&viewguid={99E2223F-C1E0-4A14-87E8-C39C14325C03}" "HTTPS://test-fr-vp-01.easyvista-training.com/api/v1/50011/internalqueries?queryguid={57667FCD-134B-48A7-A188-CE700EF02C15}&filterguid={B4B3A15D-1DE2-41B5-91A7-A8E8343784E1}&viewguid={DB9C013B-28E0-45C8-A4C2-79E7D43C5421}" --entity-names "table1" "table2"
Exporting entities using entities defined in configuration
It is also possible to export data by specifying the Usercube's server URL, so the export tool automatically fetches the entity type mapping property names linked to the specified connection:
--url "https://test-fr-vp-01.easyvista-training.com" --login "usercube" --password "usercube2021" --connection-identifier "ConnectionEasyVista" --output-path "C:/EasyVistaExport" --account "{account}" --cookie-path "C:/EasyVistaExport" --log-level Verbose --fetching-urls "HTTPS://test-fr-vp-01.easyvista-training.com/api/v1/50011/internalqueries?queryguid={3226F4FE-F3FC-4301-965A-32E546707BD0}&filterguid={9F3146C5-4EE0-4D1A-A4B9-8DC87A63C4E4}&viewguid={99E2223F-C1E0-4A14-87E8-C39C14325C03}" "HTTPS://test-fr-vp-01.easyvista-training.com/api/v1/50011/internalqueries?queryguid={57667FCD-134B-48A7-A188-CE700EF02C15}&filterguid={B4B3A15D-1DE2-41B5-91A7-A8E8343784E1}&viewguid={DB9C013B-28E0-45C8-A4C2-79E7D43C5421}" --entity-names "table1" "table2" --api-url "http://localhost:5000" --api-client-id Job --api-secret secret
For each exported table, there is a resulting CSV file containing exported data.
The server has to be running.
Arguments
| Argument Name | Details |
| ----------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------- |
| --attributes optional | Type String list Description List of attributes to enrich the research. Format is: -at "Table1=[last_name, begin_of_contract, department_id, location_id] | Table2=[profile_id, e_mail]" |
| --fetching-urls required if --entity-names is set | Type String list Description The specific URLs to fetch data, corresponding to entity names. It must be the same length and have the same order as --entity-names. |
| --entity-names required if --fetching-urls is set | Type String list Description The corresponding table names to fetch data, corresponding to fetching URLs. It must be the same length and have the same order as --fetching-urls. |
| --url required | Type String Description EasyVista API Endpoint URL. |
| --account required | Type String Description EasyVista account. |
| --login required | Type String Description Path of the file used for complete synchronization. |
| --password required | Type String Description EasyVista server password. |
Usercube-Export-Excel
Examples
Exporting a file respecting the default parameters
Consider the file C:/identitymanagerContoso/Sources/hr_example.xlsx with UTF8 encoding, it can be
exported using these command's arguments:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.xlsx --not-incremental --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput
The output file(s) will be located in C:/identitymanagerContoso/Temp/ExportOutput/. Their number
corresponds to the number of sheets in the XLSX file and they would be labeled: HREXAMPLE_0.csv,
HREXAMPLE_1.csv, � HREXAMPLE_n-1.csv where n corresponds to the amount of spread sheets of the
XLSX file. The encoding is UTF8 and the separator is ,.
Skipping some file's lines
The possibility to skip lines is made available using the --lines-to-skip argument:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.xlsx --not-incremental --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --lines-to-skip 10
As a consequence, the exported file would include the content of the XLSX file without the ten first lines.
Regex in file name
Considering a generated file following the regex: C:/identitymanagerContoso/Sources/hr_example(.*?).xlsx,
for instance C:/identitymanagerContoso/Sources/hr_example5fH8g1.xlsx, if several files match with the
regex, the executable would use the most recent one.
The regex can be included in the filename and would need to be precised using the --is-regex
argument:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example(.*?).xlsx --not-incremental --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --is-regex
Choosing value to trim
It's possible to precise characters to trim using the --values-to-trim argument:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.xlsx --not-incremental --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --values-to-trim e
The CSV output file will see every words beginning and ending by "e" (lower-case, this process is case sensitive) removed of this letter.
Ignoring particular sheets
The --sheets-ignored argument allows the user to specify for each sheet if it should be ignored
during the export. More precisely, a list of true or false arguments should be specified
respectively to the sheets. Let's say the C:/identitymanagerContoso/Sources/hr_example.xlsx file
possesses three sheets, in order to export the first and the last ones the arguments would be:
--raw-files-path C:/identitymanagerContoso/Sources/hr_example.xlsx --not-incremental --connection-identifier HREXAMPLE --output-path C:/identitymanagerContoso/Temp/ExportOutput --sheets-ignored false true true false
Thus, two CSV files would be created corresponding to the the chosen ones: HREXAMPLE_0.csv and
HREXAMPLE_3.csv.
Path Duality and the Not-Launch-Export System
The export executable might be used for a complete or an incremental synchronization. Thus, it
possesses two paths that could be precised - depending on the case - with the --raw-files-path for
complete synchronizations argument or the --path-incremental for incremental ones.
At the end of the day, the --not-incremental argument defines the export behavior: if present it
means a complete synchronization should be performed and the --raw-files-path argument must be
precised; if missing an incremental synchronization would be performed using --path-incremental.
It means that if the user provide the --not-incremental argument and no --raw-files-path, or if
the user doesn't provide --not-incremental nor --path-incremental, the export will not be
launched to prevent any issue (complete data for an incremental synchronization for instance). The
--force-complete argument bypasses this safeguard: during the initialization job for example,
where we want to perform a complete synchronization, even for Excel connections with only an
incremental path.
Arguments
| Argument Name | Details |
|---|---|
| --not-incremental optional | Type No Value Description Specifies the synchronization mode, its presence meaning complete, its absence incremental. |
| --is-regex optional | Type No Value Description The file's name is a regex so we find the last generated corresponding file. |
| --- | --- |
| --connection-identifier optional | Type String Description Connector's connection identifier. The output file will have this identifier as name. |
| --output-path required | Type String Description Output path for the files generated by the export. |
Usercube-Export-Scim
This tool is made to export entries from a SCIM API to CSV files.
Examples
Exporting entities by specifying attributes
It is possible to export data by specifying the attribute names to fetch, for each exported table:
--server "https://scim-server.com" --connection-identifier "ConnectionSCIM" --output-path "C:/SCIMExport" --cookie-path "C:/SCIMExport" --log-level Verbose --login "usercube" --password "usercube2021" --filter-entities "Users|username eq \"john\";username name:givenName|familyName"
Exporting entities using entities defined in configuration
It is also possible to export data by specifying the Usercube's server URL, so the export tool automatically fetches the entity type mapping property names linked to the specified connection:
--server "https://scim-server.com" --login "usercube" --password "usercube2021" --connection-identifier "ConnectionSCIM" --output-path "C:/SCIMExport" --cookie-path "C:/SCIMExport" --log-level Verbose --api-url "http://localhost:5000" --api-client-id Job --api-secret secret
The server has to be running.
Exporting entities with a token authentication
It is possible to export data by specifying the attribute names to fetch, for each exported table:
--server "https://scim-server.com" --connection-identifier "ConnectionSCIM" --output-path "C:/SCIMExport" --cookie-path "C:/SCIMExport" --log-level Verbose --oauth-token "MyToken" --filter-entities "Users|username eq \"john\";username name:givenName|familyName"
Arguments
| Argument Name | Details |
|---|---|
| --cookie-path required | Type String Description Path of the cookie file for incremental export. |
| --filter-entities optional | Type String Description List of the non group entities and corresponding attributes to export. Syntax: EntityNameInSCIM1 |
| --filter-entities-group optional | Type String Description Works as �filter-entities but for entities with members. Syntax: EntityNameInSCIM1 |
| --attributes-in-filter optional | Type No Value Description Specifies true if the server is not available and the entities and their attributes to export are given in the �filter-entities and �filter-entities-group arguments. |
| --- | --- |
| --connection-identifier optional | Type String Description Connector's connection identifier. The output file will have this identifier as name. |
| --output-path required | Type String Description Output path for the files generated by the export. |
| --- | --- |
| --ignore-cookies optional | Type No Value Description Specifies the synchronization mode, its presence meaning complete, its absence incremental. |
| --- | --- |
| --server required | Type String Description URL of the SCIM endpoints of your application, not including the v2. |
| --login optional | Type String Description Specifies the login of the account you may need. |
| --password optional | Type String Description Specifies the password of the account you may need. |
| --application-id optional | Type String Description Specifies the application connection login or the login of your application's id provider. |
| --application-key optional | Type String Description Specifies the application connection password or the password of your application's id provider. |
| --oauth-url optional | Type String Description The server's url when using OAuth2 authentication. |
| --oauth-token optional | Type String Description Specifies the OAuth token to connect to the application. |
| --scim-syntax optional | Type Enum Description Specifies the syntax used for requests body. Has to be one of those values: Salesforce (default value) or CyberArk |