On Thursday, October 02, 2003 10:04 AM Strange, PJ (Philippa) <[EMAIL PROTECTED]> 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 _______________________________________________ OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs Support oder Consulting für Ihr OTRS System? => http://www.otrs.de/
