So just a quick question so that I can put my mind to rest. Why is the
ticket count for created tickets on any one particular day different when
you compare the data in the database to what’s coming out of the API?
For example:
On a particular day in one of our queues the rt CLI command
rt ls -i “Queue = ‘support’ and Created >= ‘2007-08-23 00:00:00’ and Created
<= ‘2007-08-23 23:59:59’” | wc -l
will return 12 as the count of tickets. Now if I were to take into account
that the database stores datetime in localtime but the API wants to use UTC
for the days
rt ls -i “Queue = ‘support’ and Created >= ‘2007-08-22 17:00:00’ and Created
<= ‘2007-08-23 16:59:59’” | wc -l (assuming I am doing the math right as the
database is using PDT (GMT -7) )
will return 12 as well (coincidentally).
Now if I query the database with a similar query
select count(*) from Tickets where Created >= ‘2007-08-23 00:00:00’ and
Created <= ‘2007-08-23 23:59:59’ and Queue =1; (where support == 1 for
Queue)
I get 19 records. If I modify the record for UTC
select count(*) from Tickets where Created >= ‘2007-08-22 17:00:00’ and
Created <= ‘2007-08-23 16:59:59’ and Queue =1;
I get 25 records.
I am assuming some filtering is going on here, but I wonder why that is. If
I want a count of tickets created on one day (UTC conversions aside)
shouldn’t the database record counts and the RT API results match? Is it
only considering tickets that are active? Is it removing resolved tickets?
Is there a table join going on that is restricting the results?
This may be very basic but I haven’t been able to find it anywhere in the RT
wiki or lists.
Thanks,
Shawn Scantland
So just a quick question so that I can put my mind to rest. Why is
the ticket count for created tickets on any one particular day
different when you compare the data in the database to what’s
coming out of the API?
will return 12 as the count of tickets. Now if I were to take into
account that the database stores datetime in localtime but the API
wants to use UTC for the days
RT stores ALL dates as UTC in the database. My first guess would be
ACLs (are you root? Are there deleted tickets?)
Have you compared the missing tickets to see if there are commonalities?
-j
PGP.sig (186 Bytes)
Hi Jesse,
RT stores ALL dates as UTC in the database. My first guess would be
ACLs (are you root? Are there deleted tickets?)
Have you compared the missing tickets to see if there are commonalities?
Thanks for getting back to me so soon. I have connected as both root and
myself when querying this queue and the results were the same for the
queries. Now I have modified my mysql query to:
select count(*) from Tickets where Created >= ‘2007-08-23 04:00’ and Created
<= ‘2007-08-24 03:59:59’ and Queue = 1 and Status != ‘deleted’ and !(Status
= ‘resolved’ and Resolved = ‘1970-01-01 00:00:00’);
where a queue of 1 is the support queue. This query gives me the same list
of tickets as the RT Ticket Query results. The results also match the calls
from the RT CLI.
I have noticed that there are several tickets that have a status of resolved
but not have a Resolved datetime other than the default. Oddly doing either
a status update via “The Basics” options or selecting the Resolve link at
the top of the ticket page both show me a datetime for Resolved in the
database (and UI). So it is interesting how we can have tickets with that
situation. The version of RT at work and my local copy are both 3.4.4. The
data in question is recent but I have not been able to replicate the
situation.
Now I can get the RT Ticket Query and mysql to agree on the results but
RTx::Statistics is returning different values. Does RTx::Statistics gather
the data differently? In specific looking at the ‘Time to Resolve’ I get
values that are nothing like what the 3 other options above will show me for
the count of Created tickets.
Thanks,
Shawn
Now I can get the RT Ticket Query and mysql to agree on the results but RTx::Statistics is returning different
values. Does RTx::Statistics gather the data differently? In specific looking at the ‘Time to Resolve’ I get
values that are nothing like what the 3 other options above will show me for the count of Created tickets.
Thanks,
Shawn
Unfortunately, RTx::Statistics hasn’t been updated in quite awhile. I simply haven’t had any time this year to work on it, and it doesn’t really appear that anyone else has either. I have several patches sitting in my inbox because I haven’t had time to evaluate them, but even those are close to a year old.
-Kelly