Inserting tickets directly to database

Is there any good way to do this. I have a set of
about 4000 tickets from our old text based simple system.

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

./Jonas Lindvall

Use a perl script or even php

The table for ticket headers and details is “Tickets”

And for the content(message body and attachments) is “Attachments”.

Actually for me i play with that database alot…I have a system where
tickets are resolved automatically and sends a confirmation mail but the
logic is just
mysql> update Tickets set Status=‘Resolved’ where Id=$TicketId;
assinging Tickets owners and owners sent mail,
mysql> update Tickets set Owner=$UserId where id=$Tickeid;

And so on…just get fluent with your scripting languages but don’t delete
some initial database info like the system group, the system users, etc. If
you do, then go to /path/to/rt/etc/initialdata and do the necessary.

Guys at work also refused to always login to Our RT so they just send mail
(and tickets are created), then resolve by also sendiing mail.-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com]On Behalf Of Jonas Lindvall
(AllTele)
Sent: Tuesday, June 29, 2004 11:45 AM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] Inserting tickets directly to database

Is there any good way to do this. I have a set of
about 4000 tickets from our old text based simple system.

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

./Jonas Lindvall

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

The table for ticket headers and details is “Tickets”

While you can directly edit the database, it’s not recommended. Using
the RT api (perl libraries) or writing a wrapper around the command
line client will be less error prone.

seph

Is there any way to insert a ticket using the command line libary and giving
it a certain id number??

./JonasFrom: “seph” seph@directionless.org
To: “Wilson Abigaba” wilson@cfi.co.ug
Cc: “Jonas Lindvall (AllTele)” jonas.lindvall@alltele.se;
rt-users@lists.bestpractical.com
Sent: Tuesday, June 29, 2004 1:21 PM
Subject: Re: Inserting tickets directly to database

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

The table for ticket headers and details is “Tickets”

While you can directly edit the database, it’s not recommended. Using
the RT api (perl libraries) or writing a wrapper around the command
line client will be less error prone.

seph

Is there any good way to do this. I have a set of
about 4000 tickets from our old text based simple system.

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

Surely the easiest way would be to disable any auto-responding Scrips,
then write a simple script to loop through each ticket and;

  1. Extract the ticket info from the old data source
  2. Compile an email containing the ticket body & attachments, and set
    the From: address to that of the original sender.
  3. Insert a header containing the ticket ID of the original ticket
    (so you have a way of mapping the old ticket to the new ticket)
  4. Send the email to the destination queue address.

That way you wouldn’t need to worry about adding the correct info to
each RT table – each imported ticket would be handled exactly like a
new ticket.

The only lost info might be the creation date of each ticket, though you
could easily loop through each ticket again and correct the Created date
of each transaction associated with the ticket.

Peter

signature.asc (189 Bytes)

Is there any good way to do this. I have a set of
about 4000 tickets from our old text based simple system.

And importing thoose to RT is probably pretty
simple if i just could figure out how to add a
ticket manually, anyone can help??

Surely the easiest way would be to disable any auto-responding Scrips,
then write a simple script to loop through each ticket and;

  1. Extract the ticket info from the old data source
  2. Compile an email containing the ticket body & attachments, and set
    the From: address to that of the original sender.
  3. Insert a header containing the ticket ID of the original ticket
    (so you have a way of mapping the old ticket to the new ticket)
  4. Send the email to the destination queue address.

That way you wouldn’t need to worry about adding the correct info to
each RT table – each imported ticket would be handled exactly like a
new ticket.

This is pretty much what I did when I wanted to import a bunch of (2k5) emails
into RT - wrote a script to take them from a mail dir, thread them
(message-id, ref), sort by date and piped them through rt-mailgate.
I also enabled the enhanced-mailgate functionality, so I could resolve, set
owner etc via following emails.
Worked well, but just for reference, it took around 10 hours to feed them all
in - slow machine, but even so…
I’m pretty new to perl, so they’re messy, but still, if anyone’s interested my
scripts, they’re welcome to them.

As a side, from time to time people ask if anyone uses the enhanced-mailgate.
Well, yes it’s insecure, but I don’t mind at this point, and I couldn’t wait
for RT3.2++, so I set it up. It seems to work fine… if anyone wants my
diffs, yell.

The only lost info might be the creation date of each ticket, though you
could easily loop through each ticket again and correct the Created date
of each transaction associated with the ticket.
Good point, didn’t think of this - Would you do this via the cli, or would you
use the RT api, and maybe take advantage of ‘RecordTransaction=0’…?
Cerion

The only lost info might be the creation date of each ticket, though you
could easily loop through each ticket again and correct the Created date
of each transaction associated with the ticket.

Good point, didn’t think of this - Would you do this via the cli, or would you
use the RT api, and maybe take advantage of ‘RecordTransaction=0’…?

If I were doing this as a once-off type thing, I’d write a script that
accesses the DB directly (because I haven’t played with the RT API).
The script would need to do the following;

  1. Grab the TicketId & create date of the ticket from the original data
    source.

  2. Perform a query on the Attachments table for something like;
    select TransactionId from Attachments where (Headers like
    ’%\nX-Orig-TicketId: \n%’);

    (this could be a bit slow on a large RT db)

  3. Use the TransactionId to get the new TicketId;
    select Ticket from Transactions where (id = );

  4. Update the Created date of the transactions;
    update Transactions set Created= where
    (Ticket = ‘New TicketId’);

Someone with RT API experience could probably come up with a safer
method for achieving this, assuming the API lets you modify the create
date of transactions associated with a ticket.

Peter

signature.asc (189 Bytes)