Difference between revisions of "SQL User Import"

From Hornbill
Jump to navigation Jump to search
 
(31 intermediate revisions by 7 users not shown)
Line 1: Line 1:
 +
This document can now be found at its new location in the [https://docs.hornbill.com/data-imports-guide/users/database/overview Hornbill Document Library].
 +
 +
[[file:hornbill-document-library.png|Data Imports Guide|link=https://docs.hornbill.com/data-imports-guide/users/database/overview]]
 +
 +
<!--
 +
 
== About the Hornbill SQL User Import Utility ==
 
== About the Hornbill SQL User Import Utility ==
 
The utility provides a simple, safe and secure way to create user accounts on the Hornbill platform by synchronizing with accounts held in your Database.  The tool is designed to run behind your corporate firewall, connect to your database, query the required account information, transform and load into the Hornbill instance.  The tool connects to the Hornbill instance in the cloud over HTTPS/SSL so as long as you have standard internet access then you should be able to use tool without the need to make any firewall configuration changes.  The tool supports both the initial bulk import as well as incremental adds and updates.  You can schedule the tool to run periodically to perform the import/update tasks as required.
 
The utility provides a simple, safe and secure way to create user accounts on the Hornbill platform by synchronizing with accounts held in your Database.  The tool is designed to run behind your corporate firewall, connect to your database, query the required account information, transform and load into the Hornbill instance.  The tool connects to the Hornbill instance in the cloud over HTTPS/SSL so as long as you have standard internet access then you should be able to use tool without the need to make any firewall configuration changes.  The tool supports both the initial bulk import as well as incremental adds and updates.  You can schedule the tool to run periodically to perform the import/update tasks as required.
−
 
−
== Open Source ==
 
−
 
−
The SQL User Import Utility is provided open source under the [https://wiki.hornbill.com/index.php/The_Hornbill_Community_License_(HCL) Hornbill Community Licence] and can be found [https://github.com/hornbill/goDb2HUserImport here] on GitHub
 
  
 
== Installation Overview ==
 
== Installation Overview ==
  
 
=== Windows Installation ===
 
=== Windows Installation ===
−
* Download the [https://github.com/hornbill/goDb2HUserImport/releases/download/v1.2.1/goDb2HUserImport_1.2.1.zip package] from GitHub  
+
* Download the [https://github.com/hornbill/user-import-database/releases/latest latest package] for your specific architecture from GitHub  
 
* Extract zip into a folder you would like the application to run from e.g. '''C:\Hornbill_Import\'''
 
* Extract zip into a folder you would like the application to run from e.g. '''C:\Hornbill_Import\'''
 
* Open '''conf.json''' and add in the necessary configration
 
* Open '''conf.json''' and add in the necessary configration
−
* Open Command Line Prompt as Administrator
+
* Open Command Line Prompt
−
* Change Directory to the folder with goDb2HUserImport_x86.exe '''C:\Hornbill_Import\'''
+
* Change Directory to the folder with user_import_db.exe '''C:\Hornbill_Import\'''
−
* Run the command goDb2HUserImport_x86.exe -dryrun=true
+
* Run the command user_import_db.exe -dryrun=true
−
 
 
−
Change "x86" to "amd64" depending on your architecture
 
  
 
== Configuration Overview ==
 
== Configuration Overview ==
Line 24: Line 24:
 
A default configuration file is provided conf.json, if a configuration file is not specified as a command line argument then conf.json must exist.
 
A default configuration file is provided conf.json, if a configuration file is not specified as a command line argument then conf.json must exist.
  
−
    {
+
  {
−
     "APIKey": "", /* this is the API-key which is associated to a user in the Hornbill instance [1] */
+
     "KeysafeKeyID": 0, /* this is the Keysafe key ID, which contains your database credentials */
−
     "InstanceId": "", /* your Hornbill instance name : not likely to change */
+
    "Database": {
−
     "UpdateUserType": false, /* options : true/false ; whether the usertype (basic vs user) is updated on import */
+
    "Source": "mssql", /* options: mssql/mysql/swsql/mysql320/csv/excel ; what DB to connect to */
−
     "UserRoleAction": "Create", /* options : Create/Update/Both ; on what action to assign roles to a user */
+
     "UserID": "winaccount", /* the FIELD name */
−
     "SQLConf": { /* db configuration : unlikely to change */
+
     "Encrypt": false, /* options : true/false ; [2] */
−
        "Driver": "mssql", /* options: mssql/mysql/swsql/mysql320/csv/excel ; what DB to connect to */
+
     "Query": "SELECT department, division, emailaddress, ext, forenames, jobtitle, mgrstaffid, mgrfirstname, mgrlastname, portrait_url, staffid, subdivision, surname, winaccount FROM whatever_table" /* SQL Query to run [3] */
−
        "Server": "10.123.123.123", /* DB Server address */
+
      },
−
        "Database": "HB", /* DB Name */
+
      "User": {
−
        "UserName": "Hornbill", /* DB Username */
+
    "UserDN": "&#123;&#123;.keysearch&#125;&#125;"
−
        "Password": "password", /* DB Password */
+
    , "HornbillUniqueColumn": "h_user_id"
−
        "Port": 1433, /* DB Server Port */
+
     ,"AccountMapping":{ /* mapping of fields in the format: "Field in Hornbill": "data to insert" [4] */
−
        "UserID": "winaccount", /* the FIELD name  */
+
      "UserID":"&#123;&#123;.keysearch&#125;&#125;",
−
        "Encrypt": false, /* options : true/false ; [2] */
+
      "LoginID":"&#123;&#123;.keysearch&#125;&#125;",
−
        "Query": "SELECT department, division, emailaddress, ext, forenames, jobtitle, mgrstaffid, mgrfirstname, mgrlastname, portrait_url, staffid, subdivision, surname, winaccount FROM whatever_table" /* SQL Query to run [3] */
+
      "EmployeeID":"&#123;&#123;.keysearch&#125;&#125;",
 +
      "UserType":"basic", /* (basic vs user) */
 +
      "Name":"&#123;&#123;.forenames&#125;&#125; &#123;&#123;.surname&#125;&#125;",
 +
      "Password":"", /* if left blank a random password will be generated [7] */
 +
      "FirstName":"&#123;&#123;.forenames&#125;&#125;",
 +
      "LastName":"&#123;&#123;.surname&#125;&#125;",
 +
      "JobTitle":"",
 +
      "Site":"1", /* if set, see also comments below on SiteLookup [8] */
 +
      "Phone":"&#123;&#123;.ext&#125;&#125;",
 +
      "Email":"&#123;&#123;.emailaddress&#125;&#125;",
 +
      "Mobile":"",
 +
      "AbsenceMessage":"",
 +
      "TimeZone":"", /* see [[User Import TimeZone Fields Options]] */
 +
      "Language":"", /* ISO 629 in combination with ISO 3166 as per [https://en.wikipedia.org/wiki/Language_localisation this wikipedia entry] */
 +
      "DateTimeFormat":"", /* see [[User Import DateTime Format Options]] */
 +
      "DateFormat":"",
 +
      "TimeFormat":"",
 +
      "CurrencySymbol":"", /* any character */
 +
      "CountryCode":"" /* expects ISO 3166 Alpha 2 two Character Country Code [https://en.wikipedia.org/wiki/List_of_ISO_3166_country_codes (see here)] */
 +
      "Enable2FA": "disabled",
 +
      "DisableDirectLogin": "false",
 +
      "DisableDirectLoginPasswordReset": "false",
 +
      "DisableDevicePairing": "false"
 
     },
 
     },
−
     "UserMapping":{ /* mapping of fields in the format: "Field in Hornbill": "data to insert" [4] */
+
     "Type":{
−
        "userId":"&#123;&#123;.winaccount&#125;&#125;",
+
      "Action":"Create" /* options : Create/Update/Both ; setting to Basic User (as opposed to Full User) */
−
        "UserType":"basic", /* usertype mentioned in comment on UpdateUserType */
 
−
        "Name":"&#123;&#123;.forenames&#125;&#125; &#123;&#123;.surname&#125;&#125;",
 
−
        "Password":"", /* if left blank a random 10-character password will be generated */
 
−
        "FirstName":"&#123;&#123;.forenames&#125;&#125;",
 
−
        "LastName":"&#123;&#123;.surname&#125;&#125;",
 
−
        "JobTitle":"&#123;&#123;.jobtitle&#125;&#125;",
 
−
        "Site":"", /* if set, see also comments below on SiteLookup */
 
−
        "Phone":"&#123;&#123;.ext&#125;&#125;",
 
−
        "Email":"&#123;&#123;.emailaddress&#125;&#125;",
 
−
        "Mobile":"",
 
−
        "AbsenceMessage":"",
 
−
        "TimeZone":"",
 
−
        "Language":"",
 
−
        "DateTimeFormat":"",
 
−
        "DateFormat":"",
 
−
        "TimeFormat":"",
 
−
        "CurrencySymbol":"",
 
−
        "CountryCode":""
 
 
     },
 
     },
−
     "UserAccountStatus":{
+
     "Status":{
−
        "Action":"Both", /* options : Create/Update/Both ; on what action to change the User Account Status */
+
      "Action":"Both", /* options : Create/Update/Both ; on what action to change the User Account Status */
−
        "Enabled": false, /* options : true/false ; turns on or off User Account Status  */
+
      "Value":"active" /* options : active/suspended/archived */
−
        "Status":"active" /* options : active/suspended/archived */
 
 
     },
 
     },
−
     "UserProfileMapping":{ /* further fields [5]  */
+
     "Role":{
−
        "MiddleName":"",
+
         "Action":"Both" /* options : Create/Update/Both ; on what action to change the User Role */
−
        "JobDescription":"",
+
         , "Roles":[ /* list of Roles assigned to the users being imported */
−
        "Manager":"&#123;&#123;.mgrfirstname&#125;&#125; &#123;&#123;.mgrlastname&#125;&#125;", /* full name of manager. If set, see also comments below on UserManagerMapping */
+
          "Basic User Role"
−
        "WorkPhone":"",
+
        ]
−
        "Qualifications":"",
 
−
        "Interests":"",
 
−
        "Expertise":"",
 
−
        "Gender":"",
 
−
        "Dob":"",
 
−
        "Nationality":"",
 
−
        "Religion":"",
 
−
        "HomeTelephone":"",
 
−
        "SocialNetworkA":"",
 
−
        "SocialNetworkB":"",
 
−
        "SocialNetworkC":"",
 
−
        "SocialNetworkD":"",
 
−
        "SocialNetworkE":"",
 
−
        "SocialNetworkF":"",
 
−
        "SocialNetworkG":"",
 
−
        "SocialNetworkH":"",
 
−
        "PersonalInterests":"",
 
−
        "homeAddress":"",
 
−
        "PersonalBlog":"",
 
−
        "Attrib1":"",
 
−
            "Attrib2":"",
 
−
            "Attrib3":"",
 
−
            "Attrib4":"",
 
−
            "Attrib5":"",
 
−
            "Attrib6":"",
 
−
            "Attrib7":"",
 
−
            "Attrib8":"&#123;&#123;.staffid&#125;&#125;"
 
−
    },
 
−
    "UserManagerMapping":{
 
−
         "Action":"Both" /* options : Create/Update/Both ; on what action to change the User's Manager */
 
−
         , "Enabled":true /* options : true/false ; turn this on or off */
 
 
     },
 
     },
−
     "Roles":[ /* list of Roles assigned to the users being imported */
+
     "ProfileMapping":{ /* further fields [5]  */
−
        "Basic User Role"
+
      "MiddleName":"",
−
    ],
+
      "JobDescription":"",
−
    "SiteLookup":{
+
      "Manager":"&#123;&#123;.manager&#125;&#125;", /* full name of manager. If set, see also comments below on UserManagerMapping */
−
        "Action":"Both" /* options : Create/Update/Both ; on what action to change the User's Site */
+
      "WorkPhone":"",
−
        , "Enabled": true /* options : true/false ; turn this on or off */
+
      "Qualifications":"",
 +
      "Interests":"",
 +
      "Expertise":"",
 +
      "Gender":"",
 +
      "Dob":"",
 +
      "Nationality":"",
 +
      "Religion":"",
 +
      "HomeTelephone":"&#123;&#123;.telext&#125;&#125;",
 +
      "SocialNetworkA":"",
 +
      "SocialNetworkB":"",
 +
      "SocialNetworkC":"",
 +
      "SocialNetworkD":"",
 +
      "SocialNetworkE":"",
 +
      "SocialNetworkF":"",
 +
      "SocialNetworkG":"",
 +
      "SocialNetworkH":"",
 +
      "PersonalInterests":"",
 +
      "homeAddress":"",
 +
      "PersonalBlog":"",
 +
      "Attrib1":"1",
 +
      "Attrib2":"2",
 +
      "Attrib3":"3",
 +
      "Attrib4":"4",
 +
      "Attrib5":"5",
 +
      "Attrib6":"6",
 +
      "Attrib7":"7",
 +
      "Attrib8":"8"
 
     }
 
     }
−
     , "ImageLink":{
+
     , "Manager":{
−
        "Action":"Both" /* options : Create/Update/Both ; on what action to change the Image */
+
      "Action": "Both" /* options : Create/Update/Both ; on what action to change the User's Manager */
−
        , "Enabled": true /* options : true/false ; turn this on or off */
+
      , "Value": "&#123;&#123;.mgrfirstname&#125;&#125; &#123;&#123;.mgrlastname&#125;&#125;" /* full name of manager. If set, see also comments below on User Manager Mapping */
−
         , "UploadType": "URL"
+
      , "Options": {
−
         , "ImageType": "jpg" /* options : jpg/png */
+
         "GetStringFromValue": {
−
        , "URI": "&#123;&#123;.portrait_url&#125;&#125;"
+
          "Regex" : ""
 +
          , "Reverse": false
 +
        }
 +
        , "MatchAgainstDistinguishedName": false
 +
         , "Search": {
 +
          "Enable": true /* options : true/false ; turn this on or off */
 +
          , "SearchField": ""
 +
        }
 +
      }
 
     }
 
     }
−
     , "OrgLookup":{
+
     , "Image":{
−
        "Action":"Both" /* options : Create/Update/Both ; on what action to add to the organisational structure */
+
      "Action":"Both" /* options : Create/Update/Both ; on what action to change the Image */
−
        , "Enabled":true /* options : true/false ; turn this on or off */
+
      , "UploadType": "URL" /* options : URI/URL/LOCAL ; local (network) drive or HTTP(S) served image */
−
        , "OrgUnits":[ /* Organisational Units to associate the imported user with [6] */
+
      , "InsecureSkipVerify": false
−
            {
+
      , "ImageType": "jpg" /* options : jpg/png */
−
                "Attribute":"&#123;&#123;.department&#125;&#125;", /* name of organisational unit */
+
      , "URI": "&#123;&#123;.portrait_url&#125;&#125;"
−
                "Type":2, /* options : 0,...,5 ; type of organisational unit, respectively: general/team/department/costcenter/division/company */
 
−
                "Membership":"member", /* options : member/teamLeader/manager */
 
−
                "TasksView":false, /* options : true/false ; If set true, then the user can view tasks assigned to this group */
 
−
                "TasksAction":false /* options : true/false ; If set true, then the user can action tasks assigned to this group */
 
−
            }, {
 
−
                "Attribute":"Great Company",
 
−
                "Type":5,
 
−
                "Membership":"member",
 
−
                "TasksView":false,
 
−
                "TasksAction":false
 
−
            }, {
 
−
                "Attribute":"&#123;&#123;.division&#125;&#125;",
 
−
                "Type":4,
 
−
                "Membership":"member",
 
−
                "TasksView":false,
 
−
                "TasksAction":false
 
−
            }
 
−
        ]
 
 
     }
 
     }
 +
    , "Site":{
 +
      "Action":"Both" /* options : Create/Update/Both ; on what action to change the User's Site */
 +
      , "Value": "&#123;&#123;.site&#125;&#125;"
 
     }
 
     }
 +
    , "Org":[ /* Organisational Units to associate the imported user with [6] */
 +
      {
 +
        "Action":"Both" /* options : Create/Update/Both ; on what action to add to the organisational structure */
 +
        , "value":"&#123;&#123;.department&#125;&#125;" /* name of organisational unit */
 +
        , "MemberOf":""
 +
        , "Options": {
 +
          "Type":2, /* options : 0,...,5 ; type of organisational unit, respectively: general/team/department/costcenter/division/company */
 +
          "Membership":"member", /* options : member/teamLeader/manager */
 +
          "TasksView":false, /* options : true/false ; If set true, then the user can view tasks assigned to this group */
 +
          "TasksAction":false, /* options : true/false ; If set true, then the user can action tasks assigned to this group */
 +
          "OnlyOneGroupAssignment":false
 +
        }
 +
      }
 +
      , {
 +
        "Action":"Both"
 +
        , "value":"Great Company"
 +
        , "MemberOf":""
 +
        , "Options": {
 +
          "Type":5,
 +
          "Membership":"member",
 +
          "TasksView":false,
 +
          "TasksAction":false,
 +
          "OnlyOneGroupAssignment":false /* options: true/false ; if set to true, then a user can only be associated to a single group at any one time */
 +
        }
 +
      }
 +
      , {
 +
        "Action":"Both"
 +
        , "value":"&#123;&#123;.division&#125;&#125;"
 +
        , "MemberOf":""
 +
        , "Options": {
 +
          "Type":4,
 +
          "Membership":"member",
 +
          "TasksView":false,
 +
          "TasksAction":false,
 +
          "OnlyOneGroupAssignment":false
 +
        }
 +
      }
 +
    ]
 +
  }
 +
  }
  
  
−
# An API key is set up against a user within Hornbill (accessed through: Administration > System > Organisationl Data > Users).
 
 
# specify whether the connection between the script and the database should be encrypted. NOTE: There is a bug in SQL Server 2008 and below that causes the connection to fail if the connection is encrypted. Only set this to true if your SQL Server has been patched accordingly
 
# specify whether the connection between the script and the database should be encrypted. NOTE: There is a bug in SQL Server 2008 and below that causes the connection to fail if the connection is encrypted. Only set this to true if your SQL Server has been patched accordingly
−
# please note that all field names are specificied as lowercase - this is to ensure smooth running for the mapped fields. '''However for CSV''' please use UPPERCASE for all field names and, also, do NOT use spaces in the headers (fieldnames).
+
# please note that all field names are specificied as lowercase - this is to ensure smooth running for the mapped fields (as they are case-sensitive). Also, by specifying each field in use, one is following SQL best practice as it prevents unnecessary/unused data to be stored and processed (longvarchar and blob fields, for instance, take up a lot of memory). '''For CSV''' please use UPPERCASE for all field names and, also, do NOT use spaces in the headers (fieldnames).
 
# The fields are quite self-explanatory and part of the "Details"-section (as opposed to the "About"-section [5]), most can be left as-is. The mapping is done with some templating of the format &#123;&#123;fieldname&#125;&#125;. One can be a little adventurous, for instance "&#123;&#123;.mgrfirstname&#125;&#125; - &#123;&#123;.mgrlastname&#125;&#125;" puts a space, hyphen and space between the person's manager's first and last name. One can use this to prefix or suffix the values coming from the database or indeed to set a static value (as done for example with userType and Attribute under OrgLookup for the company (Type:5)).
 
# The fields are quite self-explanatory and part of the "Details"-section (as opposed to the "About"-section [5]), most can be left as-is. The mapping is done with some templating of the format &#123;&#123;fieldname&#125;&#125;. One can be a little adventurous, for instance "&#123;&#123;.mgrfirstname&#125;&#125; - &#123;&#123;.mgrlastname&#125;&#125;" puts a space, hyphen and space between the person's manager's first and last name. One can use this to prefix or suffix the values coming from the database or indeed to set a static value (as done for example with userType and Attribute under OrgLookup for the company (Type:5)).
 
# The same holds here as for [4], this is regarding the "About"-section of the user's details.
 
# The same holds here as for [4], this is regarding the "About"-section of the user's details.
 
# this is a non-ordered list of the organisation structure - it allows one to associate the imported user to one or more levels within the organisation. The delivered configuration file will associate each imported user with EACH of the three discernable levels (company, department and division) - depending on your requirements regarding the availability of services and such, you will likely want to manipulate this section and perhaps only leave behind the "department" level (Type: 2) with the TasksView and TasksAction set accordingly. Please note that this import only adds and not REMOVES any association.
 
# this is a non-ordered list of the organisation structure - it allows one to associate the imported user to one or more levels within the organisation. The delivered configuration file will associate each imported user with EACH of the three discernable levels (company, department and division) - depending on your requirements regarding the availability of services and such, you will likely want to manipulate this section and perhaps only leave behind the "department" level (Type: 2) with the TasksView and TasksAction set accordingly. Please note that this import only adds and not REMOVES any association.
 +
# The password field should be left empty as the utility generates a secure password that adheres to the User Password Policy as specified on your Hornbill instance. This password will only be temporary as the user should use the "Forgot Password" link available on the Hornbill Login Screen to reset their password the first time they navigate to your Hornbill instance.
 +
# "Site" - Recognises a corresponding Hornbill site ID. E.g. "Site":"1" - The value of Site should be numeric. As an alternative, the import configuration provides a "Site Lookup" section (outlined in a later section) which can make a site association based on the contents of a directory attribute.
 +
# "HornbillUniqueColumn" can be set to treat another fields as the unique identifier. By default this is 'h_user_id'. If one wishes to use h_login_id or h_email, one can. The h_user_id field will still need to remain unique within Hornbill. So, an update to the user record will will work as expected (it will NOT update the h_user_id field!!!). A Creation WILL require a unique h_user_id to be set/mapped.
 +
<br>
 +
<br>
 +
====Associating a Site to Hornbill User Accounts====
 +
The DB Import utility has the ability to associate a Hornbill Site record to a user account based on the contents of a field. This is achieved through a "Look-up". The Look up mechanism is quite simple and works in the following manner.
 +
# The import reads the fields (template rules work) that is specified in the "value" field. In the example shown, the '''site''' field is used.
 +
# It takes the content and tries to identify if there is an existing site record in Hornbill with a name that matches the value of the site. e.g. if the site field contained "Brussels", the import would look for a Hornbill Site record with the name "Brussels".
 +
# If a match is found, the import will associate the user to the site.
 +
# If no site record is found, the import will move onto the next user.
 +
i.e. The name of the Site record in Hornbill must match the value of the directory attribute specified. More on Hornbill Sites can be found here: [[Sites|'''Sites''']]
 +
<br>
 +
<br>
 +
====Associating a Group to Hornbill User Accounts====
 +
The DB Import has the ability to associate a Hornbill Group to a user account based on the contents of a fieldname. This is achieved through a "Look-up". The Look up mechanism is quite simple and works in the following manner.
 +
# The utility reads the attribute that is specified in the orgLookup section. In the example shown, the '''department''' field is used.
 +
# It takes the content and tries to identify if there is a Hornbill Group that exists with a name that matches the value of the field name. e.g. if the '''department''' field contained "Accounting", the utility would look for a Hornbill Group called "Accounting".
 +
# If a match is found, the import will associate the user to the group.
 +
# If no Hornbill organisation is found, the import will move onto the next user.
 +
i.e. The name of the Organization(Group) in Hornbill must match the value of the database field. More on Hornbill Organisational Groups can be found here: [[Organisation|'''Organisation Structure
 +
<br>
 +
<br>
 +
====User Manager Mapping in Hornbill====
 +
Hornbill can store a manager relationship between two users in Hornbill. The manager look up mechanism works as follows:
 +
# The import reads the contents of the value attribute which will contain the some text identifying the manager eg "''mgrfirstname mgrlastname''"
 +
# IF a regex is given, then this first will be applied to the data obtained above.
 +
# The import is hard-coded to remove any slash and comma in the result.
 +
# With the "Reverse" option enabled, the above string would be reversed to give: "''mgrlastname mgrfirstname''"
 +
# The import tries to match this value against an existing Hornbill user by looking up the "Handle" field i.e. h_name.
  
 
== Command Line Parameters ==
 
== Command Line Parameters ==
Line 156: Line 216:
 
* file - Defaults to '''''conf.json''''' - Name of the Configuration file to load
 
* file - Defaults to '''''conf.json''''' - Name of the Configuration file to load
 
* dryrun - Defaults to '''''false''''' - Set to True and the XMLMC for Create and Update users will not be called and instead the XML will be dumped to the log file, this is to aid in debugging the initial connection information.
 
* dryrun - Defaults to '''''false''''' - Set to True and the XMLMC for Create and Update users will not be called and instead the XML will be dumped to the log file, this is to aid in debugging the initial connection information.
−
* zone - Defaults to '''''eur''''' - Allows you to change the ZONE used for creating the XMLMC EndPoint URL https://{ZONE}api.hornbill.com/{INSTANCE}/
 
 
* workers - Defaults to `3` - Allows you to change the number of worker threads used to process the import, this can improve performance on slow import but using too many workers have a detriment to performance of your Hornbill instance.
 
* workers - Defaults to `3` - Allows you to change the number of worker threads used to process the import, this can improve performance on slow import but using too many workers have a detriment to performance of your Hornbill instance.
 +
* forcerun - This should '''only''' be used in the event of the error''' ''Unable to run import, a previous import is still running'' ''' to force the import to ignore the "running" status.
 +
* creds - Defaults to `false` - Set to `true` to decrypt and output the API key that is stored locally. NOTE - the tool will prompt you for your instance ID, and this can only be decrypted by the user who originally performed the encryption, and only on the same machine that it was encrypted on.
 +
 +
=== First Run ===
 +
 +
From version '''4.0.0''' of the User Import - Database utility, when you first run the utility it will prompt you for the ID of your Hornbill instance (case-sensitive), and the API key (also case-sensitive) that will be used to authenticate the API calls back into Hornbill. This information will be encrypted, and stored locally on the client PC that will be running the tool. For each subsequent import run, the utility will decrypt your instance ID and API key, and will use those to make the API calls back into Hornbill.
 +
 +
NOTE - the encrypted instance ID and API key can only be decrypted on the computer, and by the user, that performed the encryption, so please keep this in mind when scheduling your imports.
 +
 +
Should you wish to use a different API key to what has been previously encrypted, just delete the '''import.cfg''' file from the folder where the import binary resides, and re-run your import from the command line inputting the Instance ID and new API Key as you would have on its first run.
  
 
== Testing Overview ==
 
== Testing Overview ==
Line 163: Line 232:
  
 
<code>
 
<code>
−
goDb2HUserImport.exe -dryrun=true
+
user_import_db.exe -dryrun=true
 
</code>
 
</code>
 +
 +
== API Key Rules ==
 +
This utility uses ([[API keys]]):
 +
 +
* activity:profileImageSet
 +
* admin:sysOptionGet
 +
* admin:userAddGroup
 +
* admin:userAddRole
 +
* admin:userCreate
 +
* admin:userDeleteGroup
 +
* admin:userProfileSet
 +
* admin:userSetAccountStatus
 +
* admin:userUpdate
 +
* data:entityAddRecord
 +
* data:entityUpdateRecord
 +
* data:queryExec
 +
* session:getSystemLicenseInfo
 +
 +
=Troubleshooting=
  
 
== Logging Overview ==
 
== Logging Overview ==
Line 170: Line 258:
 
All Logging output is saved in the "log" directory which can be found in the same location as the executable. The file name contains the date and time the import was run '''''SQL_User_Import_2015-11-06T14-26-13Z.log'''''
 
All Logging output is saved in the "log" directory which can be found in the same location as the executable. The file name contains the date and time the import was run '''''SQL_User_Import_2015-11-06T14-26-13Z.log'''''
  
−
==Trouble Shooting==
+
==Common Error Messages==
−
 
 
−
===Common Error Messages===
 
  
 
Below are some common errors that you may encounter in the log file and what they mean:
 
Below are some common errors that you may encounter in the log file and what they mean:
 
* ''' ''[ERROR] Error Decoding Configuration File:.....'' ''' - this will be typically due to a missing quote (") or comma (,) somewhere in the configuration file. This is where an online JSON viewer/validator can come in handy rather than trawling the conf file looking for that proverbial needle in a haystack.
 
* ''' ''[ERROR] Error Decoding Configuration File:.....'' ''' - this will be typically due to a missing quote (") or comma (,) somewhere in the configuration file. This is where an online JSON viewer/validator can come in handy rather than trawling the conf file looking for that proverbial needle in a haystack.
−
* ''' ''[ERROR] Get https://api.github.com/repos/hornbill/goSQL2HUserImport/tags: dial tcp xx.xx.xx.xx:xxx: ........'' ''' - this most likely indicates that you have a HTTP proxy server on your network between the host running the executable and your Hornbill API endpoint. Ensure the http_proxy environment variable is set (See the section on "HTTP Proxies" for more information) and that the proxy is configured to allow this communication.
+
* ''' ''[ERROR] Get https://api.github.com/repos/hornbill/user-import-database/tags: dial tcp xx.xx.xx.xx:xxx: ........'' ''' - this most likely indicates that you have a HTTP proxy server on your network between the host running the executable and your Hornbill API endpoint. Ensure the http_proxy environment variable is set (See the section on "HTTP Proxies" for more information) and that the proxy is configured to allow this communication.
 
* ''' ''[ERROR] Unable to Create User: Invalid value for parameter '[parameter name]': The text size provided (31 characters) is greater than the maximum allowable size of 20 characters for column [column name]'' ''' - the contents of your directory attribute exceed the maximum number of characters that can be placed in the Hornbill database column.
 
* ''' ''[ERROR] Unable to Create User: Invalid value for parameter '[parameter name]': The text size provided (31 characters) is greater than the maximum allowable size of 20 characters for column [column name]'' ''' - the contents of your directory attribute exceed the maximum number of characters that can be placed in the Hornbill database column.
 
* ''' ''[ERROR] Unable to Create User: The value in element <userId> did not meet the required input pattern constraints. at location '/methodCall/params/userId' '' ''' - the user id contains characters that are not allowed. The User Id should be made up of alphanumeric characters. Full stops (.) and underscores (_) are also supported.
 
* ''' ''[ERROR] Unable to Create User: The value in element <userId> did not meet the required input pattern constraints. at location '/methodCall/params/userId' '' ''' - the user id contains characters that are not allowed. The User Id should be made up of alphanumeric characters. Full stops (.) and underscores (_) are also supported.
 
* ''' ''[ERROR] Unable to Update User: Invalid value for parameter '[parameter name]': Error setting value for column '[column name]'. bad lexical cast: source type value could not be interpreted as target'' ''' - this error is indicating that the contents of your directory attribute are in a format that is not compatible with the type of the Hornbill database column. For example, you will get this when trying to place text into a database field that is of type "INT" (accepts integer values only).
 
* ''' ''[ERROR] Unable to Update User: Invalid value for parameter '[parameter name]': Error setting value for column '[column name]'. bad lexical cast: source type value could not be interpreted as target'' ''' - this error is indicating that the contents of your directory attribute are in a format that is not compatible with the type of the Hornbill database column. For example, you will get this when trying to place text into a database field that is of type "INT" (accepts integer values only).
 
* ''' ''[ERROR] Unable to Load LDAP Attribute: '[LDAP attribute name]' For Input Param: '[Hornbill Parameter name]' '' ''' - When the import utility is unable to load a particular LDAP attribute, this means that the attribute field in your directory does not contain a value. This error will not prevent the user account being created or updated in Hornbill and can be considered more as a warning rather than an outright failure or problem.
 
* ''' ''[ERROR] Unable to Load LDAP Attribute: '[LDAP attribute name]' For Input Param: '[Hornbill Parameter name]' '' ''' - When the import utility is unable to load a particular LDAP attribute, this means that the attribute field in your directory does not contain a value. This error will not prevent the user account being created or updated in Hornbill and can be considered more as a warning rather than an outright failure or problem.
 +
* ''' ''[ERROR] Unable to run import, a previous import is still running'' ''' - This can occur when a previous import did not exit cleanly. Use the command-line parameter '''-forcerun''' on the next import to force the import to ignore the running status.
  
 
=== Error Codes ===
 
=== Error Codes ===
Line 187: Line 274:
 
* '''102''' - Unable to Load Configuration File
 
* '''102''' - Unable to Load Configuration File
  
−
== HTTP Proxies ==
+
= HTTP Proxies =
  
−
If you use a proxy for all of your internet traffic, the HTTP_PROXY Environment variable needs to be set. The https_proxy environment variable holds the hostname or IP address of your proxy server. It is a standard environment variable and like any such variable, the specific steps you use to set it depends on your operating system.
+
{{UtilityProxies}}
  
−
For windows machines, it can be set from the command line using the following:
+
= Scheduling Overview =
−
<br>
 
−
<code>
 
−
set HTTP_PROXY=HOST:PORT
 
−
</code>
 
−
<br>
 
−
Where "HOST" is the IP address or host name of your Proxy Server and "PORT" is the specific port number.
 
  
−
== Scheduling Overview ==
+
== Windows ==
−
 
 
−
=== Windows ===
 
 
You can schedule .exe to run with any optional command line argument from Windows Task Scheduler.
 
You can schedule .exe to run with any optional command line argument from Windows Task Scheduler.
 
* Ensure the user account running the task has rights to ldap_import.exe and the containing folder.
 
* Ensure the user account running the task has rights to ldap_import.exe and the containing folder.
Line 209: Line 288:
  
  
−
== Required Permissions ==
+
= Required Permissions =
  
−
=== Default Role ===
+
== Default Role ==
 
As of Server Release 2494 a default role '''User Import''' is delivered which has all the necessary rights to import / update users. The exception to this is any roles you try and assign to users being imported must be assigned to the user account logging in and running the import, this is due to a security measure that prevents you inflating your own rights or giving a user more rights than you have yourself.  
 
As of Server Release 2494 a default role '''User Import''' is delivered which has all the necessary rights to import / update users. The exception to this is any roles you try and assign to users being imported must be assigned to the user account logging in and running the import, this is due to a security measure that prevents you inflating your own rights or giving a user more rights than you have yourself.  
  
Line 218: Line 297:
 
The following rights are required by any user account being used to run the User Import tool.
 
The following rights are required by any user account being used to run the User Import tool.
  
−
=== System Rights ===
+
== System Rights ==
 
'''Group A (Accounts)'''
 
'''Group A (Accounts)'''
 
* Manage Users
 
* Manage Users
Line 224: Line 303:
 
* Update Users
 
* Update Users
 
[[File:Screen_Shot_2016-02-26_at_07.59.50.png]]
 
[[File:Screen_Shot_2016-02-26_at_07.59.50.png]]
−
=== Table Rights ===
+
== Table Rights ==
 
'''h_sys_accounts'''
 
'''h_sys_accounts'''
 
* Browse
 
* Browse
Line 243: Line 322:
 
* Add
 
* Add
 
[[File:Screen_Shot_2016-03-02_at_10.00.30.png]]
 
[[File:Screen_Shot_2016-03-02_at_10.00.30.png]]
 +
 +
-->
 +
[[Category:HDOC]]

Latest revision as of 20:38, 11 April 2024

This document can now be found at its new location in the Hornbill Document Library.

Data Imports Guide

Retrieved from "https://wiki.hornbill.com/index.php?title=SQL_User_Import&oldid=32320"