I would like to implement a policy change for my helpdesk team.  Basically, I want them to respond, within 4 hours, of ticket assignment.  I want to be able to run a report that shows all tickets that do not have a comment within 4 hours.  We've had KACE deployed for a few months.  We are only using basic funtionality.

1 Comment   [ + ] Show Comment

Comments

  • Awesome, Thanks Chuck! So If I only want to see this on open tickets, I would add: HD_STATUS.STATE = 'opened' to the end of the query? Could I also add the associated technician to the report?
Please log in to comment

Answers

0

This report will show you all tickets where the first change was greater than four hours after it was created:

 SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP as "First Change", FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAMEFROM ORG1.HD_TICKET TJOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")left join HD_STATUS on HD_STATUS_ID = HD_STATUS.IDLEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_IDWHERE TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400and FIRST_CHANGE.ID

This was a little more tricky than I initially expected since KACE adds an entry in the change table as soon as the ticket is created so I filter out that change. I also don't include any system changes to the ticket (user_id = 0).

To just include tickets that are currently open add the following line:

and HD_STATUS.STATE = 'opened'

To just include tickets from the previous day (e.g. you want to run the report in the morning to find tickets from yesterday where the SLA was violated) add this line:

DATE(T.CREATED) = DATE(NOW()) - INTERVAL 1 DAY

Answered 04/15/2014 by: chucksteel
Red Belt

  • Chuck, new to ITNinja and this is my first post. Thank you for the quick, thorough, response!
    • Chuck, I cut and pasted the following:

      SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP as "First Change", FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAMEFROM ORG1.HD_TICKET TJOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")left join HD_STATUS on HD_STATUS_ID = HD_STATUS.IDLEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_IDWHERE TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED))

      This gives the following Syntax error


      mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.HD_TICKET TJOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.' at line 1] in EXECUTE( "SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP as "First Change", FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAMEFROM ORG1.HD_TICKET TJOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")left join HD_STATUS on HD_STATUS_ID = HD_STATUS.IDLEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_IDWHERE TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400and FIRST_CHANGE.ID - See more at: http://www.itninja.com/question/reporting-question-2#sthash.2LVyTq3r.dpuf LIMIT 0")
      Back to Reports List
      Report Detail
      • It looks like the carriage returns got removed at some point when I pasted into ITNinja. See if this works better:
        SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP as "First Change",
        FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAME
        FROM ORG1.HD_TICKET T
        JOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")
        left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
        LEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_ID
        WHERE TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400
        and FIRST_CHANGE.ID
      • Chuck... Thanks... That was it! I tried to add and HD_STATUS.STATE = 'opened' and I get an unknown column error. Is the syntax correct? Also, would it be hard to associate the ticket with the technician?
      • That should be the correct syntax for the HD_STATUS.STATE. Here's a query that includes it and the current ticket owner (technician):

        SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP as "First Change",
        FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAME as "First Change User", OWNER.FULL_NAME as "Owner"
        FROM ORG1.HD_TICKET T
        JOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID
        and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")
        left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
        LEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_ID
        LEFT JOIN USER OWNER on OWNER.ID = T.OWNER_ID
        WHERE TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400 and FIRST_CHANGE.ID
        and HD_STATUS.STATE = 'opened'
Please log in to comment
Answer this question or Comment on this question for clarity