I found that the built in reports were rather clunky and unusable. I wrote a perl script to directly query the database to retrieve information that I wanted. I was wrote a script that allowed me to get average time to resolution for tickets if you would like to look at it to get a general starting place check out my reply to an earlier reporting question http://www.mail-archive.com/otrs@otrs.org/msg25019.html .

Here is the breakdown of the tables you will need to look at:

ticket - contains the information about the ticket itself including current owner, create date, subject and other information relating to the current status of the ticket.

ticket_history - contains all the activity for all tickets, including state changes, owner changes, and events (such as email reminders). There is one row created for each event for each ticket so this table contains a huge treasure trove of information.

system_users - contains the users and the user ids that you will need to tie ownership to tickets

queue - contains queue names and ids for to allow you to find tickets for a particular queue in the ticket table.

ticket_state - ties the state id to a state name

With these tables you should be able to generate the report that contains the information you are looking for.

Let me know if you have any other questions.

Keith Pierno
Lulu.com

roshanrh@gmail.com wrote:

Hi,
First of all thanks,

My requirements are:
i need to create a report which would contain the details of a ticket, like: ticket subject, ticket body, creation time, time taken to close ti etc.

My questions:
* can i do this from stats?
* if not how can i achieve this? do i have to modify the OTRS source code? if yes, how can i make this work easy?

thanks & regards
Roshan

On Apr 13, 2009 9:46pm, Keith Pierno <kpierno@lulu.com> wrote:
> Unfortunately, you will need to dive into the backend database and write a script to extract the data you want and format it for excel. If you can give me an idea of the type of information you are after I may be able to help point you in the proper direction.
>
>
>
> Keith Pierno
>
> Lulu.com
>
>
>
> Roshan R Hegde wrote:
>
>
> How can i generate a custom report or a custom excel page in OTRS?
>
>
>
>
>
> --
>
> With Regards
>
>
>
> Roshan R Hegde
>
>
>
>