Re: [rt-users] LastUpdated for tickets not working?

[Aaron McCormack]

Nevermind, I tried this in testing on 4.2 and it didn't fix it.  I was thinking 
perhaps the human readable parsing was changing how the date was considered, 
but it doesn't seem to be the case.

Aaron 
> On Dec 19, 2016, at 3:16 PM, Aaron McCormack  wrote:
> 
> Alex, could it be an accidental reversal of > and < ?
> 
> I think LastUpdated >= '3 days ago' is more of the intent because you were 
> surprised of seeing tickets which were updated within the past day.  
> 
> Aaron
> 
> 
>> On Dec 19, 2016, at 12:32 PM, Alex Hall > > wrote:
>> 
>> Well, I found something that works. It's not UntouchedInHours, but this 
>> query seems to return what I want:
>> 
>> select id
>> from Tickets
>> where LastUpdated <= (now() - INTERVAL 10 DAYS);
>> 
>> I still have to work out how to email ticket owners, but at least I can get 
>> the right tickets now. Odd that the other way doesn't work. How exactly does 
>> this "10 days ago" syntax get interpreted? Or is it no longer supported?
>> 
>> On Mon, Dec 19, 2016 at 2:48 PM, Alex Hall > > wrote:
>> I'm using the Crontool, yes, but I've also been doing searches on the web 
>> interface to see if I could get this to work. My Crontool syntax is 
>> something like:
>> 
>> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
>> --search-arg "status != 'resolved' and LastUpdated <= '3 days ago'" \
>> --action RT::Action \
>> --verbose
>> 
>> Or:
>> 
>> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
>> --search-arg "status != 'resolved'" \
>> --condition RT::Condition::UntouchedInHours \
>> --arg-condition 72 \
>> --verbose
>> 
>> This shouldn't be modifying tickets, yet I'm seeing tickets created hours or 
>> minutes ago appearing in the results. Same for my RT web searches for 
>> similar SQL to what's above.
>> 
>> I'm in the database now, looking at the Tickets table and messing with 
>> queries. I just tried this, but got an empty set:
>> 
>> select id, LastUpdated
>> from Tickets
>> where LastUpdated <= '3 days ago'
>> order by LastUpdated DESC
>> limit 10;
>> 
>> I'm not surprised I got nothing, as I imagine the '3 days ago' syntax is 
>> something RT interprets before giving the query to the database engine. 
>> Still, it was worth a shot. I'm now refreshing my knowledge of date math in 
>> MySQL so I can query exactly what I want, but I hoped UntouchedInHours would 
>> do all that for me. Oh, and yes, LastUpdated does seem to have normal values 
>> in it. They're in GMT time, but they seem to be correct.
>> 
>> On Mon, Dec 19, 2016 at 2:37 PM, Matt Zagrabelny > > wrote:
>> Hi Alex,
>> 
>> On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall > > wrote:
>> > Hello all,
>> > I'm trying to get stale ticket alerts working, so am using the
>> > UntouchedInHours condition. I've also tried, in SQL:
>> > LastUpdated <= '2 days ago'
>> > and similar searches. Yet, I always get the same number of tickets as I get
>> > when I leave the date restriction off completely, and UntouchedInHours is
>> > always giving me tickets opened today.
>> 
>> I assume you are using rtcrontool. Correct?
>> 
>> How are you alerting?
>> 
>> Is the alerting actually "touching" the tickets thus affecting the query?
>> 
>> You can query the tickets table directly and see the lastupdated
>> field. See if that field changes how you would expect when you "touch"
>> or "update" a ticket.
>> 
>> -m
>> 
>> 
>> 
>> -- 
>> Alex Hall
>> Automatic Distributors, IT department
>> ah...@autodist.com 
>> 
>> 
>> 
>> -- 
>> Alex Hall
>> Automatic Distributors, IT department
>> ah...@autodist.com 
> 

Re: [rt-users] LastUpdated for tickets not working?

[Aaron McCormack]

Alex, could it be an accidental reversal of > and < ?

I think LastUpdated >= '3 days ago' is more of the intent because you were 
surprised of seeing tickets which were updated within the past day.  

Aaron


> On Dec 19, 2016, at 12:32 PM, Alex Hall  wrote:
> 
> Well, I found something that works. It's not UntouchedInHours, but this query 
> seems to return what I want:
> 
> select id
> from Tickets
> where LastUpdated <= (now() - INTERVAL 10 DAYS);
> 
> I still have to work out how to email ticket owners, but at least I can get 
> the right tickets now. Odd that the other way doesn't work. How exactly does 
> this "10 days ago" syntax get interpreted? Or is it no longer supported?
> 
> On Mon, Dec 19, 2016 at 2:48 PM, Alex Hall  > wrote:
> I'm using the Crontool, yes, but I've also been doing searches on the web 
> interface to see if I could get this to work. My Crontool syntax is something 
> like:
> 
> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
> --search-arg "status != 'resolved' and LastUpdated <= '3 days ago'" \
> --action RT::Action \
> --verbose
> 
> Or:
> 
> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
> --search-arg "status != 'resolved'" \
> --condition RT::Condition::UntouchedInHours \
> --arg-condition 72 \
> --verbose
> 
> This shouldn't be modifying tickets, yet I'm seeing tickets created hours or 
> minutes ago appearing in the results. Same for my RT web searches for similar 
> SQL to what's above.
> 
> I'm in the database now, looking at the Tickets table and messing with 
> queries. I just tried this, but got an empty set:
> 
> select id, LastUpdated
> from Tickets
> where LastUpdated <= '3 days ago'
> order by LastUpdated DESC
> limit 10;
> 
> I'm not surprised I got nothing, as I imagine the '3 days ago' syntax is 
> something RT interprets before giving the query to the database engine. 
> Still, it was worth a shot. I'm now refreshing my knowledge of date math in 
> MySQL so I can query exactly what I want, but I hoped UntouchedInHours would 
> do all that for me. Oh, and yes, LastUpdated does seem to have normal values 
> in it. They're in GMT time, but they seem to be correct.
> 
> On Mon, Dec 19, 2016 at 2:37 PM, Matt Zagrabelny  > wrote:
> Hi Alex,
> 
> On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall  > wrote:
> > Hello all,
> > I'm trying to get stale ticket alerts working, so am using the
> > UntouchedInHours condition. I've also tried, in SQL:
> > LastUpdated <= '2 days ago'
> > and similar searches. Yet, I always get the same number of tickets as I get
> > when I leave the date restriction off completely, and UntouchedInHours is
> > always giving me tickets opened today.
> 
> I assume you are using rtcrontool. Correct?
> 
> How are you alerting?
> 
> Is the alerting actually "touching" the tickets thus affecting the query?
> 
> You can query the tickets table directly and see the lastupdated
> field. See if that field changes how you would expect when you "touch"
> or "update" a ticket.
> 
> -m
> 
> 
> 
> -- 
> Alex Hall
> Automatic Distributors, IT department
> ah...@autodist.com 
> 
> 
> 
> -- 
> Alex Hall
> Automatic Distributors, IT department
> ah...@autodist.com 

Re: [rt-users] LastUpdated for tickets not working?

[Landon Stewart]

On Dec 19, 2016, at 11:48 AM, Alex Hall 
> wrote:

I'm using the Crontool, yes, but I've also been doing searches on the web 
interface to see if I could get this to work. My Crontool syntax is something 
like:

/opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
--search-arg "status != 'resolved' and LastUpdated <= '3 days ago'" \
--action RT::Action \
--verbose


LastUpdated can change for nearly any change to the ticket if that change is 
recorded (ie. RecordTransaction => 1) like a Status change or an update to the 
watchers etc.  The one you might want to use instead is LastTold which, to my 
knowledge, is when the last correspondence was made.  I may be mistaken here 
though but I think that's what LastTold is for.  Try using LastTold instead of 
LastUpdated.

I'm in the database now, looking at the Tickets table and messing with queries. 
I just tried this, but got an empty set:

select id, LastUpdated from Tickets where LastUpdated <= '3 days ago' order by 
LastUpdated DESC limit 10;

I'm not surprised I got nothing, as I imagine the '3 days ago' syntax is 
something RT interprets before giving the query to the database engine. Still, 
it was worth a shot. I'm now refreshing my knowledge of date math in MySQL so I 
can query exactly what I want, but I hoped UntouchedInHours would do all that 
for me. Oh, and yes, LastUpdated does seem to have normal values in it.

Yes - '3 days ago' is interpreted specially by a module used by RT for things 
like rt-crontool and the search UI within RT.   MySQL has no idea how to 
interpret that though so it won't work there.

They're in GMT time, but they seem to be correct.

All date/time values stored by MySQL are GMT.  One must convert them to GMT 
before storage and convert them to the user's timezone when displaying them.  
RT is aware of this and already takes care of it assuming your time zone 
settings are correct on the server.

--
Landon Stewart
Lead Analyst - Abuse and Security Management
INTERNAP ®
 lstew...@internap.com
 www.internap.com

Re: [rt-users] LastUpdated for tickets not working?

[Alex Hall]

Well, I found something that works. It's not UntouchedInHours, but this
query seems to return what I want:

select id
from Tickets
where LastUpdated <= (now() - INTERVAL 10 DAYS);

I still have to work out how to email ticket owners, but at least I can get
the right tickets now. Odd that the other way doesn't work. How exactly
does this "10 days ago" syntax get interpreted? Or is it no longer
supported?

On Mon, Dec 19, 2016 at 2:48 PM, Alex Hall  wrote:

> I'm using the Crontool, yes, but I've also been doing searches on the web
> interface to see if I could get this to work. My Crontool syntax is
> something like:
>
> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
> --search-arg "status != 'resolved' and LastUpdated <= '3 days ago'" \
> --action RT::Action \
> --verbose
>
> Or:
>
> /opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
> --search-arg "status != 'resolved'" \
> --condition RT::Condition::UntouchedInHours \
> --arg-condition 72 \
> --verbose
>
> This shouldn't be modifying tickets, yet I'm seeing tickets created hours
> or minutes ago appearing in the results. Same for my RT web searches for
> similar SQL to what's above.
>
> I'm in the database now, looking at the Tickets table and messing with
> queries. I just tried this, but got an empty set:
>
> select id, LastUpdated
> from Tickets
> where LastUpdated <= '3 days ago'
> order by LastUpdated DESC
> limit 10;
>
> I'm not surprised I got nothing, as I imagine the '3 days ago' syntax is
> something RT interprets before giving the query to the database engine.
> Still, it was worth a shot. I'm now refreshing my knowledge of date math in
> MySQL so I can query exactly what I want, but I hoped UntouchedInHours
> would do all that for me. Oh, and yes, LastUpdated does seem to have normal
> values in it. They're in GMT time, but they seem to be correct.
>
> On Mon, Dec 19, 2016 at 2:37 PM, Matt Zagrabelny 
> wrote:
>
>> Hi Alex,
>>
>> On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall  wrote:
>> > Hello all,
>> > I'm trying to get stale ticket alerts working, so am using the
>> > UntouchedInHours condition. I've also tried, in SQL:
>> > LastUpdated <= '2 days ago'
>> > and similar searches. Yet, I always get the same number of tickets as I
>> get
>> > when I leave the date restriction off completely, and UntouchedInHours
>> is
>> > always giving me tickets opened today.
>>
>> I assume you are using rtcrontool. Correct?
>>
>> How are you alerting?
>>
>> Is the alerting actually "touching" the tickets thus affecting the query?
>>
>> You can query the tickets table directly and see the lastupdated
>> field. See if that field changes how you would expect when you "touch"
>> or "update" a ticket.
>>
>> -m
>>
>
>
>
> --
> Alex Hall
> Automatic Distributors, IT department
> ah...@autodist.com
>



-- 
Alex Hall
Automatic Distributors, IT department
ah...@autodist.com

Re: [rt-users] LastUpdated for tickets not working?

[Alex Hall]

I'm using the Crontool, yes, but I've also been doing searches on the web
interface to see if I could get this to work. My Crontool syntax is
something like:

/opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
--search-arg "status != 'resolved' and LastUpdated <= '3 days ago'" \
--action RT::Action \
--verbose

Or:

/opt/rt4/bin/rt-crontool --search RT::Search::FromSQL \
--search-arg "status != 'resolved'" \
--condition RT::Condition::UntouchedInHours \
--arg-condition 72 \
--verbose

This shouldn't be modifying tickets, yet I'm seeing tickets created hours
or minutes ago appearing in the results. Same for my RT web searches for
similar SQL to what's above.

I'm in the database now, looking at the Tickets table and messing with
queries. I just tried this, but got an empty set:

select id, LastUpdated
from Tickets
where LastUpdated <= '3 days ago'
order by LastUpdated DESC
limit 10;

I'm not surprised I got nothing, as I imagine the '3 days ago' syntax is
something RT interprets before giving the query to the database engine.
Still, it was worth a shot. I'm now refreshing my knowledge of date math in
MySQL so I can query exactly what I want, but I hoped UntouchedInHours
would do all that for me. Oh, and yes, LastUpdated does seem to have normal
values in it. They're in GMT time, but they seem to be correct.

On Mon, Dec 19, 2016 at 2:37 PM, Matt Zagrabelny  wrote:

> Hi Alex,
>
> On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall  wrote:
> > Hello all,
> > I'm trying to get stale ticket alerts working, so am using the
> > UntouchedInHours condition. I've also tried, in SQL:
> > LastUpdated <= '2 days ago'
> > and similar searches. Yet, I always get the same number of tickets as I
> get
> > when I leave the date restriction off completely, and UntouchedInHours is
> > always giving me tickets opened today.
>
> I assume you are using rtcrontool. Correct?
>
> How are you alerting?
>
> Is the alerting actually "touching" the tickets thus affecting the query?
>
> You can query the tickets table directly and see the lastupdated
> field. See if that field changes how you would expect when you "touch"
> or "update" a ticket.
>
> -m
>



-- 
Alex Hall
Automatic Distributors, IT department
ah...@autodist.com

Re: [rt-users] LastUpdated for tickets not working?

[Matt Zagrabelny]

Hi Alex,

On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall  wrote:
> Hello all,
> I'm trying to get stale ticket alerts working, so am using the
> UntouchedInHours condition. I've also tried, in SQL:
> LastUpdated <= '2 days ago'
> and similar searches. Yet, I always get the same number of tickets as I get
> when I leave the date restriction off completely, and UntouchedInHours is
> always giving me tickets opened today.

I assume you are using rtcrontool. Correct?

How are you alerting?

Is the alerting actually "touching" the tickets thus affecting the query?

You can query the tickets table directly and see the lastupdated
field. See if that field changes how you would expect when you "touch"
or "update" a ticket.

-m

[rt-users] LastUpdated for tickets not working?

[Alex Hall]

Hello all,
I'm trying to get stale ticket alerts working, so am using the
UntouchedInHours condition. I've also tried, in SQL:
LastUpdated <= '2 days ago'
and similar searches. Yet, I always get the same number of tickets as I get
when I leave the date restriction off completely, and UntouchedInHours is
always giving me tickets opened today.

Because the condition and my own query both rely on LastUpdated, I'm
starting to suspect that this field may not be working correctly. Is this a
know problem in 4.4.1, or--more probably--am I doing something wrong? I'm
not getting any errors anywhere, now that UntouchedInHours has been fixed
to use RT::Condition and not RT::Condition::Generic, but neither am I
getting the tickets I should be. Thanks for any ideas!

-- 
Alex Hall
Automatic Distributors, IT department
ah...@autodist.com