/build/static/layout/Breadcrumb_cap_w.png

Excluding a Ticket Status Through KACE Report

Hello Everybody,

I had a request to exclude the status of "Project" on tickets for a paticular department (Queue 8). I'm not familar with how to exclude anything. Could someone please point me in the right direction on how to do this with the following report?

SELECT
(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, HD_STATUS
where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
group by CLOSE_GROUP

UNION
select 'all' 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
,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) 
and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
group by CLOSE_GROUP

Thanks,

 

Anthony

Asked 11 years ago 1307 views
0 Comments   [ + ] Show comments

Answers (1)

Posted by: jverbosk 11 years ago
Red Belt
1

Try adding this line after "and HD_STATUS.NAME='closed' :

and HD_STATUS.NAME<>'project'

John

Comments:
  • Thanks for that John, I made the change and I'm comparing to verify this works for them.



    Anthony - AnthonyC 11 years ago
  • Hey John,

    As it turns out, it isn't the status which is set to project however instead it is priority. I attempted to change HD_STATUS.NAME<>'project' to HD_PRIORITY.NAME<>'project' but this didn't seem to pass the KACE smell test and errored out. Maybe you could help me with this?

    Thanks,

    Anthony - AnthonyC 11 years ago
  • The reason it doesn't work is because the HD_PRIORITY table isn't specified in the query. I added it in along with what you specified, so try this and see where it gets you.

    John
    ___________________

    SELECT
    (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, HD_STATUS
    where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' 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_TICKET.HD_STATUS=HD_STATUS.ID)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP - jverbosk 11 years ago
    • Hi John, When I attempt to enter what you provided I am getting the error: "mysql error: [1054: Unknown column 'HD_TICKET.HD_STATUS' in 'on clause'] in EXECUTE(". When I look through the code I can't seem to find what is causing this..

      Anthony - AnthonyC 11 years ago
  • The error was due to a typo - should have been HD_TICKET.HD_STATUS_ID in the JOIN statement. Try this.

    John
    _______________________

    SELECT
    (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, HD_STATUS
    where HD_TICKET.HD_STATUS_ID=HD_STATUS.ID and HD_STATUS.NAME='closed' and TIME_CLOSED<>0 AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' 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)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP - jverbosk 11 years ago
    • John, it seems I am getting this same error, but this time with the Queue: "mysql error: [1054: Unknown column 'HD_TICKET.HD_QUEUE' in 'where clause'] in EXECUTE(" - AnthonyC 11 years ago
  • OK, I changed the top part (above UNION) to reflect the same syntax, joins and where filters as the lower part, see if this gets it.

    John
    ____________________

    SELECT
    (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)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW() AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (8) /*add queue numbers here*/
    group by CLOSE_GROUP
    UNION
    select 'all' 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)
    join HD_PRIORITY on (HD_PRIORITY.ID=HD_TICKET.HD_PRIORITY_ID)
    where HD_STATUS.NAME='closed'
    and HD_PRIORITY.NAME<>'project'
    and TIME_CLOSED<>0
    AND ((TIMESTAMP(HD_TICKET.TIME_CLOSED) <= NOW()
    AND TIMESTAMP(HD_TICKET.TIME_CLOSED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))
    and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
    group by CLOSE_GROUP - jverbosk 11 years ago
  • Thanks for that John, I'll send that over and see if this is better for them. - AnthonyC 11 years ago

Posted by:

AnthonyC White Belt
Ninja since: 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

KACE Product Support Questions
question

Related Questions

Link

Related Links

Post

Related Posts

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ