TicketSQL question

I’m trying to build a query for the rt command line.
Included in the query would be '… AND lastupdated != told …'
meaning (I think) that the requestor did the last updating.
‘lastupdatedby=requestor’ would also work.

Except it is an invalid query.

Are there better docs for TicketSQL other than
"use the query builder and click ‘advanced’" ?
I can’t find better in the book or wiki.
The query builder doesn’t allow internal terms
like requestor or owner or told on the right side
of an equals sign.

If not, what is an appropriate query for
"gimme all tickets where the requestor has been
waiting more than X days for a response from the consultants" ?

bobg

The query builder doesn’t allow internal terms
like requestor or owner or told on the right side
of an equals sign.

Correct.

If not, what is an appropriate query for
“gimme all tickets where the requestor has been
waiting more than X days for a response from the consultants” ?

Told > ‘3 days ago’

or

LastUpdated > ‘3 days ago’

or something like that.

The query builder doesn’t allow internal terms
like requestor or owner or told on the right side
of an equals sign.

Correct.

If not, what is an appropriate query for
“gimme all tickets where the requestor has been
waiting more than X days for a response from the consultants” ?

Told > ‘3 days ago’

or

LastUpdated > ‘3 days ago’

or something like that.

Thanks. But I don’t see how that works.
LastUpdated is set when a queue changes or the
owner is assigned, not just for the last correspond.

If the requestor was Told 3 days ago and never
replied, that’s his problem. But if he did
reply, it’s ours. However, if he did reply
and we assigned the ticket to a consultant
(thus updating LastUpdated), the ball is still
in our court.

I basically want to know, did the requestor
send that last Correspond? If he did,
and did it 3 days ago, I need to crack some
heads. But if we sent the last correspond,
the heads can stay healthy.

 bobg

I am getting the error like below and I do not have a clue why can some one please help me with this.

Thanks Joel

Forcing the attempted delivery of mail with the command /usr/lib/sendmail -v -q -C/etc/mail/sendmail.cf …

Running /var/spool/mqueue/j9HGJvvs026702 (sequence 1 of 1)“|rt-mailgate --queue general --action comment --url http://137.218.0.4/rt”… Connecting to prog…mailer prog died with signal 11"|rt-mailgate --queue general --action comment --url http://137.218.0.4/rt"… Deferred: prog mailer (/usr/sbin/smrsh) exited with EX_TEMPFAIL

Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

You don’t have your smrsh setup.

Depending on where your smrsh directory is, you need to link to
rt-mailgate. Often, smrsh will limit the links to /bin and /usr/bin
depending on how it was compiled. My suggestion would be to:

Make a symlink from /usr/bin to rt-mailgate
Make a symlink from your smrsh directory to /usr/bin/rt-mailgate.

Regards

Ramon Kagan, GCIA
York University, Computing and Network Services
Information Security - Senior Information Security Analyst
(416)736-2100 #20263
rkagan@yorku.ca


I have not failed. I have just I don’t know the secret to success,
found 10,000 ways that don’t work. but the secret to failure is
trying to please everybody.
- Thomas Edison - Bill Cosby
----------------------------------- ------------------------------------On Tue, 18 Oct 2005, Joel p wrote:

I am getting the error like below and I do not have a clue why can some one please help me with this.

Thanks Joel

Forcing the attempted delivery of mail with the command /usr/lib/sendmail -v -q -C/etc/mail/sendmail.cf …

Running /var/spool/mqueue/j9HGJvvs026702 (sequence 1 of 1)“|rt-mailgate --queue general --action comment --url http://137.218.0.4/rt”… Connecting to prog…mailer prog died with signal 11"|rt-mailgate --queue general --action comment --url http://137.218.0.4/rt"… Deferred: prog mailer (/usr/sbin/smrsh) exited with EX_TEMPFAIL


Yahoo! Music Unlimited - Access over 1 million songs. Try it free.

I basically want to know, did the requestor
send that last Correspond? If he did,
and did it 3 days ago, I need to crack some
heads. But if we sent the last correspond,
the heads can stay healthy.

I don’t believe that is possible in raw TicketSQL.

It wouldn’t be hard to add a “LastTransaction” concept to TicketSQL,
but then you have to ask the question: “Do we generalize this? What
if I want the 2nd to last transaction?” “What if I want the last
Comment?” And suddenly the world explodes.

-R

I basically want to know, did the requestor
send that last Correspond? If he did,
and did it 3 days ago, I need to crack some
heads. But if we sent the last correspond,
the heads can stay healthy.

I don’t believe that is possible in raw TicketSQL.

It wouldn’t be hard to add a “LastTransaction” concept to TicketSQL,
but then you have to ask the question: “Do we generalize this? What
if I want the 2nd to last transaction?” “What if I want the last
Comment?” And suddenly the world explodes.

Boom! I think you’re right, but still, what SHOULD
TicketSQL do? How much should it be like SQL?

 ...(select max(Transaction.date) where Transaction.type='correspond') ...

The ‘S’ in SQL basically means subqueries, but not in TicketSQL.
I agree that “last Comment” is fair game, but the “2nd to last”
is a lot harder and less useful, so I’m less concerned there.

Let me generalize my question/quandry. I need some reports.
My options are:
1. TicketSQL
2. Real SQL
3. Perl with Objects and DBIx::SB

TicketSQL doesn’t have the features, although the fact it works
in a url is very attractive. Real SQL would be great if the
schema docs were ok (am I missing them?) and I could count on
the schema not changing, but the book kinda recommends against
raw sql (I’m talking read-only though). Perl would work, of
course, but that seems so laborious and ad hoc compared to sql
(much as I like perl).

What would you do?

 bobg

You have one more option, create new status similar to stalled , then
(using scrips) every time the requestor send a correspondence the ticket
is set to status (CustUpdate) or whatever, any other action or
correspondence from anywhere else reset the status to open (obvoiusly
with the exception of status change) …
Then search for Tickets with status = CustUpdate and LastUpdated > 3
days …

Roy

Bob Goldstein wrote:

Is it possible to search for all tickets Resolved less than 2 weeks
after Creation using ticketSQL?

thanks
Taan

No, only with custom code (using API).On 9/27/06, taan taan@cryologic.com wrote:

Is it possible to search for all tickets Resolved less than 2 weeks
after Creation using ticketSQL?

thanks
Taan


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.

Is it possible to search for all tickets Resolved less than 2 weeks after
Creation using ticketSQL?

thanks
Taan

I am not sure but maybe a query:

status==resolved and TimeWorked<14

will do the trick.

Tom