[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 <ahall at autodist.com> 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 <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
> 
> 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 <mzagrabe at d.umn.edu <mailto:mzagrabe at d.umn.edu>> wrote:
> Hi Alex,
> 
> On Mon, Dec 19, 2016 at 12:54 PM, Alex Hall <ahall at autodist.com <mailto:ahall at autodist.com>> 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
> ahall at autodist.com <mailto:ahall at autodist.com>
> 
> 
> 
> -- 
> Alex Hall
> Automatic Distributors, IT department
> ahall at autodist.com <mailto:ahall at autodist.com>

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