LDAP not syncing all attributes

Moderator: crythias

Post Reply
nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

LDAP not syncing all attributes

Post by nmuleski »

I have LDAP authenticating to AD and I'm bringing in several attributes. From what I understand I can sync pretty much any AD attribute using the UserSyncMap feature. I am bringing in First Name, Last Name, Email, Title, and Phone Number. For some reason the phone number isn't working though. All I get is a dash (-) when I try to use the tag (<OTRS_CURRENT_UserDirect>).

I have several suspicions as to why it isn't working but I can find answers anywhere.

1 - You can only map to certain fields in OTRS such as Userfirstname, Userlastname, Usertitle. I made up the UserDirect and UserPhone so maybe that's why it isn't working?

2 - OTRS is installed on Ubuntu Server and I'm assuming that restarting the networking components is enough to trigger changes made in the Config.pm file. I'm also logging out every time. Do I need to do something more for the changes to take effect?

3 - The telephoneNumber attribute from AD somehow isn't compatible with OTRS.


Could somebody help me out please? I've been at this all day!

Here is the relevant code from my Config.pm file if it helps:

Code: Select all

 $Self->{'AuthModule::UseSyncBackend'} = 'AuthSyncBackend';
    $Self->{'AuthSyncModule'} = 'Kernel::System::Auth::Sync::LDAP';
    $Self->{'AuthSyncModule::LDAP::Host'} = 'OURDC.com';
    $Self->{'AuthSyncModule::LDAP::BaseDN'} = 'dc=domain,dc=com';
    $Self->{'AuthSyncModule::LDAP::UID'} = 'sAMAccountName';
    $Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'CN=AD Reader,OU=Agents,DC=p$
    $Self->{'AuthSyncModule::LDAP::SearchUserPw'} = 'OURPW';
    #$Self->{'AuthSyncModule::LDAP::AlwaysFilter'} = '(objectclass=user)';
    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'title',
        UserPhone     => 'description',
        UserDirect    => 'telephoneNumber'
    };
# Syncs agents to 'users' group at first login
    $Self->{'AuthSyncModule::LDAP::UserSyncInitialGroups'} = [
        'users',
        'stats',
        'faq',
    ];

THANKS! :)
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

Take a look at this thread:

viewtopic.php?f=61&t=19915
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:Take a look at this thread:

viewtopic.php?f=61&t=19915

Thanks! So I ended up following your How-To at the end of that thread: viewtopic.php?f=60&t=19936

But I still can't get it to work.

This is my XML file:

Code: Select all

<?xml version="1.0" encoding="utf-8" ?>
<otrs_config version="1.0" init="Framework">
    <ConfigItem Name="PreferencesGroups###UserDirect" Required="0" Valid="1">
        <Description Translatable="1">Direct dial telephone number.</Description>
        <Group>Framework</Group>
        <SubGroup>Frontend::Agent::Preferences</SubGroup>
        <Setting>
            <Hash>
                <Item Key="Module">Kernel::Output::HTML::PreferencesGeneric</Item>
                <Item Key="Column">Other Settings</Item>
                <Item Key="Label" Translatable="1">DirectTelephone</Item>
                <Item Key="Key" Translatable="1">DirectTelephone</Item>
                <Item Key="Block">Input</Item>
                <Item Key="Data">$Env{"UserDirect"}</Item>
                <Item Key="PrefKey">UserDirect</Item>
                <Item Key="Prio">6100</Item>
                <Item Key="Active">1</Item>
            </Hash>
        </Setting>
    </ConfigItem>
</otrs_config>
It's located at /opt/otrs/Kernel/Config/Files/AgentPrefs-myfield.xml

The attached file is what I see under SysConfig.

In my Config.pm file I still have:

Code: Select all

$
    $Self->{'AuthModule::UseSyncBackend'} = 'AuthSyncBackend';
    $Self->{'AuthSyncModule'} = 'Kernel::System::Auth::Sync::LDAP';
    $Self->{'AuthSyncModule::LDAP::Host'} = 'pjdist-dc-1.pjdist.com';
    $Self->{'AuthSyncModule::LDAP::BaseDN'} = 'dc=pjdist,dc=com';
    $Self->{'AuthSyncModule::LDAP::UID'} = 'sAMAccountName';
    $Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'CN=AD Reader,OU=PJ Agents,DC=p$
    $Self->{'AuthSyncModule::LDAP::SearchUserPw'} = '2013LDAPpj';
    #$Self->{'AuthSyncModule::LDAP::AlwaysFilter'} = '(objectclass=user)';
    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'description',
        UserDirect       => 'telephoneNumber',
    };
Not sure what I'm doing wrong. I'm not too familiar with Pearl or XML.
You do not have the required permissions to view the files attached to this post.
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

the howto at the end doesn't work for ldap.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:the howto at the end doesn't work for ldap.
Well that would explain it. It just seemed like a more straight forward approach. I'll try the other method on Monday.

Thanks for the response!
OTRS 3.2.6
Ubuntu Server 12.4.02

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

I followed the steps in the original article but phone number attribute is still not syncing.

Config.pm:

Code: Select all

    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'description',
        UserDirect    => 'telephoneNumber',
    };
User.pm:

Code: Select all

    # update db
    return if !$Self->{DBObject}->Do(
        SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, phone = ? "
            . " WHERE $Self->{UserTableUserID} = ?",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserID}, \$Param{UserDirect}
        ],
    );

Code: Select all

    # get initial data
    my @Bind;
    my $SQL = "SELECT $Self->{UserTableUserID}, $Self->{UserTableUser}, "
        . " title, first_name, last_name, $Self->{UserTableUserPW}, valid_id, "
        . " create_time, change_time, UserDirect FROM $Self->{UserTable} WHERE ";

Code: Select all

    my %Data;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $Data{UserID}        = $Row[0];
        $Data{UserLogin}     = $Row[1];
        $Data{UserTitle}     = $Row[2];
        $Data{UserFirstname} = $Row[3];
        $Data{UserLastname}  = $Row[4];
        $Data{UserPw}        = $Row[5];
        $Data{ValidID}       = $Row[6];
        $Data{CreateTime}    = $Row[7];
        $Data{ChangeTime}    = $Row[8];
        $Data{UserDirect}    = $Row[9];
    }

The UserDirect column of the users table is still null even after restarting Apache.
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

first

Code: Select all

ALTER TABLE `users` ADD `phone` VARCHAR(15) NULL ;
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

Code: Select all

SELECT:
        . " create_time, change_time, UserDirect FROM $Self->{UserTable} WHERE ";
s/UserDirect/phone/
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:first

Code: Select all

ALTER TABLE `users` ADD `phone` VARCHAR(15) NULL ;

I did

Code: Select all

ALTER TABLE 'users' ADD 'UserDirect' VARCHAR(20)
OTRS 3.2.6
Ubuntu Server 12.4.02

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:

Code: Select all

SELECT:
        . " create_time, change_time, UserDirect FROM $Self->{UserTable} WHERE ";
s/UserDirect/phone/
Sorry, but I'm not sure what you are saying to do?
OTRS 3.2.6
Ubuntu Server 12.4.02

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

Another thing I forgot to mention is that I added this:

Code: Select all

   $Self->{PreferencesGroups}->{UserDepartment} = {
        Module => 'Kernel::Output::HTML::PreferencesGeneric',
        Column => 'Other Settings',
        Label => 'Additional Data',
        Key => 'DirectPhone',
        Block => 'Input',
        Data => '$Env{"UserDirect"}',
        PrefKey => 'UserDirect',
        Prio => 7000,
        Active => 1,
    };
to the Config.pm file. Is that the right place?
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

If you're going to UPDATE phone, you will need to SELECT phone
If you created a column/field in your table that is literally called UserDirect, then you will need to UPDATE Userdirect

Code: Select all

       SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, ***phone*** = ? "
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:If you're going to UPDATE phone, you will need to SELECT phone
If you created a column/field in your table that is literally called UserDirect, then you will need to UPDATE Userdirect

Code: Select all

       SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, ***phone*** = ? "

Got it, thanks. Unfortunately even after changing phone to UserDirect the field still isn't populating.
OTRS 3.2.6
Ubuntu Server 12.4.02

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

nmuleski wrote:Another thing I forgot to mention is that I added this:

Code: Select all

   $Self->{PreferencesGroups}->{UserDepartment} = {
        Module => 'Kernel::Output::HTML::PreferencesGeneric',
        Column => 'Other Settings',
        Label => 'Additional Data',
        Key => 'DirectPhone',
        Block => 'Input',
        Data => '$Env{"UserDirect"}',
        PrefKey => 'UserDirect',
        Prio => 7000,
        Active => 1,
    };
to the Config.pm file. Is that the right place?

Was the Config.pm file the right place for this?
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

I'm not sure what your current status is. There were specific things that needed to be in place.

Code: Select all

alter table users add myFieldshortname varchar(20);

Code: Select all

    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        UserFirstname => 'givenName',
        UserLastname => 'sn',
        UserEmail => 'mail',
        UserMyField => 'telephoneNumber', 
    };

Code: Select all

    # update db
    return if !$Self->{DBObject}->Do(
        SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, myFieldshortname = ? "
            . " WHERE $Self->{UserTableUserID} = ?",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserMyField}, \$Param{UserID}
        ],
    );

Code: Select all

    # get initial data
    my @Bind;
    my $SQL = "SELECT $Self->{UserTableUserID}, $Self->{UserTableUser}, "
        . " title, first_name, last_name, $Self->{UserTableUserPW}, valid_id, "
        . " create_time, change_time, myFieldshortname FROM $Self->{UserTable} WHERE ";

Code: Select all

    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $Data{UserID}        = $Row[0];
        $Data{UserLogin}     = $Row[1];
        $Data{UserTitle}     = $Row[2];
        $Data{UserFirstname} = $Row[3];
        $Data{UserLastname}  = $Row[4];
        $Data{UserPw}        = $Row[5];
        $Data{ValidID}       = $Row[6];
        $Data{CreateTime}    = $Row[7];
        $Data{ChangeTime}    = $Row[8];
        $Data{UserMyField}    = $Row[9];
    }
What you have ... I don't really know at this point.
Last edited by crythias on 25 Mar 2014, 23:14, edited 1 time in total.
Reason: wrong field order. Fixed per item below.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

JoeFoster
OTRS newbie
Posts: 6
Joined: 18 Feb 2014, 13:23
OTRS Version?: 3.3.4

Re: LDAP not syncing all attributes

Post by JoeFoster »

Hello Crythias,

I. I altered the database...
crythias wrote:alter table users add phone varchar(20);
II. ...made the following changes to "/opt/otrs/Kernel/System/User.pm"...
crythias wrote: # update db
return if !$Self->{DBObject}->Do(
SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
. " $Self->{UserTableUser} = ?, valid_id = ?, "
. " change_time = current_timestamp, change_by = ?, phone = ? "
. " WHERE $Self->{UserTableUserID} = ?",
Bind => [
\$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
\$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserID}, \$Param{UserPhone},
],
);
crythias wrote: # get initial data
my @Bind;
my $SQL = "SELECT $Self->{UserTableUserID}, $Self->{UserTableUser}, "
. " title, first_name, last_name, $Self->{UserTableUserPW}, valid_id, "
. " create_time, change_time, phone FROM $Self->{UserTable} WHERE ";
crythias wrote: while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
$Data{UserID} = $Row[0];
$Data{UserLogin} = $Row[1];
$Data{UserTitle} = $Row[2];
$Data{UserFirstname} = $Row[3];
$Data{UserLastname} = $Row[4];
$Data{UserPw} = $Row[5];
$Data{ValidID} = $Row[6];
$Data{CreateTime} = $Row[7];
$Data{ChangeTime} = $Row[8];
$Data{UserPhone} = $Row[9];
}
III. ...and extended the mapping in "/opt/otrs/Kernel/Config/Files/ZZZAgents.pm"
# AuthSyncModule::LDAP::UserSyncMap
# (map if agent should create/synced from LDAP to DB after successful login)
$Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
# DB -> LDAP
UserFirstname => 'givenName',
UserLastname => 'sn',
UserEmail => 'mail',
UserPhone => 'telephoneNumber',
};
IV. Finally restarted the server and logged in as an agent.

Unfortunately the phone number is still not synced from LDAP to the OTRS database - the phone value in the user table is still NULL.
I'm also not entirely sure how to display the phone number on the "Edit Agent" screen once it is working.

Do you by chance have any suggestions where/what else to look into?

Best Regards,
Joe
OTRS v3.3.4 with MySQL DB on Ubuntu 12.04

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

JoeFoster wrote:III. ...and extended the mapping in "/opt/otrs/Kernel/Config/Files/ZZZAgents.pm"
did you mistype that?
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

nmuleski wrote:
nmuleski wrote:Another thing I forgot to mention is that I added this:

Code: Select all

   $Self->{PreferencesGroups}->{UserDepartment} = {
        Module => 'Kernel::Output::HTML::PreferencesGeneric',
        Column => 'Other Settings',
        Label => 'Additional Data',
        Key => 'DirectPhone',
        Block => 'Input',
        Data => '$Env{"UserDirect"}',
        PrefKey => 'UserDirect',
        Prio => 7000,
        Active => 1,
    };
to the Config.pm file. Is that the right place?

Was the Config.pm file the right place for this?
I'm not saying it's the wrong place, but I'm not sure why you did it.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

JoeFoster
OTRS newbie
Posts: 6
Joined: 18 Feb 2014, 13:23
OTRS Version?: 3.3.4

Re: LDAP not syncing all attributes

Post by JoeFoster »

crythias wrote:
JoeFoster wrote:III. ...and extended the mapping in "/opt/otrs/Kernel/Config/Files/ZZZAgents.pm"
did you mistype that?
nope, that's where the LDAP settings for the agents are configured on this system; I've also got "ZZZCustomerUser.pm" in the same directory for all customer related settings
OTRS v3.3.4 with MySQL DB on Ubuntu 12.04

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

I expected these to be in Config.pm, so perhaps you might want to debug by adding logobject entries . I could probably do it as well, but I'm not sure I'd follow your customization and be able to confirm the information.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

Ok, so I was finally able to get back to this today, but I still cannot get it working.

1.

Code: Select all

user otrs; alter table users add DirectPhone varchar(20);
2. Changes made to /opt/otrs/Kernel/Config.pm

Code: Select all

#$Self->{'AuthSyncModule::LDAP::AlwaysFilter'} = '(objectclass=user)';
    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'description',
        UserDirect    => 'telephoneNumber',
    };
3. Changes made to /opt/otrs/Kernel/System/User.pm

Code: Select all

    # update db
    return if !$Self->{DBObject}->Do(
        SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, DirectPhone = ? "
            . " WHERE $Self->{UserTableUserID} = ?",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserID}, \$Param{UserDirect}
        ],
    );

Code: Select all

    # get initial data
    my @Bind;
    my $SQL = "SELECT $Self->{UserTableUserID}, $Self->{UserTableUser}, "
        . " title, first_name, last_name, $Self->{UserTableUserPW}, valid_id, "
        . " create_time, change_time, DirectPhone FROM $Self->{UserTable} WHERE ";

Code: Select all

    my %Data;
    while ( my @Row = $Self->{DBObject}->FetchrowArray() ) {
        $Data{UserID}        = $Row[0];
        $Data{UserLogin}     = $Row[1];
        $Data{UserTitle}     = $Row[2];
        $Data{UserFirstname} = $Row[3];
        $Data{UserLastname}  = $Row[4];
        $Data{UserPw}        = $Row[5];
        $Data{ValidID}       = $Row[6];
        $Data{CreateTime}    = $Row[7];
        $Data{ChangeTime}    = $Row[8];
        $Data{UserDirect}    = $Row[9];
    }

Then I restarted Apache and logged out and back in but still the DirectPhone column is empty in the database. I also tried restarting the server.

Do you have any other ideas?
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

I don't have ideas except to create logs.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:I don't have ideas except to create logs.
What type of logs? I'm guessing something beyond what's available in the Admin CP?

You mention logobject entries in this post and I'm guessing that's a Perl thing? Can you explain further?

Your help is much appreciated!
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

ok, first I'd be looking at something like ... is a field assigned?

do I get to forinstance:

Kernel/System/Auth/Sync/LDAP.pm

Code: Select all

       elsif (%SyncUser) {
 $Self->{LogObject}->Log( Priority => 'notice', Message => "I'm syncing!" );

            # get user data
            my %UserData = $Self->{UserObject}->GetUserData( User => $Param{User} );


            # check for changes
            my $AttributeChange;
            ATTRIBUTE:
            for my $Attribute ( sort keys %SyncUser ) {
if ($Attribute eq "UserDirect") { $Self->{LogObject}->Log( Priority => 'notice', Message => "Got UserDirect=$SyncUser{$Attribute}" );
                next ATTRIBUTE if $SyncUser{$Attribute} eq $UserData{$Attribute};
                $AttributeChange = 1;
                last ATTRIBUTE;
            }

            if ($AttributeChange) {
                $Self->{UserObject}->UserUpdate(
                    %UserData,
                    UserID    => $UserID,
                    UserLogin => $Param{User},
                    %SyncUser,
                    UserType     => 'User',
                    ChangeUserID => 1,
                );
            }
        }
Something like that anyway... and similar where it's storing and similar where it's fetching.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

Before I mess around with setting up logging, do you know if there is something more required in the Config.pm file to allow the Agent LDAP information to sync at login as opposed to just syncing when the user is first created?

Code: Select all

#-------------------------------------------------------------------------------------------#
#                                  Agent Authentication                                     #
#-------------------------------------------------------------------------------------------#
   # $Self->{'AuthModule::UseSyncBackend'} = 'AuthSyncBackend';
    $Self->{'AuthModule'} = 'Kernel::System::Auth::LDAP';
    $Self->{'AuthModule::LDAP::Host'} = 'pjdist-dc-1.pjdist.com';
    $Self->{'AuthModule::LDAP::BaseDN'} = 'DC=pjdist,DC=com';
    $Self->{'AuthModule::LDAP::UID'} = 'sAMAccountName';
    $Self->{'AuthModule::LDAP::GroupDN'} = 'CN=OTRS Agents,OU=Program - Permissions,OU=PJ Security Groups,DC=pjdist,D$
    $Self->{'AuthModule::LDAP::AccessAttr'} = 'member';
    $Self->{'AuthModule::LDAP::UserAttr'} = 'DN';
    $Self->{'AuthModule::LDAP::SearchUserDN'} = 'CN=AD Reader,OU=PJ Agents,DC=pjdist,DC=com';
    $Self->{'AuthModule::LDAP::SearchUserPw'} = '2013LDAPpj';
    $Self->{'AuthModule::LDAP::AlwaysFilter'} = '(objectclass=user)';

#-------------------------------------------------------------------------------------------#
#                                  Agent Data                                               #
#-------------------------------------------------------------------------------------------#
    $Self->{'AuthModule::UseSyncBackend'} = 'AuthSyncBackend';
    $Self->{'AuthSyncModule'} = 'Kernel::System::Auth::Sync::LDAP';
    $Self->{'AuthSyncModule::LDAP::Host'} = 'dc.domain.com';
    $Self->{'AuthSyncModule::LDAP::BaseDN'} = 'dc=domain,dc=com';
    $Self->{'AuthSyncModule::LDAP::UID'} = 'sAMAccountName';
    $Self->{'AuthSyncModule::LDAP::SearchUserDN'} = 'CN=ADUSER,OU=Agents,DC=domain,DC=com';
    $Self->{'AuthSyncModule::LDAP::SearchUserPw'} = 'password';
    #$Self->{'AuthSyncModule::LDAP::AlwaysFilter'} = '(objectclass=user)';
    $Self->{'AuthSyncModule::LDAP::UserSyncMap'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'title',
        UserDirect    => 'telephoneNumber',
    };
# Syncs agents to 'users' group at first login
    $Self->{'AuthSyncModule::LDAP::UserSyncInitialGroups'} = [
        'users',
        'stats',
        'faq',
    ];
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

To answer your question, I'd do a simple test: Modify the name of an individual in Active Directory (like an additional space, or a period, just long enough to test.)

If OTRS updates that information on an existing user, you have your answer.
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:To answer your question, I'd do a simple test: Modify the name of an individual in Active Directory (like an additional space, or a period, just long enough to test.)

If OTRS updates that information on an existing user, you have your answer.

It does not. So I guess what it's doing is syncing only the first time the user logs in and never again. I see no reason why that would since we have nearly the same LDAP settings as we do for customer LDAP sync, which works fine.
OTRS 3.2.6
Ubuntu Server 12.4.02

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

Finally, success!

I enabled binary logging on MySQL and saw the update statement read this:

Code: Select all

UPDATE users SET title = 'Titie', first_name = 'Nate', last_name = 'M',  login = 'natem', valid_id = '1',  change_time = '2014-03-25 15:52:41', change_by = '1', DirectPhone = '2'  WHERE id = '555-555-5555'
Then it was obvious the issue was that the parameters were out of order. Sure enough, in User.pm I had this:

Code: Select all

    return if !$Self->{DBObject}->Do(
        SQL => "UPDATE $Self->{UserTable} SET title = ?, first_name = ?, last_name = ?, "
            . " $Self->{UserTableUser} = ?, valid_id = ?, "
            . " change_time = current_timestamp, change_by = ?, DirectPhone = ? "
            . " WHERE $Self->{UserTableUserID} = ?",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserID}, \$Param{UserDirect}
        ],
    );
Once I moved \$Param{UserID} to the end of the parameter list it worked.

Thanks for all your help crythias! And thanks for all your great work on the forums!
OTRS 3.2.6
Ubuntu Server 12.4.02

crythias
Moderator
Posts: 10126
Joined: 04 May 2010, 18:38
OTRS Version?: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: LDAP not syncing all attributes

Post by crythias »

I apologize for a (in retrospect) obvious oversight. I've amended my posts to reflect this information.

Thanks for sticking with this. On the one side, you probably could tell I'm on the right track. On the other, you know.. oops. Yeah. Sorry. :(
OTRS 5.0.x (private/testing/public) on Linux with MySQL database. Also on github.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask

nmuleski
OTRS newbie
Posts: 22
Joined: 17 May 2013, 20:51
OTRS Version?: 3.2.6
Real Name: nathan muleski
Company: Palmer Johnson Power Systems

Re: LDAP not syncing all attributes

Post by nmuleski »

crythias wrote:I apologize for a (in retrospect) obvious oversight. I've amended my posts to reflect this information.

Thanks for sticking with this. On the one side, you probably could tell I'm on the right track. On the other, you know.. oops. Yeah. Sorry. :(
No problem, everybody makes mistakes! Thanks again for all the help.
OTRS 3.2.6
Ubuntu Server 12.4.02

vincent13
OTRS newbie
Posts: 43
Joined: 28 May 2013, 12:38
OTRS Version?: 3.2000
Company: cpr sncf

Re: LDAP not syncing all attributes

Post by vincent13 »

Hello,

thanks for all the informations in this ticket !
Just success to setup a new field and sync on LDAP ;)

is there a way to setup a readonly field in the preferences interface ?
lab : otrs 5rc1 test : otrs 3.3.15 prod : cluster 3.3.15

xsign
OTRS newbie
Posts: 2
Joined: 09 Apr 2015, 14:45
OTRS Version?: 3.0.2

Re: LDAP not syncing all attributes

Post by xsign »

Just in case someone needs this again. I wanted to change some self-defined preferences such as phone (UserAgentenDurchwahl) and department (UserAgentenAbteilung). It's not needed to alter the table to achieve this. This should be a standard behaviour imho, but it isn't. So I've hotfixed the packages:

Kernel/Config.pm:

Code: Select all

    $Self->{'AuthSyncModule::LDAP::UserPreferencesSyncMap'} = {
      # DB -> LDAP (user preferences)
      UserAgentenDurchwahl  => 'telephoneNumber',
      UserAgentenAbteilung  => 'department',
    }

Kernel/System/Auth/Sync/LDAP.pm, add on line 358 (OTRS 4)

Code: Select all


    # sync user preferences from ldap
    my $UserPreferencesSyncMap = $ConfigObject->Get( 'AuthSyncModule::LDAP::UserPreferencesSyncMap' . $Self->{Count} );
    if ($UserPreferencesSyncMap && $UserID) {

        # get whole user dn
        my %SyncUserPreferences;
        for my $Entry ( $Result->all_entries() ) {
            for my $Key ( sort keys %{$UserPreferencesSyncMap} ) {
              
                my $AttributeNames = $UserPreferencesSyncMap->{$Key};
                if ( ref $AttributeNames ne 'ARRAY' ) {
                    $AttributeNames = [$AttributeNames];
                }
                ATTRIBUTE_NAME:
                for my $AttributeName ( @{$AttributeNames} ) {
                    if ( $AttributeName =~ /^_/ ) {
                        $SyncUserPreferences{$Key} = substr( $AttributeName, 1 );
                        last ATTRIBUTE_NAME;
                    }
                    elsif ( $Entry->get_value($AttributeName) ) {
                        $SyncUserPreferences{$Key} = $Entry->get_value($AttributeName);
                        last ATTRIBUTE_NAME;
                    }
                }

                # e. g. set utf-8 flag
                $SyncUserPreferences{$Key} = $Self->_ConvertFrom(
                    $SyncUserPreferences{$Key},
                    'utf-8',
                );
            }
        }

        # update user preferences attributes (only if changed)
        if (%SyncUserPreferences) {

            # get user data
            my %UserPreferencesData = $UserObject->GetPreferences( UserID => $UserID );

            # check for changes
            my $AttributeChange;
            ATTRIBUTE:
            for my $Attribute ( sort keys %SyncUserPreferences ) {
                next ATTRIBUTE if $SyncUserPreferences{$Attribute} eq $UserPreferencesData{$Attribute};
                $AttributeChange = 1;
                last ATTRIBUTE;
            }

            if ($AttributeChange) {
                for my $Attribute ( sort keys %SyncUserPreferences ) {
                  $UserObject->SetPreferences(
                    Key    => $Attribute,
                    Value  => $SyncUserPreferences{$Attribute},
                    UserID => $UserID,
                  );
                }
            }
        }
    }
Hope it helps.

1soproni
OTRS newbie
Posts: 17
Joined: 07 Dec 2015, 18:19
OTRS Version?: 5.0.4
Real Name: Csaba Németh

Re: LDAP not syncing all attributes

Post by 1soproni »

Great! It works also on otrs 5.
Could you please send it to the repo?
xsign wrote:Just in case someone needs this again. I wanted to change some self-defined preferences such as phone (UserAgentenDurchwahl) and department (UserAgentenAbteilung). It's not needed to alter the table to achieve this. This should be a standard behaviour imho, but it isn't. So I've hotfixed the packages:

Kernel/Config.pm:

Code: Select all

    $Self->{'AuthSyncModule::LDAP::UserPreferencesSyncMap'} = {
      # DB -> LDAP (user preferences)
      UserAgentenDurchwahl  => 'telephoneNumber',
      UserAgentenAbteilung  => 'department',
    }

Kernel/System/Auth/Sync/LDAP.pm, add on line 358 (OTRS 4)

Code: Select all


    # sync user preferences from ldap
    my $UserPreferencesSyncMap = $ConfigObject->Get( 'AuthSyncModule::LDAP::UserPreferencesSyncMap' . $Self->{Count} );
    if ($UserPreferencesSyncMap && $UserID) {

        # get whole user dn
        my %SyncUserPreferences;
        for my $Entry ( $Result->all_entries() ) {
            for my $Key ( sort keys %{$UserPreferencesSyncMap} ) {
              
                my $AttributeNames = $UserPreferencesSyncMap->{$Key};
                if ( ref $AttributeNames ne 'ARRAY' ) {
                    $AttributeNames = [$AttributeNames];
                }
                ATTRIBUTE_NAME:
                for my $AttributeName ( @{$AttributeNames} ) {
                    if ( $AttributeName =~ /^_/ ) {
                        $SyncUserPreferences{$Key} = substr( $AttributeName, 1 );
                        last ATTRIBUTE_NAME;
                    }
                    elsif ( $Entry->get_value($AttributeName) ) {
                        $SyncUserPreferences{$Key} = $Entry->get_value($AttributeName);
                        last ATTRIBUTE_NAME;
                    }
                }

                # e. g. set utf-8 flag
                $SyncUserPreferences{$Key} = $Self->_ConvertFrom(
                    $SyncUserPreferences{$Key},
                    'utf-8',
                );
            }
        }

        # update user preferences attributes (only if changed)
        if (%SyncUserPreferences) {

            # get user data
            my %UserPreferencesData = $UserObject->GetPreferences( UserID => $UserID );

            # check for changes
            my $AttributeChange;
            ATTRIBUTE:
            for my $Attribute ( sort keys %SyncUserPreferences ) {
                next ATTRIBUTE if $SyncUserPreferences{$Attribute} eq $UserPreferencesData{$Attribute};
                $AttributeChange = 1;
                last ATTRIBUTE;
            }

            if ($AttributeChange) {
                for my $Attribute ( sort keys %SyncUserPreferences ) {
                  $UserObject->SetPreferences(
                    Key    => $Attribute,
                    Value  => $SyncUserPreferences{$Attribute},
                    UserID => $UserID,
                  );
                }
            }
        }
    }
Hope it helps.

xsign
OTRS newbie
Posts: 2
Joined: 09 Apr 2015, 14:45
OTRS Version?: 3.0.2

Re: LDAP not syncing all attributes

Post by xsign »

Any developer that I can send it to?

jojo
Moderator
Posts: 14844
Joined: 26 Jan 2007, 14:50
OTRS Version?: Git Master
Contact:

Re: LDAP not syncing all attributes

Post by jojo »

you can place your pull request here: https://github.com/OTRS/otrs
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com

Post Reply