Where is my message content?

RT Users
1

Hey…

Ive been given the task (from up above...) to write a script that regularly fetches tickets/emails out of a certain queue, forward them to a non-RT address, and mark the ticket as resolved with a note saying that its been forwarded
appropriately.

So far I`m fine with an external DBI/DBD::MySQL perl script that grabs the
ticket number of any new or open ticket in a given queue. I now need to reliably
grab the message headers and content out of the RT database. Honestly, I like RT
alot but I the database looks a bit messy to_me. The most unique columns that
I could find to join “Ticket” and “Attachments” on (so that I can grab the
attachments.headers and attachments.content with only the ticket number) are the
“Subject” and “Created” columns which is very very inefficient. The basic idea
is kinda like this:

$ticketid = “select ID from Tickets where Queue = ‘whatever’ and (status = ‘new’
|| status = ‘open’);”;
$email = “select Headers,Content from Tickets,Attachments where Tickets.Subject
= Attachments.Subject and Tickets.Created = Attachments.Created and Tickets.id =
‘$ticketid’;”;

Can anyone think of a better way to do this? Grab an new/open ticket out of a
particular queue, mark it resolved and forward along the attachments.headers and
attachment.content? Also, ive noticed a few instances where the message body isnt in attachments.content, would somebody please enlighten me as to where my
ticket content is and exactly how everything is tied together so that RT can
display everything so impeccably yet I can`t get this seemingly simple script to
function correctly?

As always, any insight at all would be greatly appreciated.

Thank You.

timuel

2

Ive been given the task (from up above...) to write a script that regularly fetches tickets/emails out of a certain queue, forward them to a non-RT address, and mark the ticket as resolved with a note saying that its been forwarded
appropriately.

$ticketid = “select ID from Tickets where Queue = ‘whatever’ and (status = ‘new’
|| status = ‘open’);”;
$email = “select Headers,Content from Tickets,Attachments where Tickets.Subject
= Attachments.Subject and Tickets.Created = Attachments.Created and Tickets.id =
‘$ticketid’;”;

‘ugh’.

Why not use the public interfaces in RT::Ticket to simply do:

my $tickets = RT::Tickets->new( $RT::CurrentUser );
$tickets->UnLimit;
# Lets pretend we only want 'new' tickets.
$tickets->LimitStatus( VALUE => 'new',
			OPERATOR => '=', );

# In queue 'smaland'
$tickets->LimitQueue( VALUE => 'smaland',
			OPERATOR => '=', );

# That haven't been picked up for ages.
my $tmpdate = RT::Date->new( $RT::CurrentUser );
# Not sure if RT::Date will parse this.
$tmpdate->Set( Format => 'Unknown', Value => '5 hours ago' );
$tickets->LimitCreated( VALUE => $tmpdate->ISO,
			OPERATOR => '>=' );

# Lets go and see what we've got.
while( my $ticket = $tickets->Next ){
	my $headers = $ticket->Transactions->First->Message->Headers();

	# start generating message
	while( my $attachment = $ticket->Transactions->Next ){
		# attach stuff to the message
	}

	# send the message to the other address.

	# resolve the ticket.
	$ticket->Resolve();

	# remember to tell the original parent^Wrequestor that
	# their child^Wticket has been shifted elsewhere, although
	# I'd do a Scrip for this.

}

( Note, this is untested. )

Or, you could bundle this in a thing for Escalator.

Regards,

                         Bruce Campbell                            RIPE
               Systems/Network Engineer                             NCC
             www.ripe.net - PGP562C8B1B             Operations/Security