stats - otrs - OTRS mailing lists

newer
Re: [otrs] Feature request:...

stats

older
Customer users Access Control

Strange, PJ (Philippa)

2 Oct 2003 2 Oct '03

8:04 a.m.

Hi All Is it possible either now or sometime in the near future to have queue specific stats built in to the existing stats offering? Thanks Philippa Strange

Attachments:

attachment.html (text/html — 1000 bytes)

Reply

Sign in to reply online Use email software

Show replies by date

Robert Kehl

8 Oct 8 Oct

2:44 p.m.

On Thursday, October 02, 2003 10:04 AM Strange, PJ (Philippa) wrote:

...
Is it possible either now or sometime in the near future to have queue specific stats built in to the existing stats offering?

It were possible right now by hacking otrs/bin/mkStats.pl, and is a good idea for the future if not already noted somewhere in the developers' cookbook. This is quick'n'dirty'n'hardcoded, but working - you'll get the idea. Original SQL statement for counting the values, line 210ff: my $SQL = "SELECT count(*) FROM ticket_history " . " WHERE " . " history_type_id = $State " . " AND " . " create_time >= '$Year-$Month-$StartDate 00:00:01'". " AND " . " create_time <= '$Year-$Month-$StartDate 23:59:59'"; New code to do the stats just for the queue with id=1, which is 'Postmaster' at least on my system: my $SQL = "SELECT count(*) FROM ticket_history, ticket " . " WHERE " . " history_type_id = $State " . " AND " . " ticket_history.ticket_id = ticket.id " . " AND " . " ticket.queue_id = 1 " . " AND " . " ticket_history.create_time >= '$Year-$Month-$StartDate 00:00:01'". " AND " . " ticket_history.create_time <= '$Year-$Month-$StartDate 23:59:59'"; Watch the additional table prefixes. They are needed because we're now combining two tables partially containing the same field names (create_time). If one doesn't (want to) know the queue id, we have to fetch the real name from a third table, the 'queue' itself: my $SQL = "SELECT count(*) FROM ticket_history, ticket, queue " . " WHERE " . " history_type_id = $State " . " AND " . " ticket_history.ticket_id = ticket.id " . " AND " . " ticket.queue_id = queue.id " . " AND " . " queue.name = 'Postmaster' " . " AND " . " ticket_history.create_time >= '$Year-$Month-$StartDate 00:00:01'". " AND " . " ticket_history.create_time <= '$Year-$Month-$StartDate 23:59:59'"; You may enter the above *after* the original $SQL build statement (ie. from line 217 on, shoving the rest downwards) to easily and errorless revert to the previous version. Watch out for line breaks due to mailer formatting - sorry about that. Yes, this can be extended to do other stats as well. Remember that calculating the statistic values can sometime produce a system load quite unwanted on a production machine, depending on the number of tickets and the other use of the DB. So it would be best to calculate stats during general maintenance times, mostly at night. hth, Robert Kehl

Reply

Sign in to reply online Use email software

Graham Smith

9 Oct 9 Oct

4:56 p.m.

I have just finished customising the SystemStats,pm and System.pm modules and the SystemStats.dtl file to provide a queue based breakdown of current ticket states for our OTRS setup. Not going to anything with the graphics side as I am not sure that a queue based output would be very usable for our purpose. My customisation does not break these down by date at moment but I would probably optimise such a query by copying the date constrained result records to an intermediate table and then run queue based or user based queries against the intermediate table for multiple queries. This would probably be a bit more efficient than a single query for each pass. I think to some extent the performance issue could be mitigated by peroidically archiving closed records and storing reporting snapshots. I dont know whether adding specific support for this is on the To Do for OTRS but it could be useful. On Wed, 2003-10-08 at 15:44, Robert Kehl wrote:

On Thursday, October 02, 2003 10:04 AM Strange, PJ (Philippa) wrote:

...
...
Is it possible either now or sometime in the near future to have queue specific stats built in to the existing stats offering?

It were possible right now by hacking otrs/bin/mkStats.pl, and is a good idea for the future if not already noted somewhere in the developers' cookbook.

This is quick'n'dirty'n'hardcoded, but working - you'll get the idea. Original SQL statement for counting the values, line 210ff:

my $SQL = "SELECT count(*) FROM ticket_history " . " WHERE " . " history_type_id = $State " . " AND " . " create_time >= '$Year-$Month-$StartDate 00:00:01'". " AND " . " create_time <= '$Year-$Month-$StartDate 23:59:59'";

Reply

Sign in to reply online Use email software

7953

Age (days ago)

7960

Last active (days ago)

Download

2 comments

3 participants

tags

participants (3)

Graham Smith
Robert Kehl
Strange, PJ (Philippa)