Where are attachments stored?

RT Users
1

I need to import attachments into our RT from our legacy system. How are
attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org

2

Aaron, the attachments are stored in RT’s database in a table called
‘Attachments’. There is a field called ‘Content’ that holds the ones and
zeros that make up the attachment. I suspect that importing files into RT
as ticket attachments will be a non-trivial exercise.

Regards,
Gene

At 11:02 AM 4/10/2008, Aaron Sallade wrote:

I need to import attachments into our RT from our legacy system. How are
attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
mailto:asallade@ptsowa.orgasallade@ptsowa.org

Gene LeDuc, GSEC
Security Analyst
San Diego State University

3

Gene, Aaron,

Not only that, but the content is stored in various ways, depending on

the CONTENTTYPE field (text, message, excell, etc.) AND if you have
ORACLE, CONTENTCODING is not supported (at least up to 9). I built my
own Data Dictionary on the ORACLE version of the RT DataBase so I would
know what to do if I was gonna update the DB via SQL or something.
Fooling around with creating or deleting records is more than a bit
tricky external to RT tools. Like I didn’t know that ALL users have a
group id record JUST for their individual user ID. It is usually the
User ID plus 1. Anyway, like Gene said, not a trivial exercise.

Kenn
LBNLOn 4/10/2008 11:45 AM, Gene LeDuc wrote:

Aaron, the attachments are stored in RT’s database in a table called
‘Attachments’. There is a field called ‘Content’ that holds the ones
and zeros that make up the attachment. I suspect that importing files
into RT as ticket attachments will be a non-trivial exercise.

Regards,
Gene

At 11:02 AM 4/10/2008, Aaron Sallade wrote:

I need to import attachments into our RT from our legacy system. How
are attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
/“Shared Technology for Community Health”
/(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org mailto:asallade@ptsowa.org

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

4

Hmm,

I wrote a script that can email the attachments to RT with the proper
Ticked ID format in the header, my concern is :

We just finished our import of data from the legacy system and have
about 15,000 tickets that are resolved, but do not have their
attachments added. If I add them via email, it will open the ticket.

If I can find out how to not change the status or the resolved date by
commenting out a portion of the code that handles ticket replies, then
it could work.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.orgFrom: Kenneth Crocker [mailto:KFCrocker@lbl.gov]
Sent: Thursday, April 10, 2008 12:45 PM
To: Gene LeDuc
Cc: Aaron Sallade; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Where are attachments stored?

Gene, Aaron,

Not only that, but the content is stored in various ways,

depending on
the CONTENTTYPE field (text, message, excell, etc.) AND if you have
ORACLE, CONTENTCODING is not supported (at least up to 9). I built my
own Data Dictionary on the ORACLE version of the RT DataBase so I would
know what to do if I was gonna update the DB via SQL or something.
Fooling around with creating or deleting records is more than a bit
tricky external to RT tools. Like I didn’t know that ALL users have a
group id record JUST for their individual user ID. It is usually the
User ID plus 1. Anyway, like Gene said, not a trivial exercise.

Kenn
LBNL

5

Hi Aaron,

Good call on the import method - that way RT gets to do all the grunt
work. Consider creating a scrip that fires when a ticket is reopened
(status changes from resolved to open) and then (quietly) reset it to
resolved. Just enable the scrip when your external script is going to be
e-mailing attachments to RT. This ought to be pretty straightforward. I
would do the status reset using
$self->TicketObj->_Set(Field=>‘Status’, Value=>‘resolved’,
RecordTransaction=>0);
rather than
$self->TicketObj->SetStatus(‘resolved’);
to avoid triggering any “On Resolve” scrips.

Regards,
Gene

At 01:05 PM 4/10/2008, Aaron Sallade wrote:

Hmm,

I wrote a script that can email the attachments to RT with the proper
Ticked ID format in the header, my concern is :

We just finished our import of data from the legacy system and have
about 15,000 tickets that are resolved, but do not have their
attachments added. If I add them via email, it will open the ticket.

If I can find out how to not change the status or the resolved date by
commenting out a portion of the code that handles ticket replies, then
it could work.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org

-----Original Message-----
From: Kenneth Crocker [mailto:KFCrocker@lbl.gov]
Sent: Thursday, April 10, 2008 12:45 PM
To: Gene LeDuc
Cc: Aaron Sallade; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Where are attachments stored?

Gene, Aaron,

    Not only that, but the content is stored in various ways,

depending on
the CONTENTTYPE field (text, message, excell, etc.) AND if you have
ORACLE, CONTENTCODING is not supported (at least up to 9). I built my
own Data Dictionary on the ORACLE version of the RT DataBase so I would
know what to do if I was gonna update the DB via SQL or something.
Fooling around with creating or deleting records is more than a bit
tricky external to RT tools. Like I didn’t know that ALL users have a
group id record JUST for their individual user ID. It is usually the
User ID plus 1. Anyway, like Gene said, not a trivial exercise.

Kenn
LBNL

Aaron, the attachments are stored in RT’s database in a table called
‘Attachments’. There is a field called ‘Content’ that holds the ones
and zeros that make up the attachment. I suspect that importing files

into RT as ticket attachments will be a non-trivial exercise.

Regards,
Gene

At 11:02 AM 4/10/2008, Aaron Sallade wrote:

I need to import attachments into our RT from our legacy system. How
are attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
/“Shared Technology for Community Health”
/(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org mailto:asallade@ptsowa.org

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Gene LeDuc, GSEC
Security Analyst
San Diego State University

6

My concern on that is the timestamp for Resolved and how it impacts
reporting :frowning:

I am going to try to disable the scrips and see if it works.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.orgFrom: Gene LeDuc [mailto:gleduc@mail.sdsu.edu]
Sent: Thursday, April 10, 2008 1:30 PM
To: Aaron Sallade
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] Where are attachments stored?

Hi Aaron,

Good call on the import method - that way RT gets to do all the grunt
work. Consider creating a scrip that fires when a ticket is reopened
(status changes from resolved to open) and then (quietly) reset it to
resolved. Just enable the scrip when your external script is going to
be
e-mailing attachments to RT. This ought to be pretty straightforward.
I
would do the status reset using
$self->TicketObj->_Set(Field=>‘Status’, Value=>‘resolved’,
RecordTransaction=>0);
rather than
$self->TicketObj->SetStatus(‘resolved’);
to avoid triggering any “On Resolve” scrips.

Regards,
Gene

At 01:05 PM 4/10/2008, Aaron Sallade wrote:

Hmm,

I wrote a script that can email the attachments to RT with the proper
Ticked ID format in the header, my concern is :

We just finished our import of data from the legacy system and have
about 15,000 tickets that are resolved, but do not have their
attachments added. If I add them via email, it will open the ticket.

If I can find out how to not change the status or the resolved date by
commenting out a portion of the code that handles ticket replies, then
it could work.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org

-----Original Message-----
From: Kenneth Crocker [mailto:KFCrocker@lbl.gov]
Sent: Thursday, April 10, 2008 12:45 PM
To: Gene LeDuc
Cc: Aaron Sallade; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Where are attachments stored?

Gene, Aaron,

    Not only that, but the content is stored in various ways,

depending on
the CONTENTTYPE field (text, message, excell, etc.) AND if you have
ORACLE, CONTENTCODING is not supported (at least up to 9). I built my
own Data Dictionary on the ORACLE version of the RT DataBase so I would
know what to do if I was gonna update the DB via SQL or something.
Fooling around with creating or deleting records is more than a bit
tricky external to RT tools. Like I didn’t know that ALL users have a
group id record JUST for their individual user ID. It is usually the
User ID plus 1. Anyway, like Gene said, not a trivial exercise.

Kenn
LBNL

Aaron, the attachments are stored in RT’s database in a table called
‘Attachments’. There is a field called ‘Content’ that holds the
ones
and zeros that make up the attachment. I suspect that importing
files

into RT as ticket attachments will be a non-trivial exercise.

Regards,
Gene

At 11:02 AM 4/10/2008, Aaron Sallade wrote:

I need to import attachments into our RT from our legacy system.
How
are attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
/“Shared Technology for Community Health”
/(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org mailto:asallade@ptsowa.org

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Gene LeDuc, GSEC
Security Analyst
San Diego State University

7

Why not just update them as a comment, which will not re-open the
ticket?

Alternately, you could just disable the global scrip that
re-opens the tickets while you reload.
-Chuck Boeheim, SLACOn Apr 10, 2008, at 1:39 PM, Aaron Sallade wrote:

My concern on that is the timestamp for Resolved and how it impacts
reporting :frowning:

I am going to try to disable the scrips and see if it works.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org

-----Original Message-----
From: Gene LeDuc [mailto:gleduc@mail.sdsu.edu]
Sent: Thursday, April 10, 2008 1:30 PM
To: Aaron Sallade
Cc: rt-users@lists.bestpractical.com
Subject: RE: [rt-users] Where are attachments stored?

Hi Aaron,

Good call on the import method - that way RT gets to do all the grunt
work. Consider creating a scrip that fires when a ticket is reopened
(status changes from resolved to open) and then (quietly) reset it to
resolved. Just enable the scrip when your external script is going to
be
e-mailing attachments to RT. This ought to be pretty straightforward.
I
would do the status reset using
$self->TicketObj->_Set(Field=>‘Status’, Value=>‘resolved’,
RecordTransaction=>0);
rather than
$self->TicketObj->SetStatus(‘resolved’);
to avoid triggering any “On Resolve” scrips.

Regards,
Gene

At 01:05 PM 4/10/2008, Aaron Sallade wrote:

Hmm,

I wrote a script that can email the attachments to RT with the proper
Ticked ID format in the header, my concern is :

We just finished our import of data from the legacy system and have
about 15,000 tickets that are resolved, but do not have their
attachments added. If I add them via email, it will open the ticket.

If I can find out how to not change the status or the resolved date
by
commenting out a portion of the code that handles ticket replies,
then
it could work.

Aaron Sallade’
Application Manager
PTSO of Washington
“Shared Technology for Community Health”
(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org

-----Original Message-----
From: Kenneth Crocker [mailto:KFCrocker@lbl.gov]
Sent: Thursday, April 10, 2008 12:45 PM
To: Gene LeDuc
Cc: Aaron Sallade; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Where are attachments stored?

Gene, Aaron,

   Not only that, but the content is stored in various ways,

depending on
the CONTENTTYPE field (text, message, excell, etc.) AND if you have
ORACLE, CONTENTCODING is not supported (at least up to 9). I built my
own Data Dictionary on the ORACLE version of the RT DataBase so I
would
know what to do if I was gonna update the DB via SQL or something.
Fooling around with creating or deleting records is more than a bit
tricky external to RT tools. Like I didn’t know that ALL users have a
group id record JUST for their individual user ID. It is usually the
User ID plus 1. Anyway, like Gene said, not a trivial exercise.

Kenn
LBNL

On 4/10/2008 11:45 AM, Gene LeDuc wrote:

Aaron, the attachments are stored in RT’s database in a table called
‘Attachments’. There is a field called ‘Content’ that holds the
ones
and zeros that make up the attachment. I suspect that importing
files

into RT as ticket attachments will be a non-trivial exercise.

Regards,
Gene

At 11:02 AM 4/10/2008, Aaron Sallade wrote:

I need to import attachments into our RT from our legacy system.
How
are attachments stored in RT?

Aaron Sallade’
Application Manager
PTSO of Washington
/“Shared Technology for Community Health”
/(206) 613-8938 Desk
(206) 521-8833 Main
(206) 613-5078 Fax
asallade@ptsowa.org mailto:asallade@ptsowa.org

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

–
Gene LeDuc, GSEC
Security Analyst
San Diego State University

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com