dCSServiceDetails

The main data table used for Customer Service is dCSServiceDetails. Most of the data is collected from the TICKET table. This is what it contains when shipped from us:

Based on (table.column) Type Column alias Localised english name Comment
Basic        
TimeSpent(minutes) C CalculatedColumn Cost (currency) This column calculates the cost based on the time_spent column multiplied by the value of the system variable ej_hour_cost. It's ment to give rough estimate.
ticket.id P id Nr. of tickets Primary key for the ticket table. May be used for counting when grouping is used.
ticket.connect_id P ConnectId ConnectId Tickets may be connected to each other in a hierarchy. This is the ticket_id of the parent record.
ticket.title P Title Title The title of the ticket
ticket.author P Author Contact person A string representing the author of the ticket (same as author of first message).
         
Owner        
ejuser.lastname, ejuser.firstname E OwnedPersonName(Last,First) Ticket owner Name of the owner.
ejuser.firstname, ejuser.lastname E OwnedPersonName(FirstLast) Ticket owner Name of the owner.
hierarchy.fullname E OwnedPersonUserGroup Cost (currency) The owners group.
         
Createdby        
ejuser.lastname, ejuser.firstname E CreatePersonName(Last,First) Created by person Name of creator.
ejuser.firstname, ejuser.lastname E CreatePersonName(FirstLast) Created by person Name of creator.
hierarchy.name E CreatedPersonUsergroup User group (creator) The creators group.
         
Customer        
contact.name E CustCompanyName Contact Name of the company
person.lastname, person.firstname E CustomersPersonName(Last,First) Customer contact Name of the company's contact person
person.firstname, person.lastname E CustomersPersonName(FirstLast) Customer contact Name of the company's contact person
ej_category.name E CategoryName Category CS Category - not the same as category in S&M
category.name E Category(contact) Category (S&M) Category from S&M
business.name E Business(contact) Business (S&M) Business from S&M
         
Status        
ticket.status P Status StatusID The status of the ticket. Only system defined status are included here. If a user-defined status is set on the ticket, then the system defined status it is based upon will be used. Mapping: 1=Open, 2=Closed, 3=Postponed, 4=Deleted, 5=Linked
ticket_status.name P StatusName Status Name of system-defined status in users seslected language
ticket.ticket_status P Status2 User-defined statusID The user-defined status of the ticket - if a system-defined status is selected is will be used.
ticket_status.name P StatusName2 User-defined status Name of user-defined status
         
Priority        
ticket.Priority P Priority PriorityID Id of current priority
ticket_priority.name P PriorityName Priority Name of current priority
ticket.orig_priority P OrigPriority Original Priority ID Id of original priority
ticket_priority.name P OrigPriorityName Original Priority Name of original priority
         
First time Resolution FTR        
ticket.closed_at, ticket.replied_at E FTR(Category) FTR(Category) First Time Resolution indicator. Equals 'FTR' when FTR is detacted, otherwise equals 'Non-FTR'. If closed_at > replied_at then it is considdered to be non-FTR.
ticket.closed_at, ticket.replied_at E FTR(Number) FTR(Number) Works same way as the column above, but returns a float value of 1.0 if FTR is detected, and 0.0 for non-FTR cases. May be used for avg() aggregations to atain FTR ratios.
         
Time/work involved        
ticket.replied_at P RepliedAt First response (date) The datetime for when the ticket was replied to. I.e. the first external message added to the ticket. Unformated date.
ticket.time_to_reply E TimeToReply(Hour) Response time calculated (hours) No. of hours it took to responde to this ticket. Only working hours are counted (as defined in CS).
ticket.time_to_close E TimeToClose(Hour) Closing time calculated (hours) No. of hours it took to close this ticket. Only working hours are counted (as defined in CS).
ticket.real_time_to_reply E RealTimeToReply(Hour) Response time (hours) No. of hours it took to responde to this ticket
ticket.real_time_to_close E RealTimeToClose(Hour) Closing time (hours) No. of hours it took to close this ticket
ticket_log_action.id E NrActions No. of actions No. of actions performed on this ticket
ej_message.id E NrMessages No. of replies to customer No. of messages sent to customer on this tick
    NrRepliesToCustomer Nr. of messages No. of replies sent to customer on this ticket
ticket_log_action.user_id E NrPersons Nr. of people involved No. of persons involved in the processing of this ticket
ej_message.time_spent E TimeSpent(minutes) Time spent on messages (mins) Time spent on messages for this ticket in minutes
ej_message.time_charge E TimeCharge(minutes) Billable time (minutes) Billable time spent on this ticket in minutes
ticket.time_spent_internally P Time spent internally Time spent internally Time spent internally (in seconds)
ticket.time_spent_externally P Time spent externally Time spent externally Time spent externally (in seconds)
ticket.time_spent_queue P Time spent in queue Time spent in queue Time spent in queue (in seconds)
ticket.real_time_spent_internally P Real time spent internally Real time spent internally Time spent internally (in seconds) - only working hours are counted (as defined in CS)
ticket.real_time_spent_externally P Real time spent externally Real time spent externally Time spent externally (in seconds) - only working hours are counted (as defined in CS)
ticket.real_time_spent_queue P Real time spent in queue Real time spent in queue Time spent in queue (in seconds) - only working hours are counted (as defined in CS)
         
Deadline        
ticket.deadline P Deadline Deadline Deadline date and time
ticket.deadline E Deadline(minute) Deadline(minute) No. of minuttes to deadline
ticket.deadline E Deadline(hour) Deadline(hour) No. of hours to deadline
ticket.deadline E Deadline(day) Deadline(day) No. of days to deadline
ticket.deadline E Deadline(week) Deadline(week) No. of weeks to deadline
ticket.status, ticket.deadline E Deadlinepast Deadlinepast For tickets with status open(1) or closed(2) a float is returned. 1.0 is returned if the ticket's deadline is passed. 0.0 is returned if it isn't pased. For tickets with status other than open or closed NULL is returned. Use average on this column and the number will give a metric between 0.0 and 1.0. The closer to 0.0 the better!
         
Age        
ticket.created_at E Age(minute) Age(minute) Age in minutes
ticket.created_at E Age(hour) Age(hour) Age in hours
ticket.created_at E Age(day) Age(day) Age in days
ticket.created_at E Age(week) Age(week) Age in weeks
         
Age group        
ticket.created_at E AgeGroup Age group The ticket is categorized based on its age. By default the categories are defined like this: age < 1 -> '1: 0-1'
age < 3 -> '2: 1-3' 
age < 7 -> '3: 3-7'
age < 14 -> '4: 7-14'
otherwise -> '5: 14+'
The measure is days. This column should be customized for each installation to best suit the needs of the customer. Group by this column to create clear and concise charts.
         
DATE: CreatedAt        
ticket.created_at P CreatedAt Created Date created
ticket.created_at E CreatedAt2 Created2 Used to create pivoted visualizations like This year vs. last year..
         
DATE: ClosedAt        
ticket.closed_at P ClosedAt Closed Closed at
ticket.closed_at E ClosedAt2 Closed2 Used to create pivoted visualizations like This year vs. last year..
         
DATE: LastChanged        
ticket.last_changed P LastChanged Last Changed  Last changed
ticket.last_changed E LastChanged2 Last Changed2 Used to create pivoted visualizations like This year vs. last year..
         
Links        
ticket.id P LinkTicket Link ticket Owl link to ticket view in CS
contact.contact_id P LinkContact Link contact Owl link to contact view in CS
         
Alert        
ticket.alert_level P AlertLevel Alert level The alert level for the ticket. Matches the level value of the ticket_alert table.
ticket.alert_stop P AlertStop Alert escalation (seconds) If the esclatation was stopped, this fields indicates how many seconds are left before the next escalation level. -1 if escalation is running.
ticket.alert_timeout P AlertTimeout Alert timeout date The datetime for when the ticket should jump to the next alert_level.
         
Div.        
ticket.slevel P SLevel Security Level ID The securitylevel of the ticket
ticket.read_status P ReadStatus Owner Read Ticket Whether the owner has read the ticket or not. 1=Has read, 2= and 3=Hasn't been read
ticket.has_attachment P HasAttachment Has attachment Boolean indicating if this ticket has one or more attachments
ticket.read_by_owner P ReadByOwner Last date read by owner The datetime for when the ticket last was read by the owner
ticket.first_read_by_owner P FirstReadByOwner First time read by owner The datetime for when the ticket first was read by the current owner
ticket.first_read_by_user P FirstReadByUser First time read by a user The datetime for when the ticket first was read by a user.
ticket.read_by_customer P ReadByCustomer Read by customer (date) The datetime for when the ticket was read by the customer
ticket.activate P Activate Activation date (postponed) Date that the postponed ticket will be reactivated
         
For column filtering only        
ticket.owned_by P OwnedBy User group ID May be used for column based filtering
ejuser.usergroup P OwnersUsergroupId OwnersUsergroupId May be used for column based filtering
ticket.created_by P CreatedBy Craeted by May be used for column based filtering
ejuser.usergroup P CreatorsUsergroupId CreatorsUsergroupId May be used for column based filtering
ticket.Cust_id P CustId ContactID May be used for column based filtering