Anbindung externe Kundendatenbank

Hilfe zu OTRS Problemen aller Art
Post Reply
HorstiBros
Znuny newbie
Posts: 38
Joined: 03 Dec 2015, 19:47
Znuny Version: 6.0.19
Real Name: Christoph

Anbindung externe Kundendatenbank

Post by HorstiBros »

Hallo liebe Gemeinde,

ich verzweifle gerade ein wenig bei der Anbindung einer externen Kundendatenbank an unsere neue OTRS Installation.

Die Kundenbenutzer habe ich erfolgreich angebunden, auch habe ich die Option gefunden den Kundenbenutzer mit einem Kunden zu verknüpfen.
Auch habe ich den Bereic "CustomerCompany" in der config.pm erfasst und angepasst.

Trotzdem kann ich unter Admin -> Kunden nicht die Kunden aus meiner externen Datenabank sehen. Lediglich einen, den ich mal zum Testen in der internen Datenbank angelegt habe. Bei diesem einen kann ich auch ausschließen, dass er aus der externen Datenbank kommt, da dieser Eintrag nicht in der externen vorhanden ist.

Die /var/log/messages sagt nichts über irgendwelche Fehler.

Auffällig ist jedoch, dass die Verbindung grundsätzlich da zu sein schein. Wenn ich nämlich versuche den vorhandenen Kunden zu ändern, bekommt ich eine Fehlermeldung, dass das Update nicht erfolgreich gewesen sei.

Ich habe mal zum Testen das Kennwort für die DB-Anmeldung in der conifg.pm auf ein falsches geändert. Das erzeugt keine Fehlermeldung, weder in OTRS noch in der messages.
Das heißt er scheint die Datenbank beim Laden der Kunden gar nicht anzufragen.


Was mag da nicht stimmen?
reneeb
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: Anbindung externe Kundendatenbank

Post by reneeb »

Dann zeig doch mal die Konfig...
Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
HorstiBros
Znuny newbie
Posts: 38
Joined: 03 Dec 2015, 19:47
Znuny Version: 6.0.19
Real Name: Christoph

Re: Anbindung externe Kundendatenbank

Post by HorstiBros »

Hi,

hier die Config. Ich habe da ein paar Sachen und Kennwörter "geschwärzt", aber das relevante ist nicht angepasst
Nicht wundern, das Kennwort ist natürlich nicht 1234 :)

Code: Select all

# --
# Copyright (C) 2001-2015 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'} = '10.124.16.201';

    # The database name
    $Self->{'Database'} = "otrs5";

    # The database user
    $Self->{'DatabaseUser'} = "otrs4";

    # The password of database user. You also can use bin/otrs.Console.pl Maint::Database::PasswordCrypt
    # for crypted passwords
    $Self->{'DatabasePw'} = '1234';

    # 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;

    # This is an example configuration for using an MS AD backend
    $Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP';
    $Self->{'AuthModule::LDAP::Host'} = 'dc01';
    $Self->{'AuthModule::LDAP::BaseDN'} = 'dc=123,dc=local';
    $Self->{'AuthModule::LDAP::UID'} = 'sAMAccountName';

    # Check if the user is allowed to auth in a posixGroup
    # (e. g. user needs to be in a group OTRS_Agents to use otrs)
#    $Self->{'AuthModule::LDAP::GroupDN'} = 'cn=,ou=OTRS_Groups,dc=companyname,dc=local';
#    $Self->{'AuthModule::LDAP::AccessAttr'} = 'member';
#    $Self->{'AuthModule::LDAP::UserAttr'} = 'DN';

    # Bind credentials to log into AD
    $Self->{'AuthModule::LDAP::SearchUserDN'} = 'CN=Dienstkonto OTRS,OU=Dienstkonten,OU=123,DC=123,DC=local';
    $Self->{'AuthModule::LDAP::SearchUserPw'} = '1234';

    # in case you want to add always one filter to each ldap query, use
    # this option. e. g. AlwaysFilter => '(mail=*)' or AlwaysFilter => '(objectclass=user)'
    $Self->{'AuthModule::LDAP::AlwaysFilter'} = '';

    # in case you want to add a suffix to each login name,  then
    # you can use this option. e. g. user just want to use user but
    # in your ldap directory exists user@domain.
    #$Self->{'AuthModule::LDAP::UserSuffix'} = '';

    # Net::LDAP new params (if needed - for more info see perldoc Net::LDAP)
    $Self->{'AuthModule::LDAP::Params'} = {
        port => 389,
        timeout => 120,
        async => 0,
        version => 3,
    };


# --------------------------------------------------- #
# LogModule                                           #
# --------------------------------------------------- #
# (log backend module)
$Self->{LogModule} = 'Kernel::System::Log::File';

# param for LogModule Kernel::System::Log::File (required!)
$Self->{'LogModule::LogFile'} = '/var/log/otrs.log';




$Self->{CustomerUser} = {
    Name => 'KMS Datasource',
    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:mysql:database=kms_test;host=10.124.16.201',
            User => 'otrs4',
            Password => '12345',
            Table => 'view_otrs',
            # if your frontend is unicode and the charset of your
            # customer database server is iso-8859-1, use these options.
#           SourceCharset => 'iso-8859-1',
#           DestCharset => 'utf-8',

            # 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 => 'contact_id',

# customer #
CustomerID => 'customer_id',
#CustomerValid => 'valid_id',
    CustomerCompanySupport => 1,
    CustomerUserListFields => ['firstname', 'lastname', 'mail'],
    CustomerUserSearchFields => ['firstname', 'lastname', 'mail'],
    CustomerUserSearchPrefix => '',
    CustomerUserSearchSuffix => '*',
    CustomerUserSearchListLimit => 250,
    CustomerUserPostMasterSearchFields => ['mail'],
    CustomerUserNameFields => ['Firstname','Lastname'],
    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 => 1,

    Map => [
        # note: Login, Email and CustomerID needed!
        # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
        [ 'UserFirstname',  'Firstname',  'firstname', 1, 1, 'var', '', 0 ],
        [ 'UserLastname',   'Lastname',   'lastname',  1, 1, 'var', '', 0 ],
        [ 'UserLogin',      'Username',   'mail',      1, 1, 'var', '', 0 ],
        [ 'UserEmail',      'Email',      'mail',      1, 1, 'var', '', 0 ],

#        [ 'UserEmail',      'Email', 'mail',           1, 1, 'var', '$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data{"TicketID"}&ArticleID=$Data{"ArticleID"}', 0 ],
        [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ],

#        [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],
        [ 'UserPhone',        'Phone',       'fon',        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 ],
    ],
    # default selections
    Selections => {
        UserTitle => {
            'Mr.' => 'Mr.',
            'Mrs.' => 'Mrs.',
        },
    },
};

     $Self->{CustomerCompany} = {
        Name   => 'Database Backend',
        Module => 'Kernel::System::CustomerCompany::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:mysql:database=kms_test;host=10.124.16.201',
            User => 'otrs4',
            Password => '1234',
            Table => 'customer',
#            ForeignDB => 0,    # set this to 1 if your table does not have create_time, create_by, change_time and change_by fields

            # 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,
        },

        # company unique id
        CustomerCompanyKey             => 'id',
        CustomerCompanyValid           => 'valid_id',
        CustomerCompanyListFields      => [ 'id', 'name' ],
        CustomerCompanySearchFields    => ['id', 'name'],
        CustomerCompanySearchPrefix    => '*',
        CustomerCompanySearchSuffix    => '*',
        CustomerCompanySearchListLimit => 250,
        CacheTTL                       => 60 * 60 * 24, # use 0 to turn off cache

        Map => [
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly
            [ 'CustomerID',             'CustomerID', '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 ],
        ],
    };



    # ---------------------------------------------------- #

    # ---------------------------------------------------- #
    # 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;
HorstiBros
Znuny newbie
Posts: 38
Joined: 03 Dec 2015, 19:47
Znuny Version: 6.0.19
Real Name: Christoph

Re: Anbindung externe Kundendatenbank

Post by HorstiBros »

Moin!

Da hat wohl niemand mehr eine Idee zu, oder?
reneeb
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: Anbindung externe Kundendatenbank

Post by reneeb »

Hast Du sichergestellt, dass die richtige Kernel/Config.pm geändert wurde? Hast Du den Apachen mal neu gestartet? Caches gelöscht?
Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
wurzel
Znuny guru
Posts: 3224
Joined: 08 Jul 2010, 22:25
Znuny Version: x.x.x
Real Name: Florian

Re: Anbindung externe Kundendatenbank

Post by wurzel »

Hi,

das apache Log kann Dir auch noch Aufschluss geben. Auf Anhieb finde ich keine Fehler.

Flo
OTRS 8 SILVER (Prod)
OTRS 8 auf Debian 11 (Test)
Znuny 7.x latest version testing auf Debian 11

-- Ich beantworte keine Forums-Fragen PN - No PN please

I won't answer to unfriendly users any more. A greeting and regards are just polite.
Mofarocker33
Znuny newbie
Posts: 31
Joined: 03 Nov 2011, 12:24
Znuny Version: 5.0.11

Re: Anbindung externe Kundendatenbank

Post by Mofarocker33 »

Hi,

ich habe einfach mal drüber geguckt.

Code: Select all

   # in case you want to add always one filter to each ldap query, use
    # this option. e. g. AlwaysFilter => '(mail=*)' or AlwaysFilter => '(objectclass=user)'
 #   $Self->{'AuthModule::LDAP::AlwaysFilter'} = '';
So steht sie bei mir drin, also auskommentiert. Wenn ich nichts Filter brauche ich doch die ganze Zeile gar nicht, oder?
CustomerUserEmailUniqCheck => 1,
So was schalte ich immer gerne aus, macht das System nur langsam.

Steht denn gar nichts im OTRS unter Admin -> Systemprotokoll ?

Gruß
Mofa
tntkonrad
Znuny newbie
Posts: 20
Joined: 16 Feb 2015, 10:07
Znuny Version: 5.0.10

Re: Anbindung externe Kundendatenbank

Post by tntkonrad »

Hi,

Trotzdem kann ich unter Admin -> Kunden nicht die Kunden aus meiner externen Datenabank sehen. Lediglich einen, den ich mal zum Testen in der internen Datenbank angelegt habe. Bei diesem einen kann ich auch ausschließen, dass er aus der externen Datenbank kommt, da dieser Eintrag nicht in der externen vorhanden ist.
Dann wird definitiv die alte Datenbank angesprochen. Ist denn die neue auch eine MySQL DB ?

dass das Update nicht erfolgreich gewesen sei.
Kann es auch nicht, denn deine CustomerDB steht auf ReadOnly => 1.


Gruss

Konrad
Post Reply