TicketSQL

TicketSQL is RT's loose variant of SQL that you can use for composing custom queries by hand:

• in RT's web interface on the Query Builder page (click Advanced)
• on the command-line: $RTHOME/bin/rt-crontool or $RTHOME/bin/rt, the general-purpose command-line tool
• in RT's Perl API

For the time being, the best way to learn TicketSQL is to compose queries using Query Builder and then click Advanced to see the generated TicketSQL code.

Special placeholders

If you would like to search relative to the current user, you can click Advanced and enter something like

(Owner = '__CurrentUser__')

You can use __CurrentUser__ anywhere you'd use a user id. This is really useful for building saved searches for people.

e.g. Owner = '__CurrentUser__' AND Status != 'resolved'

Bookmarked tickets can be searched too:

id = '__Bookmarked__'

Date Syntax

Date statements take the following form <Field> <Operator> <Date>. Field is some Ticket Field of type date. Operator is the comparison Operator. Date is a date value.

Valid operators include:

• < (less than)
• <= (less than or equal to)

= (equals)

• != (not equal to)
• > (greater than)
• >= (greater than or equal to)

There are at least three valid date formats:

• today uses today's date as the date value
• x days ago where x is some integer value (e.g. 8 days ago)
• YYYY-MM-DD absolute day in the format year-month-date (e.g. 2024-11-14)
• 'n seconds', 'n minutes', 'n hours', 'n days', 'n weeks' are dates in the future relative to the current date, ie current_date + n minutes. May be negative, eg '-2 days' is two days in the past.

Consult the Time::ParseDate documentation for all valid date/time formats.

Examples

Tickets in the General queue that are new or open and owned by joe:

(Status = 'new' OR Status = 'open') AND Queue = 'General' AND owner = 'joe'

Searching by date:

Created > '7 days ago' AND Queue = 'General'

Status = 'stalled' AND Due <= 'today'

Accessing custom fields:

Status = 'resolved' AND CF.YourCustomField = 'somevalue'

Tickets that have no members (children):

HasMember = 'NULL'

Tickets that depend on at least one other ticket:

DependsOn != 'NULL'

Perl API

 my $tickets = RT::Tickets->new(RT->SystemUser);
 $tickets->FromSQL($tsql);
 while (my $t = $tickets->Next) {
     # do stuff with each ticket $t here
     print $t->Subject, "\n";
 }