I'm trying to create a Year-to-Date report in KACE which lists all tickets that wre closed within the last year. I know I can do this using the wizard by configuring the "is within last" filter, however this approach will not be cumulative. Each time I run the report it will only pull tickets starting one year ago from that point in time. In other words, I need the option to replace "is within last" with a specific date so that the report is always beginning with the same start date. I'm not great with SQL but If someone can help me with the SQL I can probably figure it out.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

3

Here's the where clause I used for a similar report on average time to ticket close. You just change the date range to what you want.

 

where HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2013-02-01' /*change the start date here*/
and TIME_CLOSED<'2013-02-28' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/

Answered 05/29/2013 by: lmland
Tenth Degree Black Belt

  • Thanks. I'd be interested in the seeing the whole code for your avg time to close if you're able to post it. I need to build a similar report for my environment. Right now im just exporting raw data into a .csv and then calculating the averages from there so that I can make pie charts and graphs for my management. If you have a better way of doing it I'd love to hear it.
  • This query is for Average Ticket Time to Close by Location. Location is a custom field I created for campuses/buildings in our school district. I have another one that is average ticket time to close by owner.

    SELECT HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION,
    (case when
    TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),
    INTERVAL 1 HOUR) then '0-1 hour'
    when
    TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL
    24 HOUR) then '1-24 hours'
    when
    TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL
    24 HOUR) then '>24 hours'
    else 'error' end ) as CLOSE_GROUP,
    count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
    CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
    div 86400),'d ',
    TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
    from HD_TICKET
    JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    where HD_STATUS.NAME='closed'
    and TIME_CLOSED<>0
    and TIME_CLOSED>'2013-02-01' /*change the start date here*/
    and TIME_CLOSED<'2013-02-28' /*change the end date here*/
    and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
    GROUP BY LOCATION
Please log in to comment

Answers

1

Can you paste what you currently have? I don't use the service desk feature, but I can modify what you have.

Answered 05/29/2013 by: dugullett
Red Belt

Please log in to comment
1
SELECT HD_TICKET.ID, HD_PRIORITY.NAME AS PRIORITY, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TITLE, HD_CATEGORY.NAME AS CATEGORY, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, HD_TICKET.TIME_OPENED, HD_TICKET.CREATED  FROM HD_TICKET  JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 1 YEAR)))  ORDER BY OWNER_NAME, HD_PRIORITY.ORDINAL
Answered 05/29/2013 by: svargas
Senior Yellow Belt

Please log in to comment
1

Sorry. Here it is in a slightly easier to read format.

 

SELECT HD_TICKET.ID, HD_PRIORITY.NAME AS PRIORITY, S.FULL_NAME AS SUBMITTER_NAME, HD_TICKET.TITLE, HD_CATEGORY.NAME AS CATEGORY, O.FULL_NAME AS OWNER_NAME, HD_STATUS.NAME AS STATUS, HD_TICKET.TIME_OPENED, HD_TICKET.CREATED  FROM HD_TICKET  JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 5) AND ((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 1 YEAR)))  ORDER BY OWNER_NAME, HD_PRIORITY.ORDINAL

Answered 05/29/2013 by: svargas
Senior Yellow Belt

  • Well I was hoping it would have been a little more obvious, but it looks like it's not going to. I guess I need to build out a dummy service desk to test this kind of stuff.
Please log in to comment
0

There are two ways you can approach this, you can change the report to still be relative to this year, or you can change it to be a static report for the year 2013.

To make the report relative for the current year replace this text from the query:

((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 1 YEAR)))

With this:

YEAR(HD_TICKET.CREATED) = YEAR(NOW())

For a static report for 2013, use this instead:

YEAR(HD_TICKET.CREATED) = 2013

 

 

Answered 05/30/2013 by: chucksteel
Red Belt

  • I'm going to try this now. I'll let you know how it works.
Please log in to comment
Answer this question or Comment on this question for clarity