Hello All,

    I'm no SQL wiz so just want to ask if its possible to have a sql report to show who changed the status of a ticket.

From the report I have I can only generate the names of the "Owner and the Submitter" but not the tech who changed ticket status.

Below are my current rows but need to have a separate row for the tech who changed the ticket status:


Queue
Line of Business
Category
Title
Id
Days
Created
Time Opened
Time Closed
Location
Status
First Resolution
Last Resolution
Resolution Count
Priority
Owner
Submitter


Kind regards to all.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
This query doesn't include all of the columns in your list (since I'm not sure to what they all refer) but it does show who changed tickets from New to Opened.
SELECT Q.NAME as "Queue", 
C.NAME as "Category", 
T.TITLE, 
T.ID, 
T.CREATED, 
T.TIME_OPENED,
T.TIME_CLOSED,
CU.USER_NAME as "Changer", 
OWNER.USER_NAME as "Owner", 
SUBMITTER.USER_NAME as "Submitter", 
HD_STATUS.NAME,
HD_PRIORITY.NAME,
HD_TICKET_CHANGE.DESCRIPTION
FROM HD_TICKET T
JOIN HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
JOIN HD_CATEGORY C on C.ID = T.HD_CATEGORY_ID
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.DESCRIPTION like '%Changed ticket Status from "New" to "Opened"%'
JOIN USER CU on CU.ID = HD_TICKET_CHANGE.USER_ID
JOIN USER OWNER on OWNER.ID = T.OWNER_ID
JOIN USER SUBMITTER on SUBMITTER.ID = T.SUBMITTER_ID
JOIN HD_STATUS on HD_STATUS.ID = T.HD_STATUS_ID
JOIN HD_PRIORITY on HD_PRIORITY.ID = T.HD_PRIORITY_ID

Answered 09/24/2014 by: chucksteel
Red Belt

Please log in to comment

Answers

0
The data for who made a change is stored in the HD_TICKET_CHANGE table so it is possible to create reports based on that table. I'm not sure which changes you want a report on, however. Do you want a report of all changes or just when the ticket status changes from New to Opened?
Answered 09/24/2014 by: chucksteel
Red Belt

  • Hello Chucksteel.

    Just the status change from "New to Opened"

    Kind regards. :)
Please log in to comment
0

Thanks Chuck

Sharing mine:

SELECT
 q.NAME as 'Queue',
 a.CUSTOM_FIELD_VALUE0 AS Line_of_Business,
 a.CUSTOM_FIELD_VALUE9 as Category,
 a.TITLE,
 a.ID as 'Ticket Number',
 dayname(a.created) as Days,
 a.CREATED,
 a.TIME_OPENED,
 a.TIME_CLOSED,
 a.CUSTOM_FIELD_VALUE2 AS Location,
 c.Name as STATUS,
 b.FirstResolution as FIRST_RESOLUTION,
 b.LastResolution as LAST_RESOLUTION,
 b.Resolutions as RESOLUTION_COUNT,
 d.Name as PRIORITY,
 e.FULL_NAME as OWNER,
 f.FULL_NAME as SUBMITTER

FROM HD_TICKET a
LEFT JOIN
(
 SELECT
  HD_TICKET_ID,
  MAX(TIMESTAMP) as LastResolution,
  MIN(TIMESTAMP) as FirstResolution,
  COUNT(*) as Resolutions
 FROM HD_TICKET_CHANGE
 WHERE
  DESCRIPTION LIKE '%to "resolved%'
 GROUP BY
  HD_TICKET_ID
) b ON a.ID=b.HD_TICKET_ID
LEFT JOIN HD_STATUS c ON c.ID=a.HD_STATUS_ID
LEFT JOIN HD_PRIORITY d ON d.ID=a.HD_PRIORITY_ID
LEFT JOIN USER e ON e.ID=a.OWNER_ID
LEFT JOIN USER f ON f.ID=a.SUBMITTER_ID
LEFT JOIN HD_QUEUE q on q.ID=a.HD_QUEUE_ID

WHERE a.created BETWEEN DATE_SUB(NOW(), INTERVAL 31 DAY) AND NOW()

Answered 09/24/2014 by: sniperfox29
White Belt

  • can you add my CU. USERname to mine? :)
  • Sure, you need to add the appropriate join to the HD_TICKET_CHANGE table and then another join to the USERS table:

    SELECT
    q.NAME as 'Queue',
    a.CUSTOM_FIELD_VALUE0 AS Line_of_Business,
    a.CUSTOM_FIELD_VALUE9 as Category,
    a.TITLE,
    a.ID as 'Ticket Number',
    dayname(a.created) as Days,
    a.CREATED,
    a.TIME_OPENED,
    a.TIME_CLOSED,
    a.CUSTOM_FIELD_VALUE2 AS Location,
    c.Name as STATUS,
    b.FirstResolution as FIRST_RESOLUTION,
    b.LastResolution as LAST_RESOLUTION,
    b.Resolutions as RESOLUTION_COUNT,
    d.Name as PRIORITY,
    e.FULL_NAME as OWNER,
    f.FULL_NAME as SUBMITTER

    FROM HD_TICKET a
    LEFT JOIN
    (
    SELECT
    HD_TICKET_ID,
    MAX(TIMESTAMP) as LastResolution,
    MIN(TIMESTAMP) as FirstResolution,
    COUNT(*) as Resolutions
    FROM HD_TICKET_CHANGE
    WHERE
    DESCRIPTION LIKE '%to "resolved%'
    GROUP BY
    HD_TICKET_ID
    ) b ON a.ID=b.HD_TICKET_ID
    LEFT JOIN HD_STATUS c ON c.ID=a.HD_STATUS_ID
    LEFT JOIN HD_PRIORITY d ON d.ID=a.HD_PRIORITY_ID
    LEFT JOIN USER e ON e.ID=a.OWNER_ID
    LEFT JOIN USER f ON f.ID=a.SUBMITTER_ID
    LEFT JOIN HD_QUEUE q on q.ID=a.HD_QUEUE_ID
    JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = a.ID and HD_TICKET_CHANGE.DESCRIPTION like '%Changed ticket Status from "New" to "Opened"%'
    LEFT JOIN USER CU on CU.ID = HD_TICKET_CHANGE.USER_ID

    WHERE a.created BETWEEN DATE_SUB(NOW(), INTERVAL 31 DAY) AND NOW()
    • Thanks Man. :) It worked. :) 10 stars for you. :)
Please log in to comment
Answer this question or Comment on this question for clarity