Scripting : K1000 Service Desk Reporting Question

K1000 Service Desk Reporting Question

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

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? - kwadley 10 years ago

Answers (1)

Posted by: chucksteel 10 years ago

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

Comments:

Chuck, new to ITNinja and this is my first post. Thank you for the quick, thorough, response! - kwadley 10 years ago
- 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 - kwadley 10 years ago
  - 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 - chucksteel 10 years ago
  - 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? - kwadley 10 years ago
  - 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' - chucksteel 10 years ago

Answer this question

Posted by:

View more:

Scripting Questions

question