As I am not a scripting person and have had a request for this report from our k1000, Can someone advise please?
The needed report is for the service desk. The report should have the following: shows what was open in the last week - what tickets each person still has open (ID, title, Timeopen, submitter, status, priority), and what tickets they have closed during the past 7 days.
what I have so far for sql is this, but it does not function in the manner we desire. Tickets with different status are mixed in the report as well as separate queues, ie maintenance and it queues. We need a separate report for each queue and the tickets organized by status.
SELECT T.ID AS 'Ticket #', T.TITLE AS 'Issue', T.TIME_OPENED AS 'Time Opened', IFNULL(O.FULL_NAME,'Unassigned') AS 'Owner', U.FULL_NAME AS 'Submitter', S.NAME AS 'Status', P.NAME AS 'Priority' FROM HD_TICKET T
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
WHERE (S.STATE = 'opened') OR (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY O.FULL_NAME, S.NAME, P.NAME, T.TIME_OPENED
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
LEFT JOIN USER O ON (T.OWNER_ID = O.ID)
JOIN HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
WHERE (S.STATE = 'opened') OR (S.STATE = 'closed' AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY O.FULL_NAME, S.NAME, P.NAME, T.TIME_OPENED
1 Comment
[ + ] Show comment
-
Grayematter, So is the queue id the "name" of the queue? if so when I put the statement in the where clause - I am getting a SQL error. I'm not sure where to get the queue id if this is not the name. Thank you for your quick initial answer. - Bill Brown 8 years ago
-
Queue_ID is the queue number. If you're not sure what it is, when you hover over your queue from Configuration->Queues, the URL will show up as: http://KBOX/adminui/queue.php?ID=4 - BHC-Austin 8 years ago
-
The other way to get the Queue IDs is to run the query "select ID, NAME from ORG1.HD_QUEUE" - grayematter 7 years ago
Answers (1)
Please log in to answer
Posted by:
grayematter
8 years ago
Here is the query to include and sort by the Queue names.
SELECT
Q.NAME AS 'Queue',
T.ID AS 'Ticket #',
T.TITLE AS 'Issue',
T.TIME_OPENED AS 'Time Opened',
IFNULL(O.FULL_NAME, 'Unassigned') AS 'Owner',
U.FULL_NAME AS 'Submitter',
S.NAME AS 'Status',
P.NAME AS 'Priority'
FROM
HD_TICKET T
JOIN
HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
JOIN
USER U ON (T.SUBMITTER_ID = U.ID)
LEFT JOIN
USER O ON (T.OWNER_ID = O.ID)
JOIN
HD_PRIORITY P ON (T.HD_PRIORITY_ID = P.ID)
join
HD_QUEUE Q on T.HD_QUEUE_ID = Q.ID
WHERE
(S.STATE = 'opened')
OR (S.STATE = 'closed'
AND DATEDIFF(NOW(), T.TIME_CLOSED) < 7)
ORDER BY Q.NAME, O.FULL_NAME , S.NAME , P.NAME , T.TIME_OPENED
If you really want a separate report for each queue, add the line below to the WHERE clause of your original query, replacing "1" with the appropriate queue id.
and T.HD_QUEUE_ID = 1
