How to get the K1000 Service Desk "time open" column to show in reports
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.
Answers (1)
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.
Comments:
-
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 - DCHD 10 years ago -
You have 2 D's in timediff (you have timeddiff) - gregekeys 10 years ago