[rt-users] LastUpdated for tickets not working?

[Landon Stewart]

On Dec 19, 2016, at 11:48 AM, Alex Hall <ahall at autodist.com<mailto:ahall at autodist.com>> 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 ®
📧 lstewart at internap.com<mailto:lstewart at internap.com>
🌍 www.internap.com<http://www.internap.com>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20161219/907cbd80/attachment.htm>