wir betreiben auf einem CentOS7 OTRS 6.0.10. in der Community Edition.
Ich habe erfolgreich unser AD angebunden, woraus die Agents entstehen.
Meine Aufgabe ist nun, aus unserer Warenwirtschaft (wir nutzen Mention) die Kunden in OTRS zu bekommen. Das habe ich über ein View umgesetzt und die Kunden sind auch in OTRS sichtbar. Siehe Bild1 in den Attachments.
Meinen Versuch nun die Ansprechpartner (Kundenbenutzer), welche ich ebenfalls aus der WaWi über ein View einbinden möchte schlägt bisher fehl. Aber dies ist ein anderer Thread, den ich noch eröffnen muss.
Meine Idee war es nun erstmal, die Kundenbenutzer in der lokalen DB von OTRS anzulegen und dann mit den Kunden zu verbinden. Jedoch sehe ich nun einen lokal angelegten Kundenbenutzer, aber meine Kunden sind nicht sichtbar. Siehe Bild2
In den Logs bekomme ich folgenden Fehler:
Code: Select all
[Wed Dec 12 09:34:49 2018] -e: Use of uninitialized value in concatenation (.) or string at /opt/otrs/ /Kernel/System/CustomerCompany/DB.pm line 142.
[Wed Dec 12 09:34:49 2018] -e: DBD::ODBC::st execute failed: [Microsoft][ODBC Driver 17 for SQL Server ][SQL Server]Falsche Syntax in der Nähe des IN-Schlüsselworts. (SQL-42000)
[Wed Dec 12 09:34:49 2018] -e: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Anweisung(en) kon nte(n) nicht vorbereitet werden. (SQL-42000) at /opt/otrs//Kernel/System/DB.pm line 694.
ERROR: OTRS-CGI-99 Perl: 5.16.3 OS: linux Time: Wed Dec 12 09:34:49 2018
Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Falsche Syntax in der Nähe des IN-Schl üsselworts. (SQL-42000)
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden . (SQL-42000), SQL: 'SELECT TOP 1500 customer_id, customer_id, name FROM otrs_company WHERE IN ( 1 ) AND ((customer_id LIKE ? OR name LIKE ? ) )'
RemoteAddress: 172.16.10.155
RequestURI: /index.pl?Action=AdminCustomerUserCustomer;OTRSAgentInterface=m161LZDMMxXyhJ0BGnmImO3Gyxc k3nl3
Traceback (47859):
Module: Kernel::System::CustomerCompany::DB::CustomerCompanyList Line: 189
Module: Kernel::System::CustomerCompany::CustomerCompanyList Line: 455
Module: Kernel::Modules::AdminCustomerUserCustomer::Run Line: 310
Module: Kernel::System::Web::InterfaceAgent::Run Line: 1116
Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler Line: 40
Module: (eval) (v1.99) Line: 207
Module: ModPerl::RegistryCooker::run (v1.99) Line: 207
Module: ModPerl::RegistryCooker::default_handler (v1.99) Line: 173
Module: ModPerl::Registry::handler (v1.99) Line: 32
Hier meine Config.pm. Den LDAP Teil für die Agents habe ich hier für das Forum entfernt, da es nicht von Belang ist und ja auch funktioniert. Das Mapping habe ich bereits in der View angepasst.
Code: Select all
# --
# Copyright (C) 2001-2016 xxx, http://otrs.com/
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --
# Note:
#
# -->> Most OTRS configuration should be done via the OTRS web interface
# and the SysConfig. Only for some configuration, such as database
# credentials and customer data source changes, you should edit this
# file. For changes do customer data sources you can copy the definitions
# from Kernel/Config/Defaults.pm and paste them in this file.
# Config.pm will not be overwritten when updating OTRS.
# --
package Kernel::Config;
use strict;
use warnings;
use utf8;
sub Load {
my $Self = shift;
# ---------------------------------------------------- #
# database settings #
# ---------------------------------------------------- #
# The database host
$Self->{'DatabaseHost'} = '127.0.0.1';
# The database name
$Self->{'Database'} = "otrs";
# The database user
$Self->{'DatabaseUser'} = "otrs";
# The password of database user. You also can use bin/otrs.Console.pl Maint::Database::PasswordCrypt
# for crypted passwords
$Self->{'DatabasePw'} = 'SECRETPASSWORD'; # Change if you post in Forums or other support Communities
# The database DSN for MySQL ==> more: "perldoc DBD::mysql"
$Self->{'DatabaseDSN'} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost}";
# The database DSN for PostgreSQL ==> more: "perldoc DBD::Pg"
# if you want to use a local socket connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};";
# if you want to use a TCP/IP connection
# $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};host=$Self->{DatabaseHost};";
# The database DSN for Microsoft SQL Server - only supported if OTRS is
# installed on Windows as well
# $Self->{DatabaseDSN} = "DBI:ODBC:driver={SQL Server};Database=$Self->{Database};Server=$Self->{DatabaseHost},1433";
# The database DSN for Oracle ==> more: "perldoc DBD::oracle"
# $Self->{DatabaseDSN} = "DBI:Oracle://$Self->{DatabaseHost}:1521/$Self->{Database}";
#
# $ENV{ORACLE_HOME} = '/path/to/your/oracle';
# $ENV{NLS_DATE_FORMAT} = 'YYYY-MM-DD HH24:MI:SS';
# $ENV{NLS_LANG} = 'AMERICAN_AMERICA.AL32UTF8';
# ---------------------------------------------------- #
# fs root directory
# ---------------------------------------------------- #
$Self->{Home} = '/opt/otrs';
# ---------------------------------------------------- #
# insert your own config settings "here" #
# config settings taken from Kernel/Config/Defaults.pm #
# ---------------------------------------------------- #
# $Self->{SessionUseCookie} = 0;
# $Self->{CheckMXRecord} = 0;
##############################################################
# START LOGGING #
##############################################################
$Self->{LogModule} = 'Kernel::System::Log::SysLog';
# $Self->{'LogModule'} = 'Kernel::System::Log::File';
# param for LogModule Kernel::System::Log::SysLog
$Self->{'LogModule::SysLog::Facility'} = 'user';
# param for LogModule Kernel::System::Log::SysLog
# (if syslog can't work with utf-8, force the log
# charset with this option, on other chars will be
# replaces with ?)
$Self->{'LogModule::SysLog::Charset'} = 'utf-8';
# $Self->{'LogModule::SysLog::Charset'} = 'utf-8';
# param for LogModule Kernel::System::Log::File (required!)
$Self->{'LogModule::LogFile'} = '/tmp/otrs.log';
# param if the date (yyyy-mm) should be added as suffix to
# logfile [0|1]
# $Self->{'LogModule::LogFile::Date'} = 0;
# system log cache size for admin system log (default 32k)
# $Self->{'LogSystemCacheSize'} = 32 * 1024;
##############################################################
# END LOGGING #
##############################################################
#
##############################################################
# 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 => 'OTRS',
Password => 'SECRETPASSWORD', #change it if posting in forums or other support
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 #
##############################################################
#
$Self->{CustomerUser}->{CustomerCompanySupport} = 0;
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# data inserted by installer #
# ---------------------------------------------------- #
# $DIBI$
# ---------------------------------------------------- #
# ---------------------------------------------------- #
# #
# end of your own config options!!! #
# #
# ---------------------------------------------------- #
# ---------------------------------------------------- #
}
# ---------------------------------------------------- #
# needed system stuff (don't edit this) #
# ---------------------------------------------------- #
use base qw(Kernel::Config::Defaults);
# -----------------------------------------------------#
1;
Mario