/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


As I am not a scripting person and have had a request for this report from our k1000, Can someone advise please?

04/21/2016 699 views
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
1 Comment   [ + ] Show comment

Comments

  • 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.
    • 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
    • The other way to get the Queue IDs is to run the query "select ID, NAME from ORG1.HD_QUEUE"

All Answers

0

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
Answered 04/21/2016 by: grayematter
5th Degree Black Belt

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:

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