How to modify the ticket # format?

RT Users
1

Hello all, New to the list, still newish to RT.

I’ve been using an old ‘hacked’ up version of RT 2.0.x for some time now,
and was recently asked if we could just “cut and move forward” - so
instead of
trying to migrate the DB and such, I’ve installed and deployed a new 3.8.2
deployment - and wow - nice changes since 2.0.x :slight_smile:

Anyways, one of the requirements I need to implement, is the ticket #
needs to be in the format of YEARMONTHDAYxxxx

Example: #200905110001 would be the 1st ticket for today.

I can’t seem to find out where I can do this, as I would also need this to
automatically roll-over @ Midnight, GMT time (RT_Siteconfig.pm already has
my timezone set to GMT)

Any pointers?

Eric Malenfant
Mitel Networks
IT Systems Specialist
AD / UNIX Systems / Network Applications
Toll Free: 800.267.6244 ext 2864
Eric_Malenfant@mitel.com

Unix is user friendly, it’s just picky who it’s friends are!

2

SetStartingId - Request Tracker Wiki + cronjob to set it
everyday, however you must understand limits on integers in your DB.On Mon, May 11, 2009 at 10:58 PM, Eric_Malenfant@mitel.com wrote:

Hello all, New to the list, still newish to RT.

I’ve been using an old ‘hacked’ up version of RT 2.0.x for some time now,
and was recently asked if we could just “cut and move forward” - so instead
of
trying to migrate the DB and such, I’ve installed and deployed a new 3.8.2
deployment - and wow - nice changes since 2.0.x :slight_smile:

Anyways, one of the requirements I need to implement, is the ticket # needs
to be in the format of YEARMONTHDAYxxxx

Example: #200905110001 would be the 1st ticket for today.

I can’t seem to find out where I can do this, as I would also need this to
automatically roll-over @ Midnight, GMT time (RT_Siteconfig.pm already has
my timezone set to GMT)

Any pointers?

Eric Malenfant
Mitel Networks
IT Systems Specialist
AD / UNIX Systems / Network Applications
Toll Free: 800.267.6244 ext 2864
Eric_Malenfant@mitel.com

Unix is user friendly, it’s just picky who it’s friends are!

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

Best regards, Ruslan.

3

Ruslan Zakirov wrote:

SetStartingId - Request Tracker Wiki + cronjob to set it
everyday, however you must understand limits on integers in your DB.

Example: #200905110001 would be the 1st ticket for today.

A normal signed int in 32-bit mysql has a maximum value of 2147483647, which
isn’t big enough for the format requested. You’d need to alter the table
structures to change ticket id’s to a bigint. How much code that would
break inside of RT, I have no idea.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

4

Tom,

Why do you need a date prefixed to the Ticket ID? In my 40 years in

this business from Manufacturing (MRP - Order processing, Capacity
Planning, etc.)) to Retail to Banking industries I have always seen an
ID Field defined as “non-intelligent”. That way, the ID has one function
only, to identify the record, not to define when (that’s what dates are
for). To put intelligence or some other meaning into a field that is
designed for a singular purpose is pretty much considered a very
outdated practice (for last 25 years) in DataBase design (ie. each field
is used strictly for a single purpose and it’s name indicates such
function). Are you sure you want to design the Ticket ID this way?

Kenn
LBNLOn 5/11/2009 12:56 PM, Tom Lahti wrote:

Ruslan Zakirov wrote:

SetStartingId - Request Tracker Wiki + cronjob to set it
everyday, however you must understand limits on integers in your DB.

Example: #200905110001 would be the 1st ticket for today.

A normal signed int in 32-bit mysql has a maximum value of 2147483647, which
isn’t big enough for the format requested. You’d need to alter the table
structures to change ticket id’s to a bigint. How much code that would
break inside of RT, I have no idea.

5

Ken Crocker wrote:

Tom,

Why do you need a date prefixed to the Ticket ID? In my 40 years in

I don’t, the OP (Eric) does. Couldn’t agree with you more on your point.
There’s already a “created date” field for each ticket. To add that again
as part of the ticket ID will definitely be counterproductive. If the
proposed cron job doesn’t fire for some reason, you’ll have a mess: ticket
ID’s with this embedded ‘create date’ will lie.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

6

Tom,

Correct. Plus, you may well run out of viable Ticket ID's. IF I were

you. I would do my best to dissuade. Lots of luck.

Kenn
LBNLOn 5/11/2009 2:39 PM, Tom Lahti wrote:

Ken Crocker wrote:

Tom,

Why do you need a date prefixed to the Ticket ID? In my 40 years in

I don’t, the OP (Eric) does. Couldn’t agree with you more on your point.
There’s already a “created date” field for each ticket. To add that again
as part of the ticket ID will definitely be counterproductive. If the
proposed cron job doesn’t fire for some reason, you’ll have a mess: ticket
ID’s with this embedded ‘create date’ will lie.

7 
Correct. Plus, you may well run out of viable Ticket ID's. IF I> were

you. I would do my best to dissuade. Lots of luck.

In a private message (for some reason, I expected to be on the wrong side
of this argument) I provided the OP with several concrete reasons and examples
as to why this is such a breathtakingly bad design choice, and he should
hold his breath until he turns blue if his management tries to make him.

:slight_smile:

Cheers,
– jra
Jay R. Ashworth Baylink jra@baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com '87 e24
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274

Start a man a fire, and he'll be warm all night.
 Set a man on fire, and he'll be warm for the rest of his life.
8

Correct. Plus, you may well run out of viable Ticket ID’s. IF I>
were
you. I would do my best to dissuade. Lots of luck.

In a private message (for some reason, I expected to be on the wrong
side
of this argument) I provided the OP with several concrete reasons
and examples
as to why this is such a breathtakingly bad design choice, and he
should
hold his breath until he turns blue if his management tries to make
him.

:slight_smile:

Considering the current economic climate and the abundance of lightly
skilled admins that would be willing to alter the RT DB schema to suit
his suits wishes, perhaps an approach that doesn’t involve turning
blue would be advised.

Since every RT ticket already has the ticket ID & date, it would not
be terribly difficult to hack at the SQL queries that create, search,
and retrieve tickets. If you have the skills, hack the queries to
manipulate the ticket ID into the preferred format. You could also
create a custom table that maps the ‘normal’ RT ticket ID and the
‘preferred’ ticket ID #.

Another option is writing a custom web interface to RT, using the REST
API. Have the web application handle the mapping and presentation of
ticket IDs to and from the desired format. It’s greatly simplified by
using RT::Client::REST. I wrote one in only 387 lines of perl:

$ wc -l lib/Spry/Tickets.pm
387 lib/Spry/Tickets.pm

$ grep ‘^sub’ lib/Spry/Tickets.pm
sub new {
sub rt_connect {
sub rt_close_ticket {
sub rt_create_ticket {
sub rt_get_tickets {
sub rt_update_ticket {
sub _get_rt {

If perl isn’t your cup of tea, write your web app in your favorite
language and interface directly with the database. You’ll have a bit
more testing to do during future RT upgrades, but the RT schema is
quite stable.

Either way, you’ll have the ability to easily automate and radically
alter how RT data is presented, thus appeasing the pointy haired man.

Matt

9

Well, modifying the code is what was done years ago for our current
implementation. It was deployed back before Mitel had purchased E-Smith
(old Linux distro) and
is actually still in use today, but we have some new requirements which
warrants an upgrade.

The one thing that would be great to keep, is how we are numbering our
tickets today.

Today, when a ticket is created, it does get a number like this:

id: 20090414054

this number would already be considered greater than 2147483647, and I
believe from what I have heard about the history of this old RT 2.x
deployment (I believe the old version in use is 2.0.7)
was in fact that we worked with the devs to get this to work, and I can in
fact see bigint(xx) in some parts of the schema.

I can hack around that part. What I would like to know is where can I set
the start # to begin with.

If I do abandon this date format for tickets, I’d then want to start at
something high, like 10000, not 1.

Is that possible?

Eric Malenfant
Mitel Networks
IT Systems Specialist
AD / UNIX Systems / Network Applications
Toll Free: 800.267.6244 ext 2864
Eric_Malenfant@mitel.com

Unix is user friendly, it’s just picky who it’s friends are!

Tom Lahti toml@bitstatement.net
05/11/2009 03:56 PM

To
Ruslan Zakirov ruslan.zakirov@gmail.com
cc
Eric_Malenfant@mitel.com, rt-users@lists.bestpractical.com
Subject
Re: [rt-users] How to modify the ticket # format?

Ruslan Zakirov wrote:

SetStartingId - Request Tracker Wiki + cronjob to set it
everyday, however you must understand limits on integers in your DB.

Example: #200905110001 would be the 1st ticket for today.

A normal signed int in 32-bit mysql has a maximum value of 2147483647,
which
isn’t big enough for the format requested. You’d need to alter the table
structures to change ticket id’s to a bigint. How much code that would
break inside of RT, I have no idea.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

10

Well, I don’t need to implement this style anymore - provided some
reasoning, and was agreed upon.

What I would like to do, is now to increase the baseline of the ticket # -
I don’t need to start @ 1- too low, so I want to start around 1000.

Any idea how I can increase this number?

Eric Malenfant
Mitel Networks
IT Systems Specialist
AD / UNIX Systems / Network Applications
Toll Free: 800.267.6244 ext 2864
Eric_Malenfant@mitel.com

Unix is user friendly, it’s just picky who it’s friends are!

Eric_Malenfant@Mitel.com
Sent by: rt-users-bounces@lists.bestpractical.com
05/12/2009 08:28 AM

To
Tom Lahti toml@bitstatement.net
cc
rt-users@lists.bestpractical.com, Ruslan Zakirov
ruslan.zakirov@gmail.com
Subject
Re: [rt-users] How to modify the ticket # format?

Well, modifying the code is what was done years ago for our current
implementation. It was deployed back before Mitel had purchased E-Smith
(old Linux distro) and
is actually still in use today, but we have some new requirements which
warrants an upgrade.

The one thing that would be great to keep, is how we are numbering our
tickets today.

Today, when a ticket is created, it does get a number like this:

id: 20090414054

this number would already be considered greater than 2147483647, and I
believe from what I have heard about the history of this old RT 2.x
deployment (I believe the old version in use is 2.0.7)
was in fact that we worked with the devs to get this to work, and I can in
fact see bigint(xx) in some parts of the schema.

I can hack around that part. What I would like to know is where can I set
the start # to begin with.

If I do abandon this date format for tickets, I’d then want to start at
something high, like 10000, not 1.

Is that possible?

Eric Malenfant
Mitel Networks
IT Systems Specialist
AD / UNIX Systems / Network Applications
Toll Free: 800.267.6244 ext 2864
Eric_Malenfant@mitel.com

Unix is user friendly, it’s just picky who it’s friends are!

Tom Lahti toml@bitstatement.net
05/11/2009 03:56 PM

To
Ruslan Zakirov ruslan.zakirov@gmail.com
cc
Eric_Malenfant@mitel.com, rt-users@lists.bestpractical.com
Subject
Re: [rt-users] How to modify the ticket # format?

Ruslan Zakirov wrote:

SetStartingId - Request Tracker Wiki + cronjob to set it
everyday, however you must understand limits on integers in your DB.

Example: #200905110001 would be the 1st ticket for today.

A normal signed int in 32-bit mysql has a maximum value of 2147483647,
which
isn’t big enough for the format requested. You’d need to alter the table
structures to change ticket id’s to a bigint. How much code that would
break inside of RT, I have no idea.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================
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

11

Well, I don’t need to implement this style anymore - provided some
reasoning, and was agreed upon.
What I would like to do, is now to increase the baseline of the
ticket # - I don’t need to start @ 1- too low, so I want to start
around 1000.
Any idea how I can increase this number?

As Ruslan mentioned earlier in this thread:
http://wiki.bestpractical.com/view/SetStartingId

-kevin

12

Matt Simerson wrote:

If perl isn’t your cup of tea, write your web app in your favorite
language and interface directly with the database. You’ll have a bit
more testing to do during future RT upgrades, but the RT schema is
quite stable.

Actually, you’re forgetting about the ruby library rt-client, which also
uses RT’s REST interface.

I never recommend going directly to the database. If you want to use a
language for which there is no REST client library, I recommend writing
one, or porting one of the existing ones.

13

Don’t modify the ticket number, but instead the RT tag?

[Yoyodyne 2009-05-14 #1337]

Cambridge Energy Alliance: Save money. Save the planet.