Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Support Database.

by Anonymous Monk
on Sep 03, 2002 at 17:28 UTC ( #194823=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,

I have a question for you wise people, which is probably going to be either easy as pie, or bloomin' hard.

I am trying to set up a support database in mysql that, when an e-mail is sent to support@foo.com (i'm using postfix), will take the e-mail and put it into a database.

This is so support calls canautomaticaly be logged on to the system, given a call log id etc...

Any pointers, ideas or pre-written scripts?

Cheers

Replies are listed 'Best First'.
Re: Support Database.
by krisahoch (Deacon) on Sep 03, 2002 at 18:26 UTC

    You may want to take a look a Request Tracker. I believe that it already does what you are after. Here is an excerpt from the the Features section of the website.


    Features

    RT is a robust ticketing system designed for the small-to-medium sized enterprise. It can be used for customer support, bugtracking, trouble ticketing, or any other purpose for which a group of folks need to keep track of a list of tasks. It features a number of technologies designed to make it easy to keep track of a large number of tasks, issues and projects:

    • Web, email and commandline interfaces. The web interface is designed to work well with all popular browsers, including console-based browsers.
    • Web interface for each end-user to quickly check on the status of his open tickets.
    • Data is stored in an SQL database which you can audit and report on using standard tools.
    • Granular access control system which allows users to do their work but also protects the system.
    • Flexible 'keywords' system which allows a local site to keep track of ticket metadata in the most sensible way.
    • Extensible linking system which allows you to tie tickets to each other and to external databases. (RT supports dependencies, parent-child relationships and generic relationships)
    • Clean object oriented design makes it a breeze to build new tools which work with RT's tickets, queues, users and links.
    • Open 'Scrips' system allows sites to drop in custom business logic which persists transparently across system upgrades.
    • Doesn't cost anything to set up and use. (Available under the terms of the GNU GPL)


    Being someone who has tried to build a support desk, I would strongly recommend using free stuff that already exists. If you still want to do it yourself, then have a look at what other people have done.

    Kristofer A. Hoch

Re: Support Database.
by blokhead (Monsignor) on Sep 03, 2002 at 17:50 UTC
    I've never used it, but it looks like you can pipe mail to a command using .forward files in postfix. So a very easily manageable solution might be like this:
    /home/support/.forward: ----------------------- |/home/support/myscript.pl /home/support/myscript.pl: -------------------------- #!/usr/bin/perl while (<STDIN>) { # parse email headers & body } # add appropriate entries to DB # possibly send a reply giving the ticket number
    I think the pipe-to features of postfix looks really promising. Of course, this is not much of a Perl solution, as I've given you a lot of blanks to fill in ;) .. I assume you have had experience with DBI in Perl. If not, there are a lot of good resources around, just search the monastery and you'll come up with a lot. Anyway, this feature of postfix seems like a good way to get the mail to the Perl. Now you just get the data from the Perl to the DB ;)

    <tangent> Hey other monks.. A thought just occured to me.. would it be useful to have a mod_perl-ish set of hooks in postfix (or your favorite MTA), so that you can add PerlHandlers here and there for various things -- accepting email, filtering incoming and outgoing, accepting or rejecting relays... Maybe there is something already like this. Just a random thought. </tangent>

    Good luck, it sounds like a fun project

    blokhead

      You might want to look at Mail::Audit which does a lot of that stuff for you.

      gav^

Re: Support Database.
by kschwab (Vicar) on Sep 03, 2002 at 18:28 UTC
    If your needs are simple, there's perldesk.

    It does handle the email-to-ticket functionality you asked for.

Re: Support Database.
by spartacus9 (Beadle) on Sep 04, 2002 at 03:24 UTC
    Yes, Request Tracker (affectionately known as RT) is just what you're looking for. A support ticket system is exactly what this system was written to do. I originally had it running on a system with Sendmail and have recently moved it to another server that runs Qmail. Both have worked flawlessly. The documentation provided with RT leaves a bit to be desired, but this is basically what you will need in order to build it:
    • Apache, built as a DSO preferably
    • The RT source (from www.fsck.com)
    • HTML::Template perl module
    • MySql server
    That should get you going. While the documentation is a little sketchy, the e-mail user groups for RT are pretty useful. Go to www.fsck.com for the downloads. If you have difficulty with the install / configure, I would be happy to help. Good luck.
Re: Support Database.
by Anonymous Monk on Sep 03, 2002 at 17:49 UTC
    Maybe mudpie. What OS ? (this really has less to do with perl, and more with intercepting the email. Basically, you need to look into your mailserver on how to intercept the email. Search CPAN for Mail MIME and DBI for how to do the rest ~ you can alsos earch for MYSQL)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://194823]
Approved by krisahoch
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2020-10-28 10:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (260 votes). Check out past polls.

    Notices?