Connecting RT to IBM iSeries and/or MSSQL databases?

Hello list,
Another day, another proposed RT modification for me to investigate. Our
company has six remote representatives who each have a few hundred
customers they visit in person. The customer support team works locally,
and is the group who actually enters all the tickets. We want reps to be
able to look at tickets related to their customers, but we want to do that
in a dashboard and not as a CC. Since each rep has hundreds of customers,
and since the customer base for a rep can change a lot, saved searches
aren’t an ideal solution. It is also too error-prone and time-consuming to
have staff enter a customer rep ID for every ticket, as they already have
to enter an order number and other custom information. The good news is
that, in databases we already have, the order number can lead from the
order, to the customer, to the representative.

My thought is to have a cron job or a custom script that can take the order
number, look up the rep on our iSeries or MSSQL databases, and fill in a
custom field with the username of the relevant representative. I like the
idea of a script, so that users can see the change immediately, but a cron
job would probably be okay too. This would then let us use a saved search
that just finds tickets where the rep name equals the name of the user
whose dashboard it is, instead of having hundreds of ‘or’ statements trying
to find all possible customer numbers.

  • Can Perl code in scripts talk to an external database as I’ve described?

  • Can the RT cron tool do this job? If so, what might the workflow look
    like? I haven’t yet looked very deeply into this tool’s abilities or syntax.

Thanks for any thoughts. Debian 8.6, RT4.2.8 (hopefully going to be 4.4
sometime soon), MySQL database for RT. No, the server doesn’t yet have the
drivers for these databases, but my question is about integrating the
information into RT and not about how Debian talks to DB2 or Server.

Alex Hall
Automatic Distributors, IT department
ahall@autodist.com

Hi Alex,

Sounds like you may be looking for the external custom fields
functionality, please
see the link below for details:

https://docs.bestpractical.com/rt/4.4.1/extending/external_custom_fields.html

Best Regards

MartinOn 2016-10-10 15:15, Alex Hall wrote:

Hello list,
Another day, another proposed RT modification for me to investigate.
Our company has six remote representatives who each have a few hundred
customers they visit in person. The customer support team works
locally, and is the group who actually enters all the tickets. We want
reps to be able to look at tickets related to their customers, but we
want to do that in a dashboard and not as a CC. Since each rep has
hundreds of customers, and since the customer base for a rep can
change a lot, saved searches aren’t an ideal solution. It is also too
error-prone and time-consuming to have staff enter a customer rep ID
for every ticket, as they already have to enter an order number and
other custom information. The good news is that, in databases we
already have, the order number can lead from the order, to the
customer, to the representative.

My thought is to have a cron job or a custom script that can take the
order number, look up the rep on our iSeries or MSSQL databases, and
fill in a custom field with the username of the relevant
representative. I like the idea of a script, so that users can see the
change immediately, but a cron job would probably be okay too. This
would then let us use a saved search that just finds tickets where the
rep name equals the name of the user whose dashboard it is, instead of
having hundreds of ‘or’ statements trying to find all possible
customer numbers.

  • Can Perl code in scripts talk to an external database as I’ve
    described?

  • Can the RT cron tool do this job? If so, what might the workflow
    look like? I haven’t yet looked very deeply into this tool’s abilities
    or syntax.

Thanks for any thoughts. Debian 8.6, RT4.2.8 (hopefully going to be
4.4 sometime soon), MySQL database for RT. No, the server doesn’t yet
have the drivers for these databases, but my question is about
integrating the information into RT and not about how Debian talks to
DB2 or Server.

Alex Hall
Automatic Distributors, IT department
ahall@autodist.com


RT 4.4 and RTIR training sessions, and a new workshop day!
https://bestpractical.com/training

  • Boston - October 24-26
  • Los Angeles - Q1 2017

Le 10/10/2016 � 17:15, Alex Hall a �crit :

  • Can Perl code in scripts talk to an external database as I’ve described?

yes, scrips run any kind of perl code, you can include needed modules.
There is perl libs to talk to almost any kind of databases, even
proprietary ones :wink: A quick internet search on perl + MSSQL will give
you examples of code.

we have here scrips that get and write informations to third party
softwares using DB access, ldap, soap, xmlrpc, depending on the needs.

  • Can the RT cron tool do this job? If so, what might the workflow
    look like? I haven’t yet looked very deeply into this tool’s abilities
    or syntax.

it can if the Action used is one that you self create for your needs (in
rt/local/lib/RT/Actions)