[Solved] How do I set up external backend?

[Jeanette]

I'm feeling a little lost and am not quite understanding what I'm supposed to change in the example in the manual. A little help please?

This is the example file:

# CustomerUser (customer database backend and settings)
$Self->{CustomerUser} = {
Name => 'Database Datasource',
Module => 'Kernel::System::CustomerUser::DB',
Params => {
# if you want to use an external database, add the required settings
# DSN => 'DBI:odbc:yourdsn',
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
# User => '',
# Password => '',
Table => 'customer_user',
# 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 => 'login',

# customer #
CustomerID => 'customer_id',
CustomerValid => 'valid_id',
CustomerUserListFields => ['first_name', 'last_name', 'email'],
CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['email'],
CustomerUserNameFields => ['title','first_name','last_name'],
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
[ 'UserTitle', 'Title', 'title', 1, 0, 'var', '', 0 ],
[ 'UserFirstname', 'Firstname', 'first_name', 1, 1, 'var', '', 0 ],
[ 'UserLastname', 'Lastname', 'last_name', 1, 1, 'var', '', 0 ],
[ 'UserLogin', 'Username', 'login', 1, 1, 'var', '', 0 ],
[ 'UserPassword', 'Password', 'pw', 0, 0, 'var', '', 0 ],
[ 'UserEmail', 'Email', 'email', 1, 1, 'var', '', 0 ],

# [ 'UserEmail', 'Email', 'email', 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', 'phone', 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.',
},
},
};

I pasted it into the config.pm file.

But what do I need to change?

Just the login info and the mappings?

Can I exclude any of the fields? (ie. only use email, customer names and customer id?)

[crythias]

What do you want to happen? (Customers or Agents?)
What is not happening?

lines with # are comments and not relevant.

http://doc.otrs.org/3.2/en/html/external-backends.html

[crythias]

But what do I need to change?

What do you want to get? From where?

Just the login info and the mappings?

probably.

Can I exclude any of the fields? (ie. only use email, customer names and customer id?)

Code: Select all

# note: Login, Email and CustomerID needed!

Yes.

[Jeanette]

Sorry.

I want to get customer info from an external database into OTRS.

For the last part, do i just comment out the mappings I don't want?

[Jeanette]

I should probably note that I don't really know Perl.

[crythias]

I should probably note that I don't really know Perl.

That's okay, Perl doesn't really know you, either. You should introduce yourself. :)

For the last part, do i just comment out the mappings I don't want?

#yes.

[Jeanette]

I'm still lost though.

So:

# DSN => 'DBI:odbc:yourdsn',
# DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
# User => '',
# Password => '',

Obviously I just fill inn "yourdsn", "customerdb" and "customerdbhost" fields?

# customer unique id
CustomerKey => 'login',

customerID (company number) in the database or unique id for the customer (ie email address)?

CustomerID => 'customer_id',
CustomerValid => 'valid_id',
CustomerUserListFields => ['first_name', 'last_name', 'email'],
CustomerUserSearchFields => ['login', 'last_name', 'customer_id'],
CustomerUserSearchPrefix => '',
CustomerUserSearchSuffix => '*',
CustomerUserSearchListLimit => 250,
CustomerUserPostMasterSearchFields => ['email'],
CustomerUserNameFields => ['title','first_name','last_name'],

Do I change the fields to correspond with the name of the database fields? What's "valid_id'?

For the mappings I change the third field to the database field name? THe one that's "storage"?

# [ 'UserEmail', 'Email', 'email', 1, 1, 'var', '$Env{"CGIHandle"}?Action=AgentTicketCompose&ResponseID=1&TicketID=$Data{"TicketID"}&ArticleID=$Data{"ArticleID"}', 0 ],
# [ 'UserCustomerIDs', 'CustomerIDs', 'customer_ids', 1, 0, 'var', '', 0 ],

What are these?

Do I need the default selections if I don't use titles?

[crythias]

Obviously I just fill inn "yourdsn", "customerdb" and "customerdbhost" fields?

If they're relevant and actually work, yes, plus remove # as it won't work until you do because it's commented out.

customerID (company number) in the database or unique id for the customer (ie email address)?

no, login is login. (username). The value representing the concept of who the customer is, individually, unique, as it says. It could be email, if you want or need a customer to log in with her full email address. It can be anything that you use to uniquely identify the customer in your external database.

Do I change the fields to correspond with the name of the database fields?

yes.

What's "valid_id'?

A field indicating that the record is valid, active, etc. Assuming that this field evaluates to "True" in some manner (1, true, non zero>0) it means this record (customer) is valid/active and can be used for new tickets.

For the mappings I change the third field to the database field name? THe one that's "storage"?

yes

What are these?

They are commented examples. Theoretically, you'll have more of them if you comment yours. The email one provides an example how to hyperlink the email address. The CustomerIDs provides an example for how to provide multiple of CustomerID.

Do I need the default selections if I don't use titles?

No. Default Selections is irrelevant for external databases, especially if the source is read-only for OTRS.

I recommend you read my help link in my signature.

[Jeanette]

Thank you!

[Jeanette]

One more quick question.

Our customers won't be logging into OTRS, just sending in emails. Do I still need to fill inn the CustomerKey and the UserLogin (under map)?

# # generate auto logins
# AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',

Should I be using this?

[Jeanette]

Got it working

Is it possible to do the same for Customer Companies? Would love to be able to use the same backend (different table, same server & database) to sync customer companies.

[crythias]

Yes. Copy the relevant section from Defaults.pm to Config.pm and change.

[crythias]

One more quick question.

Our customers won't be logging into OTRS, just sending in emails. Do I still need to fill inn the CustomerKey and the UserLogin (under map)?

Yes. Email needs to still have that info.

# # generate auto logins
# AutoLoginCreation => 0,
# AutoLoginCreationPrefix => 'auto',


Should I be using this?

Up to you but I say no.