There is an earlier post from me, I've included it below. And there is a readme file within the archive, have you tried those? Regards, Edgar --- Dear all, During the greater part of this year I've been developing a statistics module for OTRS (and Hostnet). Attached you'll find a zip file with the final result. See the README file within for further installation details, requirements, etc. etc. The script is able to produce the following ticket statistics, aggregated on a per-day or per-week basis, between specified dates and in specified queues. The output can be selected as well; graphs, CSV and HTML (tables) are supported. - Number of new tickets (total/calls/e-mails) - Workload (number of calls, in- and outgoing e-mails) efficiency: - E-mail time calculations: Open time Reply time Resolution time efficacy: - Average number of follow-ups/replies per ticket - Number of first time fixes An example graph is attached as workload.gif. On 2/7/06, Simone Girlanda wrote:

may you give us more detailed installation instruction ?

thank you

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.16 A: pas.de.quoi@voila.fr Cc: 'User questions and discussions about OTRS.' Oggetto: [otrs] OTRS statistics

Hi Rémi,

Sure, see the attached file. If you have any questions, drop me an e-mail.

Regards,

Edgar

...

-----Oorspronkelijk bericht----- Van: pas.de.quoi@voila.fr [mailto:pas.de.quoi@voila.fr] Verzonden: dinsdag 7 februari 2006 10:21 Aan: danie@verpakt.com; edgar.meij@gmail.com Onderwerp: OTRS statistics

Hi Edgar, Hi Danie

I use otrs too since june 2003 (1.3.2 at this time but I'm going to update to 2.0.4 soon) and I'm interested in statitics over OTRS database but I'm not an expert in SQL so, as Danie asked, could you possibly send me the zip file , I would really like to have a look at it (the ouputs that you have made meet my goals)

Thanks a lot

Rémi

_______________________________________________ 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/

-- 'An approximate answer to the right question is worth a great deal more than a precise answer to the wrong question'

icon file: icon_vraagtaken.gif is missed It's possible to have a copy of it, or what do you intend to show there ? thank you again Simone -----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Simone Girlanda Inviato: mercoledì 8 febbraio 2006 15.34 A: User questions and discussions about OTRS.org Oggetto: R: R: [otrs] OTRS statistics I have some problems using it: fonts are not present in my system, where may I get them ? or may I use other ? my $GraphFont = '/usr/share/fonts/truetype/DINLt____.ttf'; my $BoldFont = '/usr/share/fonts/truetype/DINBd.ttf'; I tried to use OTRS_stats.pl but I always have this error: "No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006" but there are 31. I think that the problem (if a problem exist) is in this part of the software, but I'm not able to understant / change it: # Put wanted tickets in array (right qID's) if (scalar (@{$Param{QueueID}}) == 0) { # No queues selected, thus select all %NewTickets = %ticket; @{$Param{QueueID}} = keys %queue_ids; } else { foreach my $Key (keys %ticket) { $NewTickets{$Key}++ if (grep {$ticket{$Key}{q_id} == $_} @{$Param{QueueID}}); } } # Print error if there are no tickets within the selcted time interval my $Error = "No tickets created in Queues: "; foreach my $ID (@{$Param{QueueID}}) {$Error .= "$queue_ids{$ID}, ";} $Error .= "between $Param{StartDay}-$Param{StartMonth}-$Param{StartYear} and $Param{EndDay}-$Param{EndMonth}-$Param{EndYear}"; error("$Error") if scalar keys %NewTickets == 0; # Find those tickets which were initially calls (type_id(PhoneCallCustomer)= 12) # Could have done this with a nested SELECT, but this is way faster $sql = qq~ SELECT DISTINCT(t.id) FROM ticket t, -----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.28 A: User questions and discussions about OTRS.org Oggetto: Re: R: [otrs] OTRS statistics There is an earlier post from me, I've included it below. And there is a readme file within the archive, have you tried those? Regards, Edgar --- Dear all, During the greater part of this year I've been developing a statistics module for OTRS (and Hostnet). Attached you'll find a zip file with the final result. See the README file within for further installation details, requirements, etc. etc. The script is able to produce the following ticket statistics, aggregated on a per-day or per-week basis, between specified dates and in specified queues. The output can be selected as well; graphs, CSV and HTML (tables) are supported. - Number of new tickets (total/calls/e-mails) - Workload (number of calls, in- and outgoing e-mails) efficiency: - E-mail time calculations: Open time Reply time Resolution time efficacy: - Average number of follow-ups/replies per ticket - Number of first time fixes An example graph is attached as workload.gif. On 2/7/06, Simone Girlanda wrote:

may you give us more detailed installation instruction ?

thank you

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.16 A: pas.de.quoi@voila.fr Cc: 'User questions and discussions about OTRS.' Oggetto: [otrs] OTRS statistics

Hi Rémi,

Sure, see the attached file. If you have any questions, drop me an e-mail.

Regards,

Edgar

...

-----Oorspronkelijk bericht----- Van: pas.de.quoi@voila.fr [mailto:pas.de.quoi@voila.fr] Verzonden: dinsdag 7 februari 2006 10:21 Aan: danie@verpakt.com; edgar.meij@gmail.com Onderwerp: OTRS statistics

Hi Edgar, Hi Danie

I use otrs too since june 2003 (1.3.2 at this time but I'm going to update to 2.0.4 soon) and I'm interested in statitics over OTRS database but I'm not an expert in SQL so, as Danie asked, could you possibly send me the zip file , I would really like to have a look at it (the ouputs that you have made meet my goals)

Thanks a lot

Rémi

_______________________________________________ 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/

-- 'An approximate answer to the right question is worth a great deal more than a precise answer to the wrong question' _______________________________________________ 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/

Thank you Edgar, SELECT COUNT(DISTINCT(ticket_id))... result is 31 please consider that I can also debug your script using Komodo. I run it with also by command line with options: StartDay=1 StartMonth=1 StartYear=2006 EndDay=1 EndMonth=2 EndYear=2006 Action=Workload Unit=week Output=Graph QueueID=2 width=640 height=480 > test.png The result is the same of running it from browser: Content-Type: image/png ?PNG Content-Type: text/html; charset=ISO-8859-1 Error, No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006 if I don't specify the "QueueID=2" parameters I got another error because the script look for a table that do not exist in my database: #Find all ticket links for First Time Fixes $sql = qq~ SELECT DISTINCT(ticket_id_master) FROM ticket_link ~; $statement = $db_handle->prepare($sql); $statement->execute(); -----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: mercoledì 8 febbraio 2006 22.00 A: 'User questions and discussions about OTRS.org' Oggetto: RE: R: [otrs] OTRS statistics Hi Simone, See attached image.

fonts are not present in my system, where may I get them ? or may I use other ?

my $GraphFont = '/usr/share/fonts/truetype/DINLt____.ttf'; my $BoldFont = '/usr/share/fonts/truetype/DINBd.ttf';

Yes, you can use any font you like.

I tried to use OTRS_stats.pl but I always have this error:

"No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006"

but there are 31.

Can you try running a MySQL query and post the results: USE otrs SELECT COUNT(DISTINCT(ticket_id)) FROM ticket_history WHERE history_type_id=1 AND create_time BETWEEN '2006-01-01' AND '2006-02-01' + INTERVAL 1 DAY; You can also try and select no queues. The script will then simply use all tickets. Regards, Edgar

-----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Namens Simone Girlanda Verzonden: woensdag 8 februari 2006 15:45 Aan: User questions and discussions about OTRS.org Onderwerp: R: R: [otrs] OTRS statistics

icon file:

icon_vraagtaken.gif

is missed

It's possible to have a copy of it, or what do you intend to show there ?

thank you again

Simone

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Simone Girlanda Inviato: mercoledì 8 febbraio 2006 15.34 A: User questions and discussions about OTRS.org Oggetto: R: R: [otrs] OTRS statistics

I have some problems using it:

fonts are not present in my system, where may I get them ? or may I use other ?

my $GraphFont = '/usr/share/fonts/truetype/DINLt____.ttf'; my $BoldFont = '/usr/share/fonts/truetype/DINBd.ttf';

I tried to use OTRS_stats.pl but I always have this error:

"No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006"

but there are 31.

I think that the problem (if a problem exist) is in this part of the software, but I'm not able to understant / change it:

# Put wanted tickets in array (right qID's) if (scalar (@{$Param{QueueID}}) == 0) { # No queues selected, thus select all %NewTickets = %ticket; @{$Param{QueueID}} = keys %queue_ids; } else { foreach my $Key (keys %ticket) { $NewTickets{$Key}++ if (grep {$ticket{$Key}{q_id} == $_} @{$Param{QueueID}}); } }

# Print error if there are no tickets within the selcted time interval my $Error = "No tickets created in Queues: "; foreach my $ID (@{$Param{QueueID}}) {$Error .= "$queue_ids{$ID}, ";} $Error .= "between $Param{StartDay}-$Param{StartMonth}- $Param{StartYear} and $Param{EndDay}-$Param{EndMonth}-$Param{EndYear}"; error("$Error") if scalar keys %NewTickets == 0;

# Find those tickets which were initially calls (type_id(PhoneCallCustomer)= 12) # Could have done this with a nested SELECT, but this is way faster $sql = qq~ SELECT DISTINCT(t.id) FROM ticket t,

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.28 A: User questions and discussions about OTRS.org Oggetto: Re: R: [otrs] OTRS statistics

There is an earlier post from me, I've included it below. And there is a readme file within the archive, have you tried those?

Regards,

Edgar

--- Dear all,

During the greater part of this year I've been developing a statistics module for OTRS (and Hostnet). Attached you'll find a zip file with the final result. See the README file within for further installation details, requirements, etc. etc.

The script is able to produce the following ticket statistics, aggregated on a per-day or per-week basis, between specified dates and in specified queues. The output can be selected as well; graphs, CSV and HTML (tables) are supported.

- Number of new tickets (total/calls/e-mails) - Workload (number of calls, in- and outgoing e-mails)

efficiency: - E-mail time calculations: Open time Reply time Resolution time

efficacy: - Average number of follow-ups/replies per ticket - Number of first time fixes

An example graph is attached as workload.gif.

On 2/7/06, Simone Girlanda wrote:

...

may you give us more detailed installation instruction ?

thank you

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.16 A: pas.de.quoi@voila.fr Cc: 'User questions and discussions about OTRS.' Oggetto: [otrs] OTRS statistics

Hi Rémi,

Sure, see the attached file. If you have any questions, drop me an e- mail.

Regards,

Edgar

...

-----Oorspronkelijk bericht----- Van: pas.de.quoi@voila.fr [mailto:pas.de.quoi@voila.fr] Verzonden: dinsdag 7 februari 2006 10:21 Aan: danie@verpakt.com; edgar.meij@gmail.com Onderwerp: OTRS statistics

Hi Edgar, Hi Danie

I use otrs too since june 2003 (1.3.2 at this time but I'm going to update to 2.0.4 soon) and I'm interested in statitics over OTRS database but I'm not an expert in SQL so, as Danie asked, could you possibly send me the zip file , I would really like to have a look at it (the ouputs that you have made meet my goals)

Thanks a lot

Rémi

_______________________________________________ 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/

-- 'An approximate answer to the right question is worth a great deal more than a precise answer to the wrong question'

_______________________________________________ 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/

_______________________________________________ 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/

When I close a ticket, should otrs send the statements entered into the "closed" ticket window in email to the ticket open/requester? They receive the ticket closed email, but not the statements entered? Any ideas? Thanks Dan Metcalf Wireless Broadband Systems www.wbisp.com 781-577-2780 ext 6021 dan.metcalf@wbisp.com

-----Original Message----- From: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Edgar Meij Sent: Thursday, February 16, 2006 3:10 PM To: 'User questions and discussions about OTRS.org' Subject: RE: R: [otrs] OTRS statistics

Hi Simone,

Sorry for the late response, I've been busy... You can safely delete (or comment out) these lines

#Find all ticket links for First Time Fixes $sql = qq~ SELECT DISTINCT(ticket_id_master) FROM ticket_link ~; $statement = $db_handle->prepare($sql); $statement->execute();

while (@data = $statement->fetchrow_array()) { push (@ticket_link, $data[0]); } $statement->finish;

The only thing is your ticket links won't be taken into account this way.

-- Edgar

...

-----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Namens Simone Girlanda Verzonden: donderdag 9 februari 2006 9:06 Aan: User questions and discussions about OTRS.org Onderwerp: R: R: [otrs] OTRS statistics

Thank you Edgar,

SELECT COUNT(DISTINCT(ticket_id))... result is 31

please consider that I can also debug your script using Komodo.

I run it with also by command line with options:

StartDay=1 StartMonth=1 StartYear=2006 EndDay=1 EndMonth=2 EndYear=2006 Action=Workload Unit=week Output=Graph QueueID=2 width=640 height=480 > test.png

The result is the same of running it from browser:

Content-Type: image/png

?PNG

Content-Type: text/html; charset=ISO-8859-1

Error, No tickets created in Queues: Supporto, between 1-1-2006 and 1-2- 2006

if I don't specify the "QueueID=2" parameters I got another error because the script look for a table that do not exist in my database:

#Find all ticket links for First Time Fixes $sql = qq~ SELECT DISTINCT(ticket_id_master) FROM ticket_link ~; $statement = $db_handle->prepare($sql); $statement->execute();

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: mercoledì 8 febbraio 2006 22.00 A: 'User questions and discussions about OTRS.org' Oggetto: RE: R: [otrs] OTRS statistics

Hi Simone,

See attached image.

...

fonts are not present in my system, where may I get them ? or may I use other ?

my $GraphFont = '/usr/share/fonts/truetype/DINLt____.ttf'; my $BoldFont = '/usr/share/fonts/truetype/DINBd.ttf';

Yes, you can use any font you like.

...

I tried to use OTRS_stats.pl but I always have this error:

"No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006"

but there are 31.

Can you try running a MySQL query and post the results:

USE otrs

SELECT COUNT(DISTINCT(ticket_id)) FROM ticket_history WHERE history_type_id=1 AND create_time BETWEEN '2006-01-01' AND '2006-02-01' + INTERVAL 1 DAY;

You can also try and select no queues. The script will then simply use all tickets.

Regards,

Edgar

...

-----Oorspronkelijk bericht----- Van: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org] Namens Simone Girlanda Verzonden: woensdag 8 februari 2006 15:45 Aan: User questions and discussions about OTRS.org Onderwerp: R: R: [otrs] OTRS statistics

icon file:

icon_vraagtaken.gif

is missed

It's possible to have a copy of it, or what do you intend to show there ?

thank you again

Simone

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Simone Girlanda Inviato: mercoledì 8 febbraio 2006 15.34 A: User questions and discussions about OTRS.org Oggetto: R: R: [otrs] OTRS statistics

I have some problems using it:

fonts are not present in my system, where may I get them ? or may I use other ?

my $GraphFont = '/usr/share/fonts/truetype/DINLt____.ttf'; my $BoldFont = '/usr/share/fonts/truetype/DINBd.ttf';

I tried to use OTRS_stats.pl but I always have this error:

"No tickets created in Queues: Supporto, between 1-1-2006 and 1-2-2006"

but there are 31.

I think that the problem (if a problem exist) is in this part of the software, but I'm not able to understant / change it:

# Put wanted tickets in array (right qID's) if (scalar (@{$Param{QueueID}}) == 0) { # No queues selected, thus select all %NewTickets = %ticket; @{$Param{QueueID}} = keys %queue_ids; } else { foreach my $Key (keys %ticket) { $NewTickets{$Key}++ if (grep {$ticket{$Key}{q_id} == $_} @{$Param{QueueID}}); } }

# Print error if there are no tickets within the selcted time interval my $Error = "No tickets created in Queues: "; foreach my $ID (@{$Param{QueueID}}) {$Error .= "$queue_ids{$ID}, ";} $Error .= "between $Param{StartDay}-$Param{StartMonth}- $Param{StartYear} and $Param{EndDay}-$Param{EndMonth}-$Param{EndYear}"; error("$Error") if scalar keys %NewTickets == 0;

# Find those tickets which were initially calls (type_id(PhoneCallCustomer)= 12) # Could have done this with a nested SELECT, but this is way faster $sql = qq~ SELECT DISTINCT(t.id) FROM ticket t,

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.28 A: User questions and discussions about OTRS.org Oggetto: Re: R: [otrs] OTRS statistics

There is an earlier post from me, I've included it below. And there is a readme file within the archive, have you tried those?

Regards,

Edgar

--- Dear all,

During the greater part of this year I've been developing a statistics module for OTRS (and Hostnet). Attached you'll find a zip file with the final result. See the README file within for further installation details, requirements, etc. etc.

The script is able to produce the following ticket statistics, aggregated on a per-day or per-week basis, between specified dates and in specified queues. The output can be selected as well; graphs, CSV and HTML (tables) are supported.

- Number of new tickets (total/calls/e-mails) - Workload (number of calls, in- and outgoing e-mails)

efficiency: - E-mail time calculations: Open time Reply time Resolution time

efficacy: - Average number of follow-ups/replies per ticket - Number of first time fixes

An example graph is attached as workload.gif.

On 2/7/06, Simone Girlanda wrote:

...

may you give us more detailed installation instruction ?

thank you

-----Messaggio originale----- Da: otrs-bounces@otrs.org [mailto:otrs-bounces@otrs.org]Per conto di Edgar Meij Inviato: martedì 7 febbraio 2006 18.16 A: pas.de.quoi@voila.fr Cc: 'User questions and discussions about OTRS.' Oggetto: [otrs] OTRS statistics

Hi Rémi,

Sure, see the attached file. If you have any questions, drop me an e- mail.

Regards,

Edgar

...

-----Oorspronkelijk bericht----- Van: pas.de.quoi@voila.fr [mailto:pas.de.quoi@voila.fr] Verzonden: dinsdag 7 februari 2006 10:21 Aan: danie@verpakt.com; edgar.meij@gmail.com Onderwerp: OTRS statistics

Hi Edgar, Hi Danie

I use otrs too since june 2003 (1.3.2 at this time but I'm going to update to 2.0.4 soon) and I'm interested in statitics over OTRS database but I'm not an expert in SQL so, as Danie asked, could you possibly send me the zip file , I would really like to have a look at it (the ouputs that you have made meet my goals)

Thanks a lot

Rémi

_______________________________________________ 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/

-- 'An approximate answer to the right question is worth a great deal more than a precise answer to the wrong question'

_______________________________________________ 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/

_______________________________________________ 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/

_______________________________________________ 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/

_______________________________________________ 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/

-- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/262 - Release Date: 02/16/2006

-- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.10/262 - Release Date: 02/16/2006