[rt-users] TicketSQL with Custom Fields
Hello,
Attached to our tickets, we have a custom field called 'Reminder
Interval'. In this field, a number is written in minutes. I would like
to create a query that checks if the last update has been longer that
the amount of minutes in this field. I would think that that query would
look like this:
Status = 'open' AND 'CF.{Productomschrijving}' IS NOT 'NULL' AND
LastUpdated 'CF.{Reminder Interval} minutes ago'
However, there is for example a ticket that has 15 in that custom field
and that hasn't been updated in 2 hours.
If I have the query:
Status = 'open' AND 'CF.{Productomschrijving}' IS NOT 'NULL' AND
LastUpdated '15 minutes ago'
this gives back the 1 result.
However if I use the first query, no results are returned.
Am I using an incorrect syntax?
Thanks,
Alain
--
Met vriendelijke groet,
Claranet Benelux BV
Alain Sips
Software Ontwikkeling
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] [bug] Message without text but with attachment(s) is not recodered
Hello Boris,
Thanks for the reply. This works perfect for the Tickets now. The bulk
update doesn;t seem to work yet, however we don;t really use that for
attaching files, so that is no problem for us.
Thanks again for the great help !
Alain
Boris Lytochkin schreef:
The reason is in share/html/Ticket/Display.html:
$ARGS{'UpdateContent'} =~ s/\r\n/\n/g if defined $ARGS{'UpdateContent'};
if ( $ARGS{'UpdateTimeWorked'} || (
defined $ARGS{'UpdateContent'}
$ARGS{'UpdateContent'} ne ''
$ARGS{'UpdateContent'} ne -- \n
. $session{'CurrentUser'}-UserObj-Signature ) )
{
$ARGS{UpdateAttachments} = $session{'Attachments'};
ProcessUpdateMessage(
ARGSRef = \%ARGS,
Actions = [EMAIL PROTECTED],
TicketObj = $TicketObj,
);
delete $session{'Attachments'};
}
But should be
if ( $ARGS{'UpdateTimeWorked'} || (
defined $ARGS{'UpdateContent'}
$ARGS{'UpdateContent'} ne '' ) ||
scalar(keys(%{$session{'Attachments'}})) )
{
$ARGS{'UpdateContent'} = $ARGS{'UpdateContent'}.--
\n.$session{'CurrentUser'}-UserObj-Signature;
$ARGS{UpdateAttachments} = $session{'Attachments'};
ProcessUpdateMessage(
ARGSRef = \%ARGS,
Actions = [EMAIL PROTECTED],
TicketObj = $TicketObj,
);
delete $session{'Attachments'};
}
Additionally, 'IncludeSignature = 0' must be set in MessageBox on Update.html
page.
The same situation is with BulkUpdate form, it must be like this:
# Prepare for ticket updates
if ($ARGS{'UpdateContent'}) {
if ( $ARGS{'UpdateContent'} ne '' ||
scalar(keys(%{$session{'Attachments'}})) ){
$ARGS{'UpdateContent'} .= \n--
\n.$session{'CurrentUser'}-UserObj-Signature;
$do_comment_reply = 1;
}
}
with 'IncludeSignature = 0' set in MessageBox.
'IncludeSignature = 0' should be used because of different wrapping
styles - make your signature be with looong lines without
hand-made wrapping (using \n) - RT will record your signature as text because
$ARGS{'UpdateContent'} ne -- \n.$session{'CurrentUser'}-UserObj-Signature )
will be true because of broken '\n' sequence.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Can't attach to an empty message
I'm sorry to ask this again, but there hasn't been a reply yet. Is nobody experiencing these problems? Thanks, Alain Alain Sips schreef: In RT3.6.4 it seems that it is not possible to attach a file to a ticket if you leave the comment field empty. Is this expected behavior? And if so, is there an easy way to go around this? If you click comment on a ticket and you attach a file and leave the comment empty and press update Ticket, nothing is attached to the ticket (also no entry is found in the attachments table). Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Can't attach to an empty message
In RT3.6.4 it seems that it is not possible to attach a file to a ticket if you leave the comment field empty. Is this expected behavior? And if so, is there an easy way to go around this? If you click comment on a ticket and you attach a file and leave the comment empty and press update Ticket, nothing is attached to the ticket (also no entry is found in the attachments table). Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Also Reminders question
Hello,
I'm sorry to ask this (probably stupid) question here, but I can't seem
to find the correct variables to make this work.
By default the my reminders show all reminders set for nobody or for
yourself.
We would like to see the reminders for yourself or for everybody who has
modify rights in the queue in which the reminder is set.
I see that in MyReminders you've got:
my $reminders = RT::Tickets-new($session{'CurrentUser'});
$reminders-FromSQL('(Owner = Nobody OR Owner =
'.$session{'CurrentUser'}-Name.')' .
' AND Type = reminder AND (Status = new OR Status = open) AND
Due 1970-01-01');
$reminders-OrderBy(FIELD = 'Due', ORDER = 'DESC');
/%init
Here it should be possible to set a condition that the if the owner of
the message is nobody, the current user should have modify rights for
the queue to which the ticket belongs.
But how can I query for the rights of the user?
Thanks,
Alain
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Upgrade to 3.6.3 gives Problems if Queue has character in name
I did an upgrade from 3.4.5 to 3.6.3. Everything seems to be working fine, except 1 queue. There is a queue called Messina RT, and if I click on this in the quicksearch table, it returns no results (in the quicksearch table it does say how many open, new and stalled tickets there are for this queue, so it can find the queue). If I now go to edit search, I get the following message: *Results* Error near -Messina- expecting a VALUE in 'Queue = 'Messina ' I'm lost Also in the search field it says: Queue = 'Messina (so at the ampersand it stops parsing). We never had a problem with this in the previous version, so I guess it is a bug? Is there something I can do about this? Can you test out RT 3.6.4RC2? Best, Jesse Hey Jesse, I tried it in 3.6.4 and here it seems to work fine. No problems anymore :-) One thing that I noticed is: if you edit the name of your queue, the Quick Search table doesn't update itself. The old queue name stays in it, and you have to edit Quick search. Here you do find the new name of the queue. You have to unselect it and select it again and then the correct queue name (and therefor correct results) will be found in the Quick Search. Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] Upgrade to 3.6.3 gives Problems if Queue has character in name
Hello, I only replied to Jesse by mistake, so here is my reply to the list. Kind regards, Alain Alain Sips schreef: Hello, I looked for previous posts regarding this problem, but couldn't seem to find any, so I'll try my luck here :-) I did an upgrade from 3.4.5 to 3.6.3. Everything seems to be working fine, except 1 queue. There is a queue called Messina RT, and if I click on this in the quicksearch table, it returns no results (in the quicksearch table it does say how many open, new and stalled tickets there are for this queue, so it can find the queue). If I now go to edit search, I get the following message: *Results* Error near -Messina- expecting a VALUE in 'Queue = 'Messina ' I'm lost Also in the search field it says: Queue = 'Messina (so at the ampersand it stops parsing). We never had a problem with this in the previous version, so I guess it is a bug? Is there something I can do about this? Can you test out RT 3.6.4RC2? Best, Jesse Hi Jesse, At the moment it's not possible for me to do that, but I will do that in the near future. For the moment I just renamed the queue. First that didn't seem to help, as the name stayed the old one in the quicksearch (everywhere else it was updated), however after a while it also changed in the quicksort. I guess that it was still in the cache or something. I will definitly try out the next upgrade later, and I'll let yo uknow what happened. Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Upgrade to 3.6.3 gives Problems if Queue has character in name
Hello, I looked for previous posts regarding this problem, but couldn't seem to find any, so I'll try my luck here :-) I did an upgrade from 3.4.5 to 3.6.3. Everything seems to be working fine, except 1 queue. There is a queue called Messina RT, and if I click on this in the quicksearch table, it returns no results (in the quicksearch table it does say how many open, new and stalled tickets there are for this queue, so it can find the queue). If I now go to edit search, I get the following message: *Results* Error near -Messina- expecting a VALUE in 'Queue = 'Messina ' I'm lost Also in the search field it says: Queue = 'Messina (so at the ampersand it stops parsing). We never had a problem with this in the previous version, so I guess it is a bug? Is there something I can do about this? Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
[rt-users] Slow RT
Hello all, We are having trouble with slow queries for a long time already. We are using RT 3.4.5 (but planning to upgrade to RT3.6). Also we are using mysql 5.0.15. Some of our tickets are rather long, and those can take up to a minute to load. The slow query option of sql is turned on and this under this mail is a snippet of it. The first one out of it takes 24 seconds, the last one even 237 seconds. It also happens a lot that the SELECT GET_LOCK takes 360 seconds (the timeout time). If I do an explain on the first query, I get this result: mysql explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType= 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Status = 'resolved')AND(main.Queue = '4')AND(main.Subject LIKE '%dsl exp%')AND ( (Attachments_2.Content LIKE '%cpe%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); ++-++--+-+---+-++---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra| ++-++--+-+---+-++---+--+ | 1 | SIMPLE | main | ref | PRIMARY,Tickets1,Tickets6,i_custom2 | Tickets1 | 17 | const,const| 50436 | Using where | | 1 | SIMPLE | Transactions_1 | ref | PRIMARY,Transactions1 | Transactions1 | 70 | const,rt3.main.EffectiveId | 1 | Using where; Using index | | 1 | SIMPLE | Attachments_2 | ref | Attachments2| Attachments2 | 4 | rt3.Transactions_1.id | 1 | Using where | ++-++--+-+---+-++---+--+ 3 rows in set (0.00 sec) If I do an explain on the last query, I get: mysql explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType= 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Status = 'resolved')AND(main.Queue = '4')AND(main.Subject LIKE '%dsl exp%')AND ( (Attachments_2.Content LIKE '%cpe%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); ++-++--+-+---+-++---+--+ | id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra| ++-++--+-+---+-++---+--+ | 1 | SIMPLE | main | ref | PRIMARY,Tickets1,Tickets6,i_custom2 | Tickets1 | 17 | const,const| 50598 | Using where | | 1 | SIMPLE | Transactions_1 | ref | PRIMARY,Transactions1 | Transactions1 | 70 | const,rt3.main.EffectiveId | 1 | Using where; Using index | | 1 | SIMPLE | Attachments_2 | ref | Attachments2| Attachments2 | 4 | rt3.Transactions_1.id | 1 | Using where | ++-++--+-+---+-++---+--+ 3 rows in set (0.00 sec) I guess these explain results are ok, or am I mistaken? Is there somewhere else that I can maybe have a look? Thanks, Alain # Query_time: 24 Lock_time: 0 Rows_sent: 1 Rows_examined: 35113 SELECT COUNT(DISTINCT main.id) FROM Tickets main , Transactions Transactions_1, Attachments Attachments_2 WHERE ((Transactions_1.ObjectType= 'RT::Ticket')) AND ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ((main.Status = 'resolved')AND(main.Queue = '4')AND(main.Subject LIKE '%dsl exp%')AND ( (Attachments_2.Content LIKE '%cpe%')AND(Attachments_2.TransactionId = Transactions_1.id)AND(main.id = Transactions_1.ObjectId) ) ); # Time: 070315 11:42:37 # [EMAIL PROTECTED]: rt_user[rt_user] @ rtdb.vianetworks.nl [10.0.15.58] # Query_time: 12 Lock_time: 0
RE: [rt-users] Ticket doesn't load completly
Hello Jesse, Thanks for the help. Removing Text::Quoted did do the trick. Thanks, Alain I also noticed that it always happens if the next Transaction that needs to be loaded contains a euro-sign. So it probably also has something to do with that. What Perl are you running? I _believe_ 3.4.5 is of a vintage where simply removing Text::Quoted from your system will make RT stop trying to colorize. (3.6.1 wasn't. 3.6.2 will be again) -j Thanks for the help, Alain On Tue, Nov 21, 2006 at 01:53:00PM +0100, Alain Sips wrote: Hello, We've always worked with RT 3.4.4, but when our frontend sever died, we had to install on a new machine and RT 3.4.5. has been installed. At first sight this didn't give any problems, all tickets load as usual. My guess is that something is causing Perl to segfault. Whatcha getting in your error logs? I saw this recently where Text::Tabs (called by Text::Quoted, our indent-based colorizer) dealt poorly with a message causing perl to Segfault. That was what we thought. Apparently there are some tickets (just a few, we found 3 of them in a week time)that won't load completely. The browser says everything is loaded (no loading signs anymore), however not all transactions are visible and you also don't see the final line on the page that tells you how long it took to load the page. You can reply or comment on the ticket and then you will see that the ticket has been updated, however it will never load more of the ticket than the first time. Is this a know problem? And is there something I can do about it? Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com -- ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
RE: [rt-users] Problems Multi taksing
This is if you do
SHOW Processlist;
in mysql.
I think we may have found the cause of the problem: every user is a
privileged user who had modifyticket rights. Of course this brings problems
once the database is growing. I'm going to change all permissions today and
hope this will do the trick..
Thanks,
Alain
-Original Message-
From: Dmitri Tikhonov [mailto:[EMAIL PROTECTED]
Sent: maandag 30 oktober 2006 19:23
To: Alain Sips
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Problems Multi taksing
On Mon, 2006-10-30 at 09:18 +0100, Alain Sips wrote:
I checked where it can be, and one thing I found out is that when you
want to retrieve a ticket, a lock is set during the time the ticket
gets displayed on the screen, for example:
| 40456 | rt_user | rtdb.vianetworks.nl:56980 | rt3 | Query |8
| User lock| SELECT
GET_LOCK('Apache-Session-3623293be2c14213fb0441bcba44ccf0', 3600)
Which table is this from?
- Dmitri.
___
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
[rt-users] Query to look for update in last X days
Hello, Is it possible to make a query in the query builder that looks for tickets that havent been updated in the last X days? You can fill in a date, but we would like to have a saved query, so the date always changes. Thanks, Alain ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html
[rt-users] permissions of RT_System
Hello, I am trying to set a custom field based on an incoming mail. Part of the code looks like this: my $CF_Obj = RT::CustomField-new($RT::SystemUser); my $cf_name = Skill; $CF_Obj-LoadByName( Name = $cf_name, Queue = '0',); my ($retcode, $msg) = $CF_Obj-AddValueForObject( Object = $self-TicketObj, Content = $cf_value, ); $RT::Logger-debug(Return code [$retcode] Msg [$msg]\n); The scrip gets executed, however the custom field wont be set, because the permission is denied. The user is RT_System, but I cant seem to find where I can set the ModifyCustomField right for this user. Can someone please help me? Thanks, Alain Sips ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com We're hiring! Come hack Perl for Best Practical: http://bestpractical.com/about/jobs.html
