KACE Product Support

Hi there,

I posted last week a request for a service desk report. I wanted to get time worked by owner on tickets every month.

Result below:

SELECT CONCAT(YEAR(HD_TICKET.TIME_CLOSED), (" "), LPAD(MONTH(HD_TICKET.TIME_CLOSED),2,'0')) AS Periode, FULL_NAME,

            (SUM(ADJUSTMENT_HOURS * 60) + SUM(TIMESTAMPDIFF(MINUTE,

        HD_WORK.START,

        HD_WORK.STOP))) AS total

FROM

    HD_TICKET

        LEFT JOIN

    HD_CATEGORY ON HD_CATEGORY_ID = HD_CATEGORY.ID

        LEFT JOIN

    HD_WORK ON HD_TICKET.ID = HD_WORK.HD_TICKET_ID

        LEFT JOIN

    USER ON USER.ID = HD_TICKET.OWNER_ID

        LEFT JOIN

    HD_STATUS ON HD_STATUS_ID = HD_STATUS.ID

WHERE

    HD_STATUS.STATE = 'closed'

        AND HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 1 YEAR)

GROUP BY Periode, FULL_NAME

I realize after closing my topic that this report count the time the ticket was “on waiting” as it count every step between open and close.

I was wondering if there is an option in the K1000 to not count this time the owner wait for the user to add a comment or anything else?

If not, is it possible to do a report which won’t take into account the time of the state: waiting?

This question because I really have no idea about how to do this.

I just saw these tables: HD_TICKET_CHANGE and HD_TICKET_CHANGE_FIELD which have timestamp and before_value field. Does these field are ok to do what I need?

Finally, if you really don't understood what i'm talking about, do not hesitate to ask again i will try to explain myself differently.

Thank you !