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