ich beschäftige mich erst seit kurzem mit OTRS und natürlich tauchen da Fragen und auch Probleme auf. Meine Recherchen im Netz waren bisher noch nicht erfolgreich
Zum Einsatz kommt eine OTRS 6.0.10 Community Edition auf einem CentOS7 Server.
Ich konnte bisher erfolgreich Agents über das AD einbinden und auch Kunden aus einer externen MSSQL DB einbinden. Nun stehe ich aber vor dem Problem, dass ich auch Kundenbenutzer aus einer MSSQL DB einbinden muss, um diese dann den Kunden zuordnen zu können.
Die Verbindung zur DB steht, jedoch bekomme ich nun folgende Fehlermeldung
Code: Select all
[Error][Kernel::System::CustomerUser::DB::CustomerUserDataGet][Line:1217]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ung▒ltiger Spaltenname 'create_time'. (SQL-42S22)#012[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ung▒ltiger Spaltenname 'create_by'. (SQL-42S22)#012[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ung▒ltiger Spaltenname 'change_time'. (SQL-42S22)#012[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ung▒ltiger Spaltenname 'change_by'. (SQL-42S22) [state was 42S22 now 42000]#012[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. (SQL-42000), SQL: 'SELECT TOP 1 first_name, last_name, last_name, email, customer_id, phone, mobile, create_time, create_by, change_time, change_by, login FROM otrs_company_user WHERE LOWER(login) = LOWER(?)'
Hat jemand einen Tipp, bzw. Lösung für mich, wie ich dies lösen kann?
Hier noch meine gekürzte Config.pm
Code: Select all
##############################################################
# Start Customer Company Backend #
##############################################################
# Connection to Mention Database
$Self->{CustomerCompany} = {
Name => 'Mention Company',
Module => 'Kernel::System::CustomerCompany::DB',
Params => {
# if you want to use an external database, add the
# required settings
Type => 'mssql', # only for ODBC connections
DSN => 'DBI:ODBC:mention',
User => 'xxx',
Password => 'xxx',
Table => 'otrs_company',
ForeignDB => 1, # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields
# CaseSensitive defines if the data storage of your DBMS is case sensitive and will be
# preconfigured within the database driver by default.
# If the collation of your data storage differs from the default settings,
# you can set the current behavior ( either 1 = CaseSensitive or 0 = CaseINSensitive )
# to fit your environment.
#
# CaseSensitive => 0,
# SearchCaseSensitive will control if the searches within the data storage are performed
# case sensitively (if possible) or not. Change this option to 1, if you want to search case sensitive.
# This can improve the performance dramatically on large databases.
SearchCaseSensitive => 0,
},
# company unique id
CustomerCompanyKey => 'customer_id',
#CustomerCompanyValid => 'valid_id',
CustomerCompanyListFields => [ 'customer_id', 'name' ],
CustomerCompanySearchFields => [ 'customer_id', 'name' ],
CustomerCompanySearchPrefix => '*',
CustomerCompanySearchSuffix => '*',
CustomerCompanySearchListLimit => 1500,
CacheTTL => 60 * 60 * 24, # use 0 to turn off cache
Map => [
# Info about dynamic fields:
#
# Dynamic Fields of type CustomerCompany can be used within the mapping (see example below).
# The given storage (third column) then can also be used within the following configurations (see above):
# CustomerCompanySearchFields, CustomerCompanyListFields
#
# Note that the columns 'frontend' and 'readonly' will be ignored for dynamic fields.
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
[ 'CustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],
[ 'CustomerCompanyName', 'Customer', 'name', 1, 1, 'var', '', 0 ],
[ 'CustomerCompanyStreet', 'Street', 'street', 1, 0, 'var', '', 0 ],
[ 'CustomerCompanyZIP', 'Zip', 'zip', 1, 0, 'var', '', 0 ],
[ 'CustomerCompanyCity', 'City', 'city', 1, 0, 'var', '', 0 ],
[ 'CustomerCompanyCountry', 'Country', 'country', 1, 0, 'var', '', 0 ],
[ 'CustomerCompanyURL', 'URL', 'url', 1, 0, 'var', '[% Data.CustomerCompanyURL | html %]', 0 ],
#[ 'CustomerCompanyComment', 'Comment', 'comments', 1, 0, 'var', '', 0 ],
#[ 'ValidID', 'Valid', 'valid_id', 0, 1, 'int', '', 0 ],
# Dynamic field example
# [ 'DynamicField_Name_Y', undef, 'Name_Y', 0, 0, 'dynamic_field', undef, 0 ],
],
};
##############################################################
# END Customer Company Backend #
##############################################################
#
##############################################################
# Start Customer User Backend #
##############################################################
# CustomerUser (customer database backend and settings)
$Self->{CustomerUser1} = {
Name => 'Mention Company Customer',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
# DSN => 'DBI:odbc:yourdsn',
Type => 'mssql', # only for ODBC connections
DSN => 'DBI:ODBC:mention',
User => 'xxx',
Password => 'xxx',
Table => 'otrs_company_user',
#ForeignDB => 1
# CaseSensitive will control if the SQL statements need LOWER()
# function calls to work case insensitively. Setting this to
# 1 will improve performance dramatically on large databases.
CaseSensitive => 0,
},
# customer unique id
CustomerKey => 'login',
# customer #
CustomerID => 'customer_id',
#CustomerValid => 'valid_id',
CustomerUserListFields => ['first_name', 'last_name', 'email'],
CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 1500,
CustomerUserPostMasterSearchFields => ['email'],
CustomerUserNameFields => ['title','first_name','last_name'],
CustomerUserEmailUniqCheck => 1,
# # show not own tickets in customer panel, CompanyTickets
# CustomerUserExcludePrimaryCustomerID => 0,
# # generate auto logins
# AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',
# # admin can change customer preferences
# AdminSetPreferences => 1,
# # cache time to live in sec. - cache any database queries
# CacheTTL => 0,
# # just a read only source
ReadOnly => 0,
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target, link class(es)
# [ 'UserTitle', 'Title', 'title', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', 'Firstname', 'first_name', 1, 1, 'var', '', 0 ],
[ 'UserLastname', 'Lastname', 'last_name', 1, 1, 'var', '', 0 ],
[ 'UserLogin', 'Username', 'login', 1, 1, 'var', '', 0 ],
# [ 'UserPassword', 'Password', 'pw', 0, 0, 'var', '', 0 ],
[ 'UserEmail', 'Email', 'email', 1, 1, 'var', '', 0 ],
# [ 'UserEmail', Translatable('Email'), 'email', 1, 1, 'var', '[% Env("CGIHandle") %]?Action=AgentTicketCompose;ResponseID=1;TicketID=[% Data.TicketID | uri %];ArticleID=[% Data.ArticleID | uri %]', 0, '', 'AsPopup OTRSPopup_TicketAction' ],
[ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],
# [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
[ 'UserPhone', 'Phone', 'phone', 1, 0, 'var', '', 0 ],
# [ 'UserFax', 'Fax', 'fax', 1, 0, 'var', '', 0 ],
[ 'UserMobile', 'Mobile', 'mobile', 1, 0, 'var', '', 0 ],
# [ 'UserStreet', 'Street', 'street', 1, 0, 'var', '', 0 ],
# [ 'UserZip', 'Zip', 'zip', 1, 0, 'var', '', 0 ],
# [ 'UserCity', 'City', 'city', 1, 0, 'var', '', 0 ],
# [ 'UserCountry', 'Country', 'country', 1, 0, 'var', '', 0 ],
# [ 'UserComment', 'Comment', 'comments', 1, 0, 'var', '', 0 ],
# [ 'ValidID', 'Valid', 'valid_id', 0, 1, 'int', '', 0 ],
# Dynamic field example
# [ 'DynamicField_Name_X', undef, 'Name_X', 0, 0, 'dynamic_field', undef, 0, undef, undef, ],
],
# default selections
Selections => {
UserTitle => {
'Mr.' => 'Mr.',
'Mrs.' => 'Mrs.',
},
},
};
##############################################################
# END Customer User Backend #
##############################################################