Customer User aus csv in OTRS Customer DB (Insert + Update)

Hilfe zu Znuny Problemen aller Art
Forum rules
Locked
michael_maurer
Znuny advanced
Posts: 146
Joined: 07 Aug 2008, 09:20
Znuny Version: 2.4.9
Contact:
Contact michael_maurer

Customer User aus csv in OTRS Customer DB (Insert + Update)

Post by michael_maurer »

Hallo zusammen,

zuerst mal kurz die Vorgeschichte:
Unsere "echten" Customer_User authentifiziere ich gegen MS Active Directory (Forest mit einigen Subdomains).
Dazu kommen "unechte" Customer User, die sich niemals am OTRS anmelden werden, aber als betroffene User ausgewählt werden sollen.

Der Server:
W2K3 Server in einer VMware
OTRS::ITSM 1.2.1

Ich habe auf http://www.cape-it.de/ ein Script zum Import von Customer Usern aus csv Dateien entdeckt, das aber mit seltsamen MainModul Fehlern abgeschmiert ist. Daraufhin stieß ich auf ein original OTRS Script namens syncuser_csv2otrs.pl zum Anlegen von Agents aus einer csv Datei, was tadellos funktioniert.

Nun habe ich den Versuch unternommen, das funktionierende Agent- Anlegescript mit dem Fehler verursachenden Script zum Import von Customer Usern zusammenzuführen. Customer User aus einer csv Datei Anlegen funktioniert nun auch prima. Nur das Update bestehender Customer User haut nicht hin... Der Aufruf und die Meldung:

Code: Select all

D:\OTRS\Perl\bin>perl d:\otrs\otrs\scripts\test.pl -s d:\utest.csv
Updating customer user '244' with password '244'
ERROR: test.pl-72 Perl: 5.8.8 OS: MSWin32 Time: Thu Sep 11 11:40:47 2008

 Message: Need User or CustomerID!

 Traceback (5756):
   Module: Kernel::System::CustomerUser::DB::CustomerUserDataGet (v1.67) Line: 4
13
   Module: Kernel::System::CustomerUser::DB::CustomerUserUpdate (v1.67) Line: 69
6
   Module: Kernel::System::CustomerUser::CustomerUserUpdate (v1.44) Line: 349
   Module: d:\otrs\otrs\scripts\test.pl (unknown version) Line: 99

Use of uninitialized value in concatenation (.) or string at d:/otrs/otrs/Kernel
/System/CustomerUser/DB.pm line 740, <IN> line 1.
Mein Test-csv:

Code: Select all

244;244@bla.de;244;244;XXX GmbH Kirchheim;1621234567;DoPi 1234 // SiPi 2345;Typ 1e39 // SiPu 3456

Anbei mal mein kläglicher Versuch:

Code: Select all

#!/usr/bin/perl -w
# --
# syncuser_csv2otrs.pl - sync csv user list or otrs
# Copyright (C) 2001-2008 xxx, http://otrs.org/
# --
# $Id: syncuser_csv2otrs.pl,v 1.10 2008/06/09 14:52:11 tr Exp $
# --
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# --

# config options / csv file - column 0-...
my %Fields = ();
$Fields{login} = 0;
$Fields{email} = 1;
$Fields{customer_id} = 2;
$Fields{pw} = 3;
$Fields{salutation} = 4;
$Fields{firstname} = 5;
$Fields{lastname} = 6;
$Fields{comment} = 7;

# use ../ as lib location
use File::Basename;
use FindBin qw($RealBin);
use lib dirname($RealBin);
use lib dirname($RealBin) . "/Kernel/cpan-lib";
use strict;
use warnings;

use Getopt::Std;
use Kernel::Config;
use Kernel::System::Log;
use Kernel::System::Main;
use Kernel::System::Time;
use Kernel::System::DB;
#use Kernel::System::User;
use Kernel::System::CustomerUser;

# common objects
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new();
$CommonObject{LogObject}    = Kernel::System::Log->new(
    LogPrefix => 'test.pl',
    %CommonObject
);
$CommonObject{MainObject} = Kernel::System::Main->new(%CommonObject);
$CommonObject{TimeObject} = Kernel::System::Time->new(%CommonObject);
$CommonObject{DBObject}   = Kernel::System::DB->new(%CommonObject);
#$CommonObject{UserObject} = Kernel::System::User->new(%CommonObject);
$CommonObject{DBObject} = Kernel::System::DB->new(%CommonObject);
$CommonObject{CustomerUserObject} = Kernel::System::CustomerUser->new(%CommonObject);

# get options
my %Opts = ();
getopt( 's', \%Opts );
my $End = "\n";
if ( !$Opts{s} ) {
    die "Need -s <CSV_FILE>\n";
}

my $Lang = $CommonObject{ConfigObject}->Get('DefaultLanguage') || 'de';

# read csv file
open (IN, "< $Opts{'s'}") || die "Can't read $Opts{'s'}: $!";
while( <IN>) {
	my $LineStrg = $_;
	$LineStrg =~ s/\"//g;
    my @Line = split(/;/, $LineStrg);

    # check if user extsis
    #my %UserData = $CommonObject{UserObject}->GetUserData( User => $Line[ $Fields{UserLogin} ] );
    my %CustomerUserData = $CommonObject{CustomerUserObject}->CustomerUserDataGet(User => $Line[$Fields{login}]);


    # if there is no pw in the csv list, gererate one
    if( !$Line[$Fields{pw}]) {
        $Line[$Fields{pw}] = $CommonObject{CustomerUserObject}->GenerateRandomPassword();
    }

    # update user
    if( %CustomerUserData) {
        print "Updating customer user '$Line[$Fields{login}]' with password '$Line[$Fields{pw}]'\n";
        $CommonObject{CustomerUserObject}->CustomerUserUpdate(
            UserSalutation => $Line[$Fields{salutation}] || '',
            UserFirstname => $Line[$Fields{firstname}],
            UserLastname => $Line[$Fields{lastname}],
            UserLogin => $Line[$Fields{login}],
            UserPassword => $Line[$Fields{pw}],
            UserEmail => $Line[$Fields{email}],
			CustomerID => $Line[$Fields{customer_id}] || '',
            UserCustomerID => $Line[$Fields{customer_id}] || '',
            UserComment => $Line[$Fields{comment}] || '',
            ValidID => 1,
            UserID => 1,
        );
     }

    # add user
    else {
         print "Add customer_user '$Line[$Fields{login}]' ($Line[$Fields{firstname}],$Line[$Fields{lastname}]) with password '$Line[$Fields{pw}]'\n";
        $CustomerUserData{UserID} = $CommonObject{CustomerUserObject}->CustomerUserAdd(
            UserSalutation => $Line[$Fields{salutation}] || '',
            UserFirstname => $Line[$Fields{firstname}],
            UserLastname => $Line[$Fields{lastname}],
            UserLogin => $Line[$Fields{login}],
            UserPassword => $Line[$Fields{pw}],
            UserEmail => $Line[$Fields{email}],
            CustomerID => $Line[$Fields{customer_id}] || '',
            UserCustomerID => $Line[$Fields{customer_id}] || '',
            UserComment => $Line[$Fields{comment}] || '',
            ValidID => 1,
            UserID => 1,
        );
    }
}
close( IN);
Wie gesagt... Anlegen neuer CUstomer aus einer csv geht, nur Ändern nicht.

Hilfe bitte :)

mfg
Michael
Wer Deutsch sät, wird Verständnis ernten!

Prod & Test jeweils:
OTRS 2.4.9
OTRS ITSM 2.1.1
MySQL
Apache
SLES11 SP0
Top
michael_maurer
Znuny advanced
Posts: 146
Joined: 07 Aug 2008, 09:20
Znuny Version: 2.4.9
Contact:
Contact michael_maurer

Customer User aus csv in OTRS Customer DB (Insert + Update)

Post by michael_maurer »

Ich hatte beim Update AUfruf die Source ID vergessen... Ich heLD

Code: Select all

$CommonObject{CustomerUserObject}->CustomerUserUpdate(
			Source        => $Line[$Fields{customer_id}] || '', # CustomerUser source config
			ID            => $Line[$Fields{login}], # current user login
			UserLogin     => $Line[$Fields{login}],       # new user login
			UserFirstname => $Line[$Fields{firstname}],
			UserLastname  => $Line[$Fields{lastname}],
			UserPassword  => $Line[$Fields{pw}],    # not required
			UserEmail     => $Line[$Fields{email}],
			UserCustomerID => $Line[$Fields{customer_id}] || '',
			UserSalutation => $Line[$Fields{salutation}] || '',
			UserComment => $Line[$Fields{comment}] || '',
			ValidID       => 1,
			UserID        => 123,
    );

Jetzt gehts!
Wer Deutsch sät, wird Verständnis ernten!

Prod & Test jeweils:
OTRS 2.4.9
OTRS ITSM 2.1.1
MySQL
Apache
SLES11 SP0
Top
marco
Znuny newbie
Posts: 7
Joined: 30 Jul 2009, 21:20
Znuny Version: 2.4.2

Customer User aus csv in OTRS Customer DB (Insert + Update)

Post by marco »

Hallo

Ich habe OTRS 2.4.2 installiert. Und für diese Version hatte noch eine Zeile gefehlt. Hier der komplette Code, wie er jetzt bei mir läuft:

synccustomer_csv2otrs.pl

Code: Select all

#!C:/PROGRA~1/OTRS/StrawberryPerl/perl/bin/perl.exe -w
# --
# synccustomer_csv2otrs.pl - sync csv user list or otrs
# Copyright (C) 2001-2008 xxx, http://otrs.org/
# --
# $Id: synccustomer_csv2otrs.pl,v 1.10 2008/06/09 14:52:11 tr Exp $
# --
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# --

# config options / csv file - column 0-...
my %Fields = ();
$Fields{login} = 0;
$Fields{email} = 1;
$Fields{customer_id} = 2;
$Fields{pw} = 3;
$Fields{salutation} = 4;
$Fields{firstname} = 5;
$Fields{lastname} = 6;
$Fields{comment} = 7;
$Fields{loginnew} = 8;
$Fields{gueltig} = 9;

# use ../ as lib location
use File::Basename;
use FindBin qw($RealBin);
use lib dirname($RealBin);
use lib dirname($RealBin) . "/Kernel/cpan-lib";
use strict;
use warnings;

use Getopt::Std;
use Kernel::Config;
use Kernel::System::Log;
use Kernel::System::Main;
use Kernel::System::Time;
use Kernel::System::DB;
#use Kernel::System::User;
use Kernel::System::CustomerUser;

# common objects
my %CommonObject = ();
$CommonObject{ConfigObject} = Kernel::Config->new();
$CommonObject{EncodeObject} = Kernel::System::Encode->new(%CommonObject);
$CommonObject{LogObject}    = Kernel::System::Log->new(
    LogPrefix => 'test.pl',
    %CommonObject
);
$CommonObject{MainObject} = Kernel::System::Main->new(%CommonObject);
$CommonObject{TimeObject} = Kernel::System::Time->new(%CommonObject);
$CommonObject{DBObject}   = Kernel::System::DB->new(%CommonObject);
#$CommonObject{UserObject} = Kernel::System::User->new(%CommonObject);
$CommonObject{DBObject} = Kernel::System::DB->new(%CommonObject);
$CommonObject{CustomerUserObject} = Kernel::System::CustomerUser->new(%CommonObject);

# get options
my %Opts = ();
getopt( 's', \%Opts );
my $End = "\n";
if ( !$Opts{s} ) {
    die "Need -s <CSV_FILE>\n";
}

my $Lang = $CommonObject{ConfigObject}->Get('DefaultLanguage') || 'de';

# read csv file
open (IN, "< $Opts{'s'}") || die "Can't read $Opts{'s'}: $!";
while( <IN>) {
   my $LineStrg = $_;
   $LineStrg =~ s/\"//g;
    my @Line = split(/;/, $LineStrg);

    # check if user extsis
    #my %UserData = $CommonObject{UserObject}->GetUserData( User => $Line[ $Fields{UserLogin} ] );
    my %CustomerUserData = $CommonObject{CustomerUserObject}->CustomerUserDataGet(User => $Line[$Fields{login}]);


    # if there is no pw in the csv list, gererate one
    if( !$Line[$Fields{pw}]) {
        $Line[$Fields{pw}] = $CommonObject{CustomerUserObject}->GenerateRandomPassword();
    }

    # update user
    if( %CustomerUserData) {
        print "Updating customer user '$Line[$Fields{login}]' with password '$Line[$Fields{pw}]'\n";
$CommonObject{CustomerUserObject}->CustomerUserUpdate(
         Source        => $Line[$Fields{customer_id}] || '', # CustomerUser source config
         ID            => $Line[$Fields{login}], # current user login
         UserLogin     => $Line[$Fields{loginnew}],       # new user login
         UserFirstname => $Line[$Fields{firstname}],
         UserLastname  => $Line[$Fields{lastname}],
         UserPassword  => $Line[$Fields{pw}],    # not required
         UserEmail     => $Line[$Fields{email}],
         UserCustomerID => $Line[$Fields{customer_id}] || '',
         UserSalutation => $Line[$Fields{salutation}] || '',
         UserComment => $Line[$Fields{comment}] || '',
         ValidID       => $Line[$Fields{gueltig}],
         UserID        => 123,
    );
     }

    # add user
    else {
         print "Add customer_user '$Line[$Fields{login}]' ($Line[$Fields{firstname}],$Line[$Fields{lastname}]) with password '$Line[$Fields{pw}]'\n";
        $CustomerUserData{UserID} = $CommonObject{CustomerUserObject}->CustomerUserAdd(
            UserSalutation => $Line[$Fields{salutation}] || '',
            UserFirstname => $Line[$Fields{firstname}],
            UserLastname => $Line[$Fields{lastname}],
            UserLogin => $Line[$Fields{loginnew}],
            UserPassword => $Line[$Fields{pw}],
            UserEmail => $Line[$Fields{email}],
            CustomerID => $Line[$Fields{customer_id}] || '',
            UserCustomerID => $Line[$Fields{customer_id}] || '',
            UserComment => $Line[$Fields{comment}] || '',
            ValidID => $Line[$Fields{gueltig}],
            UserID => 1,
        );
    }
}
close( IN);
Ich habe mit noch zwei neue Spalten hinzugefügt: loginnew und valid/activ. Damit kann ich das Login anpassen, falls benötigt (ansonsten steht in der Spalte einfach das Gleiche wie in Spalte 0) und ich kann mit Active die Einträge auf gültig oder ungültig setzen. Beispiel:

Code: Select all

1234321;abc@abc.ch;14;123;Mr.;Reto;Amba;ABC GmbH;14;1
Kann mir nun noch jemand sagen, wie ich an die anderen Felder rankomme (Telefon, Fax, Strasse, PLZ usw)?

Gruss
Marco
OTRS 2.4.2 auf Windows XP Prof
Top
michael_maurer
Znuny advanced
Posts: 146
Joined: 07 Aug 2008, 09:20
Znuny Version: 2.4.9
Contact:
Contact michael_maurer

Customer User aus csv in OTRS Customer DB (Insert + Update)

Post by michael_maurer »

Hallöchen,

nur eine Vermutung, habs noch nich selbst getestet:

1. gewünschte Spalten in der Customer_DB Tabelle einfügen oder besser neue Tabelle anlegen wegen Updates.

2. Mapping der Felder in der Config.pm für die entsprechende Customer_DB AuthQuelle erweitern oder weitere Quelle anlegen:
Map => [
# note: Login, Email and CustomerID needed!
# var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
[ 'UserPassword', 'Password', 'pw', 0, 0, 'var', '', 0 ],
[ 'UserEmail', 'Email', 'email', 1, 1, 'var', '', 0 ],
[ 'UserPhone', 'phone', 'phone', 1, 1, 'var', '', 0 ],
usw.

3. Dann einfach in dem Import-Script die Zeile(n) hinzufügen:

Einzulesendes Feld hinzufügen:
$Fields{phone} = 10;

Bei Add und Update:
CustomerID => $Line[$Fields{customer_id}] || '',
UserCustomerID => $Line[$Fields{customer_id}] || '',
UserComment => $Line[$Fields{comment}] || '',
UserPhone => $Line[$Fields{phone}] || '',
ValidID => $Line[$Fields{gueltig}],
usw.

Die Feldnamen müssen natürlich konsistent sein... Also wenns Feld in der Tabelle phone genannt hast, musses im script ebenfalls phone getauft werden. Zur Sicherheit lieber case-sensitive behandeln. Denke das müsste so funktionieren... Warum auch nicht :)

mfg
Michael
Wer Deutsch sät, wird Verständnis ernten!

Prod & Test jeweils:
OTRS 2.4.9
OTRS ITSM 2.1.1
MySQL
Apache
SLES11 SP0
Top
Locked

Return to “Hilfe”