Query External DB

Moderator: crythias

Locked
cgargent
Znuny newbie
Posts: 4
Joined: 11 Jul 2014, 15:23
Znuny Version: 3.3.8

Query External DB

Post by cgargent »

Hi,

I have a mail filter that queries the ticket subject and body looking for a serial number. If it finds one it sets a custom field with that value. I now want to take this field and query a SQL DB on another server, and return a boolean value on wether it is in warranty/support etc? My searches so far have drawn a blank so any help or guidance in the right direction would be appreciated


Many Thanks
crythias
Moderator
Posts: 10170
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Query External DB

Post by crythias »

what would you do with the result?
cgargent wrote:I have a mail filter that queries the ticket subject and body looking for a serial number. If it finds one it sets a custom field with that value
Is this postmasterfilter or external before it reaches OTRS?

(If it's external, then maybe you could have that filter do .. something...)

If it's PostMaster, then your request probably requires some sort of Ticket Event.

That's probably what I'd do: Program a ticket event to query the external DB and set another DynamicField.

"but how?"
An example of how to *write* to a database is given here.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
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
cgargent
Znuny newbie
Posts: 4
Joined: 11 Jul 2014, 15:23
Znuny Version: 3.3.8

Re: Query External DB

Post by cgargent »

The postmaster filter does a regex search on the subject field and sets a dynamic field with the value of the match. Now I want to use this field to query an external DB and process the ticket into different queues based on the response of the query.

I looked at the link given, so I guess I need 2 files an xml and pm file to make this work? The xml being just some config stuff, and the pm being a perl script that does some stuff. Will I be able to get it to query an external database with using a variable from the OTRS DB and then subsequently set a field in the ticket based on the reply?

Also how do I execute the script?
crythias
Moderator
Posts: 10170
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: Query External DB

Post by crythias »

cgargent wrote:Will I be able to get it to query an external database with using a variable from the OTRS DB and then subsequently set a field in the ticket based on the reply?
That's the goal. Whether you are able to program this is a different issue.
cgargent wrote:Also how do I execute the script?
It's an event that will trigger when something appropriate happens (automatically).
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
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
cgargent
Znuny newbie
Posts: 4
Joined: 11 Jul 2014, 15:23
Znuny Version: 3.3.8

Re: Query External DB

Post by cgargent »

I am following the HelloWorld example in the docs but I dont know where to put the file nor how to select it as an action on an event. Programming the module looks easier than implementing it OTRS!

I have got a button showing now in the top menu bar, but it doesnt do anything, besides I dont really want a button, I want an external query.
reneeb
Znuny guru
Posts: 5018
Joined: 13 Mar 2011, 09:54
Znuny Version: 6.0.x
Real Name: Renée Bäcker
Company: Perl-Services.de
Contact:

Re: Query External DB

Post by reneeb »

Perl / Znuny development: http://perl-services.de
Free Znuny add ons from the community: http://opar.perl-services.de
Commercial add ons: http://feature-addons.de
cgargent
Znuny newbie
Posts: 4
Joined: 11 Jul 2014, 15:23
Znuny Version: 3.3.8

Re: Query External DB

Post by cgargent »

I am not sure that is what I want, I have got a filter to extract a serial number from a field in the ticket, I now want to run a SQL query on an external database using that number. The example given appears to query an internal field, is it possible to modify this module so that it queries a database and updates a ticket field with the result of the query?
Locked