How do I create a report of activity starting from a fixed date?
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.
Answers (5)
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*/
Comments:
-
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. - svargas 11 years ago
-
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 - lmland 11 years ago
Can you paste what you currently have? I don't use the service desk feature, but I can modify what you have.
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
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
Comments:
-
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. - dugullett 11 years ago
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
Comments:
-
I'm going to try this now. I'll let you know how it works. - svargas 11 years ago