/build/static/layout/Breadcrumb_cap_w.png

K1000 - Report to show when a ticket has been reassigned to a new owner

Hello - In the K1000 Service Desk. Is there a way to run a report to show when a ticket has been transferred from one ticket owner to another owner in the same queue? I do not see a report category for History notes.

Our workflow is a ticket is emailed to the Queue. That ticket is then created by the submitter. It is then assigned to someone in that queue. But I would like to see if there is a report that shows if it's been transferred to another owner.

Using the Report Wizard, I've had no success. 

Any suggestions?

0 Comments   [ + ] Show comments

Answers (2)

Posted by: grayematter 7 years ago
5th Degree Black Belt
5

I don't think that kind of filtering is available in the wizard.  The query below should get you started.

SELECT 
    *
FROM
    HD_TICKET
        JOIN
    ORG1.HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
WHERE
    HD_TICKET_CHANGE.DESCRIPTION LIKE '%Changed ticket Owner from%'
        AND HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY HD_TICKET_CHANGE.TIMESTAMP DESC;

That will capture all assignment changes, including the initial assignment.  If you want to exclude the initial assignment, try this query instead.

SELECT 
    *
FROM
    HD_TICKET
        JOIN
    ORG1.HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
WHERE
    (HD_TICKET_CHANGE.DESCRIPTION LIKE '%Changed ticket Owner from%'
        AND HD_TICKET_CHANGE.DESCRIPTION NOT LIKE '%Changed ticket Owner from "Unassigned" to%')
        AND HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY HD_TICKET_CHANGE.TIMESTAMP DESC;



Comments:
  • Good morning, I was searching and came across this post, which is something I would love to be able to report on (When a user changes ticket owner to another user - I suspect someone is changing ticket ownership after it is closed to buff stats). The problem is when I run the query above I get

    mysqli error: [1142: SELECT command denied to user 'R4'@'localhost' for table 'HD_TICKET_CHANGE'] in EXECUTE( "SELECT * FROM HD_TICKET JOIN ORG1.HD_TICKET_CHANGE ON HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID WHERE HD_TICKET_CHANGE.DESCRIPTION LIKE '%Changed ticket Owner from%' AND HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY HD_TICKET_CHANGE.TIMESTAMP DESC LIMIT 0")



    I'm an admin user so I should have the permissions to do this query, I suspect maybe it's syntax but I'm not sure. - cr8zyeddie 2 years ago
Posted by: grayematter 2 years ago
5th Degree Black Belt
1

Based on the error - SELECT command denied to user 'R4'@'localhost' - the R4 user may not have permissions.  Try a simple "select * from HD_Ticket;" then "select * from HD_TICKET_CHANGE;".  If both of those are successful, the account should have the correct permissions. 

Also, in my instance, everything is under ORG1 as we only have a single organization configured.  Things may be under a different schema in your instance, especially if you have multiple organizations configured.


Comments:
  • Thank you! It was the ORG1 causing the issue as we do have multiple organizations. After changing that the query worked. Thanks again! - cr8zyeddie 2 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

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