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

1

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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share