Customize Report for Service Desk
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'stalled' OR HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
Answers (2)
SELECT HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Concat(IF (Time_to_sec(NOW()) >= Time_to_sec(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED), To_days(NOW()) - To_days(HD_TICKET.TIME_OPENED) - 1), 'd ', Date_format(Addtime("2000-01-01 00:00:00", Sec_to_time(Time_to_sec(NOW()) - Time_to_sec(HD_TICKET.TIME_OPENED))), '%kh %im')) AS TIME_OPEN,
HD_PRIORITY. NAME AS PRIORITY,
HD_CATEGORY. NAME AS CATEGORY,
HD_STATUS. NAME AS STATUS,
HD_IMPACT. NAME AS IMPACT,
MACHINE. NAME AS MACHINE_NAME,
Ifnull((SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.OWNER_ID = USER .ID), ' Unassigned') AS OWNER_NAME,
(SELECT FULL_NAME
FROM USER
WHERE HD_TICKET.SUBMITTER_ID = USER .ID) AS SUBMITTER_NAME
FROM HD_TICKET
LEFT JOIN HD_CATEGORY
ON HD_CATEGORY_ID = HD_CATEGORY.ID
LEFT JOIN HD_STATUS
ON HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN HD_PRIORITY
ON HD_PRIORITY_ID = HD_PRIORITY.ID
LEFT JOIN HD_IMPACT
ON HD_IMPACT_ID = HD_IMPACT.ID
LEFT JOIN MACHINE
ON HD_TICKET.MACHINE_ID = MACHINE.ID
JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE ( HD_STATUS.STATE = 'stalled'
OR HD_STATUS.STATE = 'opened' )
AND HD_QUEUE.NAME = 'Network/Phone' -- Queue name here
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Hello Dears,
Someone help me to put department field on the query?
Select
HD_QUEUE.NAME,
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET.DUE_DATE,
Date_Format(HD_TICKET.TIME_OPENED, '%m-%d %H:%i') As TIME_OPENED,
Date_Format(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') As TIME_CLOSED,
Concat(If(Time_To_Sec(HD_TICKET.TIME_CLOSED) >=
Time_To_Sec(HD_TICKET.TIME_OPENED), To_Days(HD_TICKET.TIME_CLOSED) -
To_Days(HD_TICKET.TIME_OPENED), To_Days(HD_TICKET.TIME_CLOSED) -
To_Days(HD_TICKET.TIME_OPENED) - 1), 'd ',
Date_Format(AddTime("2000-01-01 00:00:00",
Sec_To_Time(Time_To_Sec(HD_TICKET.TIME_CLOSED) -
Time_To_Sec(HD_TICKET.TIME_OPENED))), '%kh %im')) As TIME_TO_CLOSE,
HD_TICKET.RESOLUTION As RESOLUTION,
HD_PRIORITY.NAME As PRIORITY,
HD_CATEGORY.NAME As CATEGORY,
HD_STATUS.NAME As STATUS,
HD_IMPACT.NAME As IMPACT,
MACHINE.NAME As MACHINE_NAME,
DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%M/%Y')AS MES_FECHAMENTO,
IF((TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED,HD_TICKET.TIME_OPENED))/60) < HD_PRIORITY.ESCALATION_MINUTES,'Yes','No') AS SLA,
IfNull((Select
USER.FULL_NAME
From
USER
Where
HD_TICKET.OWNER_ID = USER.ID), ' Unassigned') As OWNER_NAME,
(Select
USER.FULL_NAME
From
USER
Where
HD_TICKET.SUBMITTER_ID = USER.ID) As SUBMITTER_NAME,
HD_PRIORITY.ESCALATION_MINUTES
From
HD_TICKET Left Join
HD_CATEGORY On HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID Left Join
HD_STATUS On HD_TICKET.HD_STATUS_ID = HD_STATUS.ID Left Join
HD_PRIORITY On HD_TICKET.HD_PRIORITY_ID = HD_PRIORITY.ID Left Join
HD_IMPACT On HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID Left Join
MACHINE On HD_TICKET.MACHINE_ID = MACHINE.ID,
HD_QUEUE
Where
HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID And
HD_STATUS.STATE = 'closed'
Order By
IfNull((Select
USER.FULL_NAME
From
USER
Where
HD_TICKET.OWNER_ID = USER.ID), ' Unassigned'),
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Thanks a lot
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
