Sync phone number from AD

[emning]

I have AD authentication setup and working, but I'm having trouble figuring out how to sync info for new users from AD into FreeText fields, or even the comment field.

Here is an attempt to sync the phone number into the comment field:

Code: Select all

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

The title synchronizes fine into UserTitle, but the phone number is nowhere to be found. I've tried many variations on this, as well as putting the data in FreeText fields I've made (named UserPhoneNumber etc.).

Anyone solved this already?

[gears]

I'm having the same issue as the OP. The AD attribute 'title' syncs fine, but still unable to pull AD attribute 'telephoneNumber' or 'mobile'. I have confirmed these are all populated correctly in AD via ADSI Edit & AD Users consoles.

Initially I started by enabling these in the agent preferences by the steps @ http://blog.otrs.org/2011/09/05/agent-s ... ignatures/ (much thanks to Nils for this)
Per those instructions, I created MyConfig01.xml and MyConfig02.xml basically copy/pasting and put them in Kernel/Config/Files/

Code: Select all

root@IT:/opt/otrs/Kernel/Config/Files# cat MyConfig01.xml
<?xml version="1.0" encoding="utf-8" ?>
<otrs_config version="1.0" init="Framework">
    <ConfigItem Name="PreferencesGroups###PersonalTitle" Required="0" Valid="0">
        <Description Translatable="1">Defines the config parameters of this item, to be shown in the preferences view.</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">Agent title</Item>
                <Item Key="Key" Translatable="1">Agent title</Item>
                <Item Key="Block">Input</Item>
                <Item Key="Data">$Env{"UserTitle"}</Item>
                <Item Key="PrefKey">UserTitle</Item>
                <Item Key="Prio">7010</Item>
                <Item Key="Active">1</Item>
            </Hash>
        </Setting>
    </ConfigItem>
</otrs_config>
root@IT:/opt/otrs/Kernel/Config/Files# cat MyConfig02.xml
<?xml version="1.0" encoding="utf-8" ?>
<otrs_config version="1.0" init="Framework">
    <ConfigItem Name="PreferencesGroups###PersonalPhoneNumber" Required="0" Valid="0">
        <Description Translatable="1">Defines the config parameters of this item, to be shown in the preferences view.</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">Agent phone number</Item>
                <Item Key="Key" Translatable="1">Agent phone number</Item>
                <Item Key="Block">Input</Item>
                <Item Key="Data">$Env{"UserPhone"}</Item>
                <Item Key="PrefKey">UserPhone</Item>
                <Item Key="Prio">7000</Item>
                <Item Key="Active">1</Item>
            </Hash>
        </Setting>
    </ConfigItem>
</otrs_config>

OTRS successfully picked up on these files in SysConfig entry in Framework -> Frontend::Agent::Preferences



I enabled these two and also added new lines to Config.pm to actually sync the info from AD but only UserTitle is pulling data while UserPhone is still blank on each agents page.

example from
/otrs/index.pl?Action=AdminUser;Subaction=Change;UserID=2;Search=


My Config.pm

Code: Select all

    $Self->{'AuthSyncModule::LDAP::UserSyncMap1'} = {
        # DB -> LDAP
        UserFirstname => 'givenName',
        UserLastname  => 'sn',
        UserEmail     => 'mail',
        UserTitle     => 'title',
        UserPhone     => 'telephoneNumber',
        UserMobile    => 'mobile',
    };

The custom signature I'm testing out which was the objective here:

Code: Select all

Thanks,
 <OTRS_Agent_UserFirstname> <OTRS_Agent_UserLastname>
 <OTRS_Agent_UserTitle>
 Company Name
 ------------------------------------
 Office Phone: <OTRS_Agent_UserPhone>
 Cell:  <OTRS_Agent_UserMobile>
 Email: <OTRS_Agent_UserEmail>
 -------------------------------------

This results in everything populating correctly except for the Office Phone and Cell which just get a '-' where the numbers should be.

In the agents listing for each user, I noticed the title actually shows up in 2 separate places.. which is not a problem.. while the phone number entry is blank.


Thanks for any help!

[gears]

strangely, I just tested this out on the customer side and it works instantly for all the attributes I've tested so far including the phone numbers. Even works on my own customer account which is also an agent account. Still can't get the agent side to pull in the additional attributes though.


customers:

Code: Select all

        Map => [
      [ 'UserFirstname', 'Firstname', 'givenname', 1, 1, 'var' ],
      [ 'UserLastname', 'Lastname', 'sn', 1, 1, 'var' ],
      [ 'UserLogin', 'Login', 'sAMAccountName', 1, 1, 'var' ],
      [ 'UserEmail', 'Email', 'mail', 1, 1, 'var' ],
      [ 'UserCustomerID', 'CustomerID', 'mail', 0, 1, 'var' ],
      [ 'UserPhone', 'Phone', 'telephoneNumber', 1, 0,'var' ],
      [ 'UserMobile', 'Mobile', 'mobile', 1, 0, 'var' ],
      [ 'UserTitle', 'Title', 'title', 1, 0, 'var' ],

],

[crythias]

Code: Select all

id	int(11)	NO	PRI	NULL	auto_increment
login	varchar(200)	NO	UNI	NULL	 
pw	varchar(64)	NO	 	NULL	 
title	varchar(50)	YES	 	NULL	 
first_name	varchar(100)	NO	 	NULL	 
last_name	varchar(100)	NO	 	NULL	 
valid_id	smallint(6)	NO	MUL	NULL	 
create_time	datetime	NO	 	NULL	 
create_by	int(11)	NO	MUL	NULL	 
change_time	datetime	NO	 	NULL	 
change_by	int(11)	NO	MUL	NULL	 

User.pm

Code: Select all

    $UserObject->UserUpdate(
        UserID        => 4321,
        UserFirstname => 'Huber',
        UserLastname  => 'Manfred',
        UserLogin     => 'mhuber',
        UserPw        => 'some-pass', # not required
        UserEmail     => 'email@example.com',
        ValidID       => 1,
        ChangeUserID  => 123,
    );

UserUpdate ignores (as far as I can tell) anything not here.
UserUpdate is

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 = ? "
            . " WHERE $Self->{UserTableUserID} = ?",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{ValidID}, \$Param{ChangeUserID}, \$Param{UserID},
        ],
    );

which leaves nothing to be updated, *BUT* if you altered the table and vetted the content, you could throw it in here.

ALTER TABLE `users` ADD `phone` VARCHAR( 50 ) NOT NULL;
modify Kernel/System/User.pm UserUpdate (around line 470)

Code: Select all

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

and you might check out UserAdd around Line 353

Code: Select all

    # sql
     unless ($Param{UserPhone}) { $Param{UserPhone} ='' };
    return if !$Self->{DBObject}->Do(
        SQL => "INSERT INTO $Self->{UserTable} "
            . "(title, first_name, last_name, "
            . " $Self->{UserTableUser}, $Self->{UserTableUserPW}, "
            . " valid_id, create_time, create_by, change_time, change_by, phone)"
            . " VALUES "
            . " (?, ?, ?, ?, ?, ?, current_timestamp, ?, current_timestamp, ?)",
        Bind => [
            \$Param{UserTitle}, \$Param{UserFirstname}, \$Param{UserLastname},
            \$Param{UserLogin}, \$Param{UserPw},        \$Param{ValidID},
            \$Param{ChangeUserID}, \$Param{ChangeUserID}, \$Param{UserPhone},
        ],
    );

Now, I don't know if this will show up anywhere, but it sure will be in the database. You'll likely want to check out

Code: Select all

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

around line 192 (shown is modified)
and

Code: Select all

        $Data{ChangeTime}    = $Row[8];
        $Data{UserPhone} =$Row[9];
    }

around line 213

This is a first blush guess. Backup before you do things. This code is not tested, may break, and definitely will not survive updates. Please test and relate your findings. (oh, and you'll likely need to change dtls and places you want to actually *show* this data, but otherwise...)

[gears]

You never cease to amaze me sir.


What you've shown makes sense. I'm not quite brave enough to push this change through on the 'production' OTRS but I'm in the process of putting together a testing system and will give it a go there!

Since my setup uses the same LDAP backend for both agents and customers and the agents also exist as customers with the same user id/login and I know the customer side populates all the AD attributes now, another angle I'm curious about would be a way for OTRS tags to pull variables/attributes for the agent but treat the process as though it were pulling the info for a customer.

I was looking over the tag info @ http://faq.otrs.org/otrs/public.pl?Acti ... &ItemID=70 and for example with an agent signature like the one i mentioned earlier, some way to instead have it instead behave this way:

Code: Select all

<OTRS_Agent_UserFirstname> <OTRS_Agent_UserLastname>
<OTRS_Agent_UserTitle>
Company Name
------------------------------------
[b]Office Phone: <OTRS_AGENTASCUSTOMER_UserPhone>
Cell:  <OTRS_AGENTASCUSTOMER_UserMobile>[/b]
Email: <OTRS_Agent_UserEmail>
-------------------------------------

Given the mapping on the customer side I showed earlier, If I then put

Code: Select all

<OTRS_CUSTOMER_DATA_UserPhone> 

into an agent signature, OTRS would then successfully put the customer who posted the ticket's phone number into the signature, so maybe it's not such a leap to have this process hijacked with the agent's id instead somehow?

Thanks

[crythias]

maybe it's not such a leap to have this process hijacked with the agent's id instead somehow?

maybe the reverse. (Update User.pm to look like CustomerUser.pm)
I'd rather that than what I did, but since this was a one-off case, yeah...