2 Replies Latest reply on Jan 9, 2009 1:20 PM by sbuehler

    Who touched that ticket?


      I need a query that will tell me who is 'working' our incoming email. When email is received an incident is created by user 'SA' in the 'mail' process. The incident has an automatic initial assignment to the 'SD1' group.  Pulling those is simple. The difficult part is figuring out who 'worked' that ticket. Once the ticket is first opened, the required field 'category' is not filled in, so in order to 'work' the ticket, the analyst must select a category. So, my thought is - if I can pull who set the category then I know who 'worked' the ticket. However, I don't think that is stored. Therefore, do I need to rely on the minimum assignment date and/or minimum notes date in order to determine who is reading through and updating/assigning income email tickets? Or, is there an easier or more accurate place to find a minimum date and the analysts who did something to that ticket on that minimum date?

      If I can't pull what I need from query designer, then I'm OK with using Crystal Reports. I'm also comfortable with writing a command (sql) in Crystal if that's what I'll need to do.

      What I'm really looking for is an idea of what tables/fields I should query in order to find who touched a ticket after the initial assignment was made.



        • 1. Re: Who touched that ticket?
          karenpeacock SupportEmployee

          Hi Sara


          If you look at the audit trail / history tab on the ticket can you see the action that you want to report on in here?  It won't specifically show that the category was changed but should show there was an update after SA created the ticket?  If so, then your best bet is to link to the audit trail table and use Crystal reports.  Here's an article from Stu on how this data is stored:




          As the audit trail table holds the history of all your tickets it can be pretty huge, it might be an idea to write a sql view so that the audit trial only for the ticket you are currently reporting on gets picked up each time you run the report.


          Once you've got the audit trial linked in the report then you can look for the minimum row based on date which was not linked to the SA user?


          The neater solution might be if you had a manual action within your process called "categorise ticket" and this brought up a new window off the ticket which allowed you to categorise the ticket and wrote the value back to the main ticket window.  In this way you could store a creation user and creation date for the "categorise ticket" object and it would make reporting easier.  This is just an idea and you might decide that it would be easier to report with what you've got rather than do some process design but I thought I'd mention it as an idea that you might want to consider.


          Best wishes


          • 2. Re: Who touched that ticket?

            Thank you Karen and Stu.


            The audit trail idea and the code snippet are exactly what I needed!