Ticket Status "New to Opened" Owner
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.
Answers (3)
Comments:
-
Hello Chucksteel.
Just the status change from "New to Opened"
Kind regards. :) - sniperfox29 9 years ago
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()
Comments:
-
can you add my CU. USERname to mine? :) - sniperfox29 9 years ago
-
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() - chucksteel 9 years ago-
Thanks Man. :) It worked. :) 10 stars for you. :) - sniperfox29 9 years ago