Data missing in statistics report

[danieldai]

To be honest, the statistics report feature in OTRS is somehow inconvenient to use. What I have done is to export what I could export from the system (stats details as below), import to be an excel sheet and use PivotTable to generate reports I need, e.g. grand totals, ticket # per queue, first response and solution time per agent, per queue, etc.

This approach basically runs well until I found a problem recently. Some exported lines have no information but Number, Ticket#, Age & Title. Other information is failed to be exported into CSV files. Wondering why. This leads to incorrect statistics.

So... I am looking for another solution to generate complete data. I manage to access OTRS mysql database from Navicat. I am basically able to make it using database view by linking couple of tables. Another problem arises. Some important time data, SolutionInMin, FirstResponseInMin, can be exported from OTRS statistics. But I browse every tables but fail to find corresponding fields in correct tables. Tough.

Code: Select all

Stat Details

Stat#:
10017
Title:
[OTRS] All historical tickets (LIB & ELS)
Object:
Ticketlist
Description:
[OTRS] All historical tickets (LIB & ELS)
Format:
CSV
Sum rows:
No
Sum columns:
No
Cache:
No
Valid:
valid
Created:
13/12/2012 17:27:09
Created by:
Daniel DAI
Changed:
13/12/2012 17:33:37
Changed by:
Daniel DAI

X-axis
Attributes to be printed:
Number
Ticket#
Age
Title
Created
Changed
Close Time
Queue
State
Priority
Customer User
CustomerID
Agent/Owner
Responsible
Accounted time
EscalationDestinationIn
EscalationDestinationDate
EscalationTimeWorkingTime
EscalationTime
FirstResponse
FirstResponseInMin
FirstResponseDiffInMin
FirstResponseTimeWorkingTime
FirstResponseTimeEscalation
FirstResponseTimeNotification
FirstResponseTimeDestinationTime
FirstResponseTimeDestinationDate
FirstResponseTime
UpdateTimeEscalation
UpdateTimeNotification
UpdateTimeDestinationTime
UpdateTimeDestinationDate
UpdateTimeWorkingTime
UpdateTime
SolutionTime
SolutionInMin
SolutionDiffInMin
SolutionTimeWorkingTime
SolutionTimeEscalation
SolutionTimeNotification
SolutionTimeDestinationTime
SolutionTimeDestinationDate
SolutionTimeWorkingTime
First Lock
lock
StateType
UntilTime
UnlockTimeout
EscalationResponseTime
EscalationSolutionTime
EscalationUpdateTime
RealTillTimeNotUsed

Value Series
Order by:
Created
Sort sequence:
ascending

Restrictions
Queue:
LIBRARY
Queue1
Queue2
Between 2011-01-01 00:00:00 and 2012-12-31 23:59:59

[danieldai]

btw, seems most of ticket lines missing data are phone tickets, which are manually input into system.