Using MessageId field in Attachments table

This was hinted at by Bruce Campbell, and something that I’ve enjoyed
with in-house ticket systems, whereby the system can track e-mail articles
by Message-Id, upon failing locating a suitable Subject: or pseudo-header
RT- tag. It’s another way to stem having to roll tickets together and
such whenever the Subject: line gets munged or a new one created. Some
mailers (Outlook) apparently don’t do In-Reply-To: or preserve other headers,
and the same users who compose new e-mails rather than reply will still leave
one with the same issues, but this was something I was willing to look into
in my spare time.

As been pointed out, and observed myself, the MessageId field in the
Attachments table is available but currently unused. Am I correct in
assuming this is to eventually hold the id of incoming/outgoing
attachments/articles ?

For starters, I would add the following code segments into Attachment.pm :

    my $MessageId = $Attachment->head->get('message-id',0);
    defined($MessageId) or $MessageId = '';
    chomp($MessageId);

    $id = $self->SUPER::Create(TransactionId => $args{'TransactionId'},
                               .....
                               MessageId => $MessageId,
                               Headers => $Attachment->head->as_string,
                               Subject => $Subject,
                               .....
                              );

Then update ParseTicketId() called by rt-mailgate to include the
In-Reply-To: field to search on in the event the Subject: header isn’t
revealing. Any linking or combining functions would still work,
however, there doesn’t appear to be any linkage from the attachments
back to the ticket. What associates these together ? A subsequent search
through that table looking for the particular attachment is needed to
get the TicketId.

Seems pretty straight-forward. Is there a catch buried in any of this ?
Was someone else working on this or been discussed previously ?

thanks,
-eric
Fujitsu Processor Technologies, Inc.
(Lawrence System Integration, Inc.)

I was working on something similar to interface our system to another ticket
system. I assume that the other system will destroy the subject line and
replace it with its own (although it shouldn’t) when it creates a new ticket
based on our ticket. I looked at enhanced-mailgate and took some code from
there (ParseMessageForCommands) and modified it to look for [rtname # and
when it finds it it returns the line to send to ParseticketId Basically if
a ticket ID is not found in the subject the system will search the message
content for an ID. I still need to work on it some more to save the new ID
somewhere maybe create a new ticket and put the information I need in the
body of that one in a certain format.

This was exactly the use I’d imagined for the MessageId field, though
the actual implementation never happened. The thing to be careful of is
that users often just reply to a message and clear the subject line and body
when attemting to create a new ticket and may inadvertently get their
new ticket attached to an existing ticket.On Mon, Feb 18, 2002 at 07:04:10PM -0600, Eric Berggren wrote:

This was hinted at by Bruce Campbell, and something that I’ve enjoyed
with in-house ticket systems, whereby the system can track e-mail articles
by Message-Id, upon failing locating a suitable Subject: or pseudo-header
RT- tag. It’s another way to stem having to roll tickets together and
such whenever the Subject: line gets munged or a new one created. Some
mailers (Outlook) apparently don’t do In-Reply-To: or preserve other headers,
and the same users who compose new e-mails rather than reply will still leave
one with the same issues, but this was something I was willing to look into
in my spare time.

As been pointed out, and observed myself, the MessageId field in the
Attachments table is available but currently unused. Am I correct in
assuming this is to eventually hold the id of incoming/outgoing
attachments/articles ?

For starters, I would add the following code segments into Attachment.pm :

    my $MessageId = $Attachment->head->get('message-id',0);
    defined($MessageId) or $MessageId = '';
    chomp($MessageId);

    $id = $self->SUPER::Create(TransactionId => $args{'TransactionId'},
                               .....
                               MessageId => $MessageId,
                               Headers => $Attachment->head->as_string,
                               Subject => $Subject,
                               .....
                              );

Then update ParseTicketId() called by rt-mailgate to include the
In-Reply-To: field to search on in the event the Subject: header isn’t
revealing. Any linking or combining functions would still work,
however, there doesn’t appear to be any linkage from the attachments
back to the ticket. What associates these together ? A subsequent search
through that table looking for the particular attachment is needed to
get the TicketId.

Seems pretty straight-forward. Is there a catch buried in any of this ?
Was someone else working on this or been discussed previously ?

thanks,
-eric

Fujitsu Processor Technologies, Inc.
(Lawrence System Integration, Inc.)


rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

This was exactly the use I’d imagined for the MessageId field, though
the actual implementation never happened. The thing to be careful of is
that users often just reply to a message and clear the subject line and body
when attemting to create a new ticket and may inadvertently get their
new ticket attached to an existing ticket.

See, telepathy.

This could be avoided in 80% of cases by your merge code doing:

Find referenced Message-Ids
Compare subject lines
If subject lines match (ignoring RT tags and Re:'s):
	Merge into ticket
else
	No merge, leave as new ticket
fi

It does become a matter of per-site user education, and issuing a caution
each time you do a judgement call based on message ids (eg, if No Merge,
use the autoreply template saying ‘According to the mail headers, this
message was in reply to ticket #12345, but the automatic checks on subject
line and body seem to indicate that it is a seperate issue’).

Regards,

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

Take a look at RT’s generated message Ids. they probably don’t need to be explicitly stored
for you to get what you want out of them…On Tue, Feb 19, 2002 at 03:36:22PM -0800, Eric Berggren wrote:

This was exactly the use I’d imagined for the MessageId field, though
the actual implementation never happened. The thing to be careful of is
that users often just reply to a message and clear the subject line and body
when attemting to create a new ticket and may inadvertently get their
new ticket attached to an existing ticket.

Indeed… shame on them! Hopefully this isn’t very common.

Adding the MessageID for incoming e-mail and lookups is complete, but
now I see a more significant issue. Each e-mail action within the
Scrips generates a new message-id, one to the requestor, in additional
to watchers and other Cc’s. Which one gets stored ? Just the one to
the requestor ? Would be nice if the others articles could work too and
re-using the same message-id for the other e-mail articles wouldn’t be
proper either.

Am considering just creating another table “MessageIds” where the
fields would map MessageID -> TransactionID. This would entail needing
an additional module and methods and whatnot, but may be a cleaner
implementation overall. For existing DB’s, if table isn’t found, or not
populated, then those “backup” lookups fail reverting to dumping the
correspondence/e-mail actions from not having a TicketID.

What do you think, sirs ?

-eric

Fujitsu Processor Technologies, Inc.
(Lawrence System Integration)

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

You just want to make sure that it’s a local rt ticket id.On Tue, Feb 19, 2002 at 03:59:44PM -0800, Eric Berggren wrote:

Take a look at RT’s generated message Ids. they probably don’t need to be
explicitly stored
for you to get what you want out of them…

Ah, so it is… didn’t notice that comment. This should work nicely
then :

if ( MessageID not in Attachments table )
if ( MessageID == /^rt-/ )
get TicketID and TransactionID from MessageID
if ( ticketid in TransactionID record matches TicketID )
return ticketid
return notfound

thanks,
-eric

Fujitsu Processor Technologies, Inc.
(Lawrence System Integration)

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

with in-house ticket systems, whereby the system can track e-mail articles
by Message-Id, upon failing locating a suitable Subject: or pseudo-header

I’ve just encountered one (remote) ticket system which at first glance
appeared to be doing something funky with the message-ids, but turned out
to be rather loose with the detection of its ticket numbering in subject
lines (and not including RT’s subject line).

Having watched RT and this system declare auto-reply war on each other,
I’ve decided to implement my own suggestion in tracking message-ids.

Mainly because RT itself currently doesn’t include the generating
message-id in any correspondence, which breaks these cutsie suggestions.

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

Take a look at RT’s generated message Ids. they probably don’t need to be
explicitly stored
for you to get what you want out of them…

Ah, so it is… didn’t notice that comment. This should work nicely
then :

if ( MessageID not in Attachments table )
        if ( MessageID == /^rt-/ )
            get TicketID and TransactionID from MessageID
            if ( ticketid in TransactionID record matches TicketID )
                return ticketid
    return notfound

thanks,
-eric
Fujitsu Processor Technologies, Inc.
(Lawrence System Integration)

This was exactly the use I’d imagined for the MessageId field, though
the actual implementation never happened. The thing to be careful of is
that users often just reply to a message and clear the subject line and body
when attemting to create a new ticket and may inadvertently get their
new ticket attached to an existing ticket.

Indeed… shame on them! Hopefully this isn’t very common.

Adding the MessageID for incoming e-mail and lookups is complete, but
now I see a more significant issue. Each e-mail action within the
Scrips generates a new message-id, one to the requestor, in additional
to watchers and other Cc’s. Which one gets stored ? Just the one to
the requestor ? Would be nice if the others articles could work too and
re-using the same message-id for the other e-mail articles wouldn’t be
proper either.

Am considering just creating another table “MessageIds” where the
fields would map MessageID -> TransactionID. This would entail needing
an additional module and methods and whatnot, but may be a cleaner
implementation overall. For existing DB’s, if table isn’t found, or not
populated, then those “backup” lookups fail reverting to dumping the
correspondence/e-mail actions from not having a TicketID.

What do you think, sirs ?

-eric
Fujitsu Processor Technologies, Inc.
(Lawrence System Integration)

You just want to make sure that it’s a local rt ticket id.

You mean in terms of checking that RT::rtname is in there too,
indicating coming from the same system ? Looks like from the
comments that SetMessageID in SendEmail.pm might later change, so the
code in ParseTicketId would need to change with id. But here’s what I
presently have (I’m not a Perl OOpert, btw :slight_smile: :

EasySearch.pm:
> use DBIx::SearchBuilder::Record::Cachable;
> @ISA= qw(DBIx::SearchBuilder DBIx::SearchBuilder::Record::Cachable);


Email.pm:
>use RT::Transactions;

sub ParseTicketId {
    my $Subject = shift;
>   my $MessageId = shift;
    ....

>   # else (ticket ref not in Subject), try to lookup in Attachments table
>   elsif (defined $MessageId && $MessageId) {
>       my($attachments) = new RT::Attachment($RT::Nobody);
>       my($transactions) = new RT::Transaction($RT::Nobody);
>
>       $attachments->RT::Record::LoadByCols('MessageId', $MessageId);
>
>       # found match in Attachments table, lookup Ticket # in Trans table
>       if (defined $attachments->TransactionId &&
>               $attachments->TransactionId > 0) {
>
>               $transactions->LoadById($attachments->TransactionId);
>               return($transactions->Ticket);
>       }
>       # else, try to parse RT-generated message-id, if it looks like one
>       elsif ($MessageId =~ /^rt-(\d+)-(\d+).*\@$RT::rtname$/i) {
>                my($mticketid, $mtransactionid) = ($1, $2);
>
>               $transactions->LoadById($mtransactionid);
>               return($mticketid) if ($transactions->Ticket == $mticketid);
>       }
>   }
>   return(undef);
}

Am I using the objects in their correct form ?

regards,
-eric

As partial closure to my thread, here’s the rest of the patch for the
MessageId tracking ‘thingy’… note that this hasn’t been put through
very rigorous testing, as the project slowly moves along, but will
certainly update my findings if anything turns up…

regards,
-eric
Fujitsu Processor Technologies, Inc.
(Lawrence System Integration, Inc.)

*** lib/RT/EasySearch.pm-old Mon Dec 3 17:13:42 2001
— lib/RT/EasySearch.pm Tue Feb 19 14:39:08 2002
*** 23,29 ****

package RT::EasySearch;
use DBIx::SearchBuilder;
! @ISA= qw(DBIx::SearchBuilder);

{{{ sub _Init

sub _Init {
— 23,30 ----

package RT::EasySearch;
use DBIx::SearchBuilder;
! use DBIx::SearchBuilder::Record::Cachable;
! @ISA= qw(DBIx::SearchBuilder DBIx::SearchBuilder::Record::Cachable);

{{{ sub _Init

sub _Init {
*** lib/RT/Attachment.pm-old Tue Nov 6 15:04:14 2001
— lib/RT/Attachment.pm Tue Feb 19 12:26:23 2002
*** 66,72 ****

=head2 TransactionObj

! Returns the transaction object asscoiated with this attachment.

=cut

— 66,72 ----

=head2 TransactionObj

! Returns the transaction object associated with this attachment.

=cut

*** 123,128 ****
— 123,134 ----
defined($Subject) or $Subject = ‘’;
chomp($Subject);

  • #Get the message-id
    
  • my $MessageId = $Attachment->head->get('message-id',0);
    
  • defined($MessageId) or $MessageId = '';
    
  • chomp($MessageId);
    
  • $MessageId = $1 if ( $MessageId =~ /<(.+)>/ );
    #Get the filename
    my $Filename = $Attachment->head->recommended_filename;
    

*** 131,136 ****
— 137,143 ----
Parent => 0,
ContentType => $Attachment->mime_type,
Headers => $Attachment->head->as_string,

  •   		   MessageId => $MessageId,
      		   Subject => $Subject,
      		   
      		  );
    

*** 201,206 ****
— 208,214 ----
Parent => $args{‘Parent’},
Content => $Body,
Headers => $Attachment->head->as_string,

  •   		      MessageId => $MessageId,
      		      Subject => $Subject,
      		      Filename => $Filename,
      		     );
    

*** lib/RT/EasySearch.pm-old Mon Dec 3 17:13:42 2001
— lib/RT/EasySearch.pm Tue Feb 19 14:39:08 2002
*** 23,29 ****

package RT::EasySearch;
use DBIx::SearchBuilder;
! @ISA= qw(DBIx::SearchBuilder);

{{{ sub _Init

sub _Init {
— 23,30 ----

package RT::EasySearch;
use DBIx::SearchBuilder;
! use DBIx::SearchBuilder::Record::Cachable;
! @ISA= qw(DBIx::SearchBuilder DBIx::SearchBuilder::Record::Cachable);

{{{ sub _Init

sub _Init {
*** lib/RT/Interface/Email.pm-old Tue Nov 6 15:04:52 2001
— lib/RT/Interface/Email.pm Tue Feb 19 16:23:31 2002
*** 6,11 ****
— 6,12 ----
use strict;
use Mail::Address;
use MIME::Entity;

  • use RT::Transactions;

    BEGIN {
    use Exporter ();
    *** 265,280 ****

    sub ParseTicketId {
    my $Subject = shift;
    my ($Id);
    !
    if ($Subject =~ s/[$RT::rtname #(\d+)]//i) {
    $Id = $1;
    $RT::Logger->debug(“Found a ticket ID. It’s $Id”);
    return($Id);
    }
    ! else {
    ! return(undef);
    }
    }

    }}}

— 266,304 ----

sub ParseTicketId {
my $Subject = shift;

  • my $MessageId = shift;
    my ($Id);
    

!
! # see if there’s a ticket reference in the subject
if ($Subject =~ s/[$RT::rtname #(\d+)]//i) {
$Id = $1;
$RT::Logger->debug(“Found a ticket ID. It’s $Id”);
return($Id);
}
!
! # else, try to lookup in Attachments table (from incoming e-mail)
! elsif (defined $MessageId && $MessageId) {
! my($attachments) = new RT::Attachment($RT::Nobody);
! my($transactions) = new RT::Transaction($RT::Nobody);
!
! $attachments->RT::Record::LoadByCols(‘MessageId’, $MessageId);
!
! # found match in Attachments table, lookup Ticket # in Transactions tbl
! if (defined $attachments->TransactionId &&
! $attachments->TransactionId > 0) {
!
! $transactions->LoadById($attachments->TransactionId);
! return($transactions->Ticket);
! }
! # else, try to parse RT-generated message-id, if it looks like one
! elsif ($MessageId =~ /^rt-(\d+)-(\d+).*@$RT::rtname$/i) {
! my($mticketid, $mtransactionid) = ($1, $2);
!
! $transactions->LoadById($mtransactionid);
! return($mticketid) if ($transactions->Ticket == $mticketid);
! }
}

  • return(undef);
    
    }

    }}}

*** /u/ericb/projects/reqtracker-2.0.11/bin/rt-mailgate Thu Nov 29 00:42:41 2001
— bin/rt-mailgate Tue Feb 19 16:51:08 2002
*** 109,120 ****
my $MessageId = $head->get(‘Message-Id’) ||
"<no-message-id-".time.rand(2000)."@.$RT::rtname>";

#Pull apart the subject line
$Subject = $head->get(‘Subject’) || “[no subject]”;
chomp $Subject;

Get the ticket ID unless it’s already set

! $TicketId = ParseTicketId($Subject) unless ($TicketId);

#Set up a queue object
my $QueueObj = RT::Queue->new($CurrentUser);
— 109,123 ----
my $MessageId = $head->get(‘Message-Id’) ||
"<no-message-id-".time.rand(2000)."@.$RT::rtname>";

  • my $ReplMessageId = $head->get(‘In-Reply-To’);

  • $ReplMessageId = $1 if ( $ReplMessageId =~ /<(.+)>/ );
    #Pull apart the subject line
    $Subject = $head->get(‘Subject’) || “[no subject]”;
    chomp $Subject;

    Get the ticket ID unless it’s already set

! $TicketId = ParseTicketId($Subject,$ReplMessageId) unless ($TicketId);

#Set up a queue object
my $QueueObj = RT::Queue->new($CurrentUser);