Searching and comparing two date field values instead of value with supplied valid

Hello,

Is there any way to search for tickets while comparing two field values?

This doesn’t work:
Queue = ‘Incident Reports’ AND Created < CF.{SpecialDate} AND Status = ‘new’

It doesn’t like the string “CF.{SpecialDate}” because it’s not enclosed in
quotations and therefor not a string. Which is what I want to do but
cannot I guess. The error displayed on the RT UI is:

Wrong query, expecting a VALUE in 'Queue = ‘Incident Reports’ AND Created <

CF.{SpecialDate}<–here AND Status = ‘new’’

With normal MySQL queries one can compare two table columns so I’m
wondering if there’s a way to do this with the SQL syntax that RT uses.

Landon Stewart LandonStewart@Gmail.com

Landon wrote:

Sorry for hijacking your question/thread.

Is there any way to search for tickets while comparing two field values?

This doesn’t work:
Queue = ‘Incident Reports’ AND Created < CF.{SpecialDate} AND Status =
‘new’

It doesn’t like the string “CF.{SpecialDate}” because it’s not
enclosed in quotations and therefor not a string. Which is what I
want to do but cannot I guess. The error displayed on the RT UI is:

Wrong query, expecting a VALUE in 'Queue = 'Incident Reports' AND
Created < >CF.{SpecialDate}<--here AND Status = 'new''

I’m looking at something similar. I would like to check whether the
ResolvedDate is past the DueDate but that is also not possible. I seem
to remember that this has been asked before but can’t seem to find that
thread anymore. Probably searching with the wrong keywords so if someone
can point me in the right direction, please?

Regards,

Joop

Landon wrote:

Sorry for hijacking your question/thread.

 Is there any way to search for tickets while comparing two field values?
 This doesn't work:
 Queue = 'Incident Reports' AND Created < CF.{SpecialDate} AND Status = 'new'
 It doesn't like the string "CF.{SpecialDate}" because it's not enclosed in quotations and
 therefor not a string.  Which is what I want to do but cannot I guess.  The error displayed
 on the RT UI is:

   Wrong query, expecting a VALUE in 'Queue = 'Incident Reports' AND Created <
   >CF.{SpecialDate}<--here AND Status = 'new''

I’m looking at something similar. I would like to check whether the ResolvedDate is past the
DueDate but that is also not possible. I seem to remember that this has been asked before but
can’t seem to find that thread anymore. Probably searching with the wrong keywords so if
someone can point me in the right direction, please?

Unfortunately, RT’s TicketSQL cannot compare two fields like that.
It’s hard to free up the engineering time required to make it go
right now. Hopefully at some point it’ll be part of a customer
requirement and that will speed up implementation.

-kevin

Landon wrote:

Sorry for hijacking your question/thread.

 Is there any way to search for tickets while comparing two field

values?

 This doesn't work:
 Queue = 'Incident Reports' AND Created < CF.{SpecialDate} AND

Status = ‘new’

 It doesn't like the string "CF.{SpecialDate}" because it's not

enclosed in quotations and

 therefor not a string.  Which is what I want to do but cannot I

guess. The error displayed

 on the RT UI is:

   Wrong query, expecting a VALUE in 'Queue = 'Incident Reports' AND

Created <

   >CF.{SpecialDate}<--here AND Status = 'new''

I’m looking at something similar. I would like to check whether the
ResolvedDate is past the
DueDate but that is also not possible. I seem to remember that this
has been asked before but
can’t seem to find that thread anymore. Probably searching with the
wrong keywords so if
someone can point me in the right direction, please?

Unfortunately, RT’s TicketSQL cannot compare two fields like that.
It’s hard to free up the engineering time required to make it go
right now. Hopefully at some point it’ll be part of a customer
requirement and that will speed up implementation.

OK failing this then perhaps some light can be shed on comparing dates in
scrips/modules. What I envision is an action module that goes through each
ticket from this condition:
–search RT::Search::FromSQL “id < 1000000”

The --action being something like “RT::Action::CreationDates” or something
that basically loads the ticket and compares the dates and closes tickets
whos CF.{ServerCreated} < Created. This way any ticket containing an IP
address of a server that was created after the ticket was created can be
marked resolved.

The help I need though is on comparing dates using RT::Date or something.
If I load the CF.{ServerCreated} and the Created value how do I compare
them reliably in modules?

Would this work?

my $ServerCreated = DateTime::Format::MySQL->parse_datetime(
$ticket->FirstCustomFieldValue(‘ServerCreated’) );
my $Created = DateTime::Format::MySQL->parse_datetime( $ticketObj->Created
);
$ticket->SetStatus( ‘resolved’ ) if $ServerCreated < $Created;

Landon Stewart LandonStewart@Gmail.com

The help I need though is on comparing dates using RT::Date or
something. If I load the CF.{ServerCreated} and the Created value how
do I compare them reliably in modules?

Would this work?

my $ServerCreated = DateTime::Format::MySQL->parse_datetime(
$ticket->FirstCustomFieldValue(‘ServerCreated’) );
my $Created = DateTime::Format::MySQL->parse_datetime(
$ticketObj->Created );
$ticket->SetStatus( ‘resolved’ ) if $ServerCreated < $Created;

my $server_created = RT::Date->new( $ticket->CurrentUser );
$server_created->Set(
Format => ‘sql’,
Value => $ticket->FirstCustomFieldValue(‘ServerCreated’),
);

if ($server_created->Unix < $ticket->CreatedObj->Unix) {

}

The help I need though is on comparing dates using RT::Date or
something. If I load the CF.{ServerCreated} and the Created value how
do I compare them reliably in modules?

Would this work?

my $ServerCreated = DateTime::Format::MySQL->parse_datetime(
$ticket->FirstCustomFieldValue(‘ServerCreated’) );
my $Created = DateTime::Format::MySQL->parse_datetime(
$ticketObj->Created );
$ticket->SetStatus( ‘resolved’ ) if $ServerCreated < $Created;

my $server_created = RT::Date->new( $ticket->CurrentUser );
$server_created->Set(
Format => ‘sql’,
Value => $ticket->FirstCustomFieldValue(‘ServerCreated’),
);

if ($server_created->Unix < $ticket->CreatedObj->Unix) {

}

Oh niiice. Thanks Thomas!

Landon Stewart LandonStewart@Gmail.com