I am new to using Kbox and have been asked by my manager to create a monthly report. I have managed get most of the stats needed but would like to add the length of time from an incident being create until the point of being closed.  I can see that the stat is on the ticket but i can not see any options in the reporting options to add it to the reports.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

0

This requires a little bit of custom SQL work.  The code needed is:

(time_to_sec(timediff(HD_TICKET.TIME_CLOSED,HD_TICKET.CREATED))/3600)

This will give you hours opened on a closed ticket.  Keep in mind that if you have tickets that get reopened this can give a false inpression as a ticket created on 6/1, closed on 6/2, and reopened on 6/30 (say, by a user replying to the closed email) will show an opened time of 30 days.  Also, for a ticket that is still opened, the HD_TICKET.TIME_CLOSED is empty.  You need to restrict your report to closed tickets via the where clause, or, use a CASE statement to return (time_to_sec(timediff(NOW(),HD_TICKET.CREATED))/3600) for tickets still open.

Answered 09/03/2013 by: gregekeys
White Belt

  • Thanks for that, inputting this and it is saying there is an error in the code. Any thoughts?

    SELECT
    HD_CATEGORY.NAME AS CATEGORY,
    HD_TICKET.CREATED AS HD_TICKET_CREATED,
    HD_IMPACT.NAME AS IMPACT,
    O.FULL_NAME AS OWNER_NAME,
    HD_PRIORITY.NAME AS PRIORITY,
    S.FULL_NAME AS SUBMITTER_NAME,
    HD_TICKET.TIME_CLOSED AS HD_TICKET_TIME_CLOSED,
    HD_TICKET.TIME_OPENED AS HD_TICKET_TIME_OPENED,
    HD_TICKET.TITLE AS HD_TICKET_TITLE,
    HD_TICKET.CUSTOM_FIELD_VALUE0 AS HD_TICKET_CUSTOM_FIELD_VALUE0,
    HD_TICKET.CUSTOM_FIELD_VALUE1 AS HD_TICKET_CUSTOM_FIELD_VALUE1,

    (time_to_sec(timeddiff(HD_TICKET.TIME_CLOSED,HD_TICKET.CREATED))/3600) AS DURATION

    FROM

    HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    JOIN HD_IMPACT ON (HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID)
    LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
    JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
    LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)

    WHERE
    (
    HD_TICKET.HD_QUEUE_ID = 1)
    AND
    (
    (DATE(HD_TICKET.CREATED)> DATE_SUB(NOW(), INTERVAL 1 month)
    AND
    DATE(HD_TICKET.CREATED)<= NOW()
    )
    )

    ORDER BY
    O.FULL_NAME asc,
    HD_PRIORITY.ORDINAL asc,
    HD_TICKET.CREATED asc,
    HD_TICKET.TIME_CLOSED asc,
    HD_TICKET.TITLE asc,
    S.FULL_NAME asc,
    HD_IMPACT.NAME asc,
    HD_CATEGORY.NAME asc,
    HD_TICKET.CUSTOM_FIELD_VALUE0 asc,
    HD_TICKET.CUSTOM_FIELD_VALUE1 asc
  • You have 2 D's in timediff (you have timeddiff)
Please log in to comment
Answer this question or Comment on this question for clarity