Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

Time Tracking Report

Hey all,

Not sure if this is the best place or not but looking for anyone who has experience or used the Time Tracking mod/add on. I'm looking to get a report to be able to send to our billing department so we can make invoices for the time spent helping. I am thinking the only way to do this is via a query as I am not seeing any other way. I'm having a little difficulty doing this. Mostly I just need to pull the ticket #, date, user and time spent. Any insight would be greatly appreciated. Thanks much.

Comments

  • edited January 31
    Hello, here is quick SQL query, you could make it better of course :)

    SELECT ost_ticket.number AS "Ticket Number", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", CONCAT(FLOOR(HOUR(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)) / 24), ' days ', MOD(HOUR(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), 24), ' hours ', MINUTE(TIMEDIFF(ost_ticket.created, ost_ticket.lastupdate)), ' minutes') AS "Created to Last Update time" FROM ost_ticket, ost_staff where ost_ticket.staff_id=ost_staff.staff_id;

    What does it do, it calculates difference between created time and last update, headers include: 
    | Ticket Number | Agents Name      | Created to Last Update time |

    Please do note it does include time such as weekends, bank holidays, etc, thus take these values at face value, if you have support which works 24/7 then it is good enough. It also takes staff who is currently assigned to the ticket, it does not take into account hours spent by other agents.

    Without few extra VIEWS, complex`y queries, it will be hard to do what you want, e.g., calculate time spent per agent on one ticket.

  • I found this on git: https://github.com/osTicket/osTicket/pull/3231

    You might be interested in checking it out, maybe it will do exactly what you need :)
  • Thanks for the response sperrow.

    I actually do use the addon you linked in your second post and I was mostly looking for the query (or ideas for the query) based off of that addon, as it creates the new fields and such within the DB, time_spent in ost_thread_entry. The query you posted is great but like you mentioned, there's some factors that make it difficult to use.
  • Hello @hybridwarhawk without DB modifications it won't be possible to do complex calculations like that :) 

    Other solution would be to do php modification + db mod (extra column for thread item) and add a field to ticket reply for agent to input time spent manually. 

    There are other products which are used for time spent management, which were meant for this specific reason - to track time :) osTicket is a ticketing system a lot more than a management tool.
  • Hi @sperrow. So there is no way to query the additional fields that were created through the mod? There is a field to put in the time in the ticket and a subsequent data field that is populated within the DB. Maybe I'm just missing it but it seems like a query, semi similar to the one you posted, just that it needs to pull the specific time_spent on each ticket created. I'm not as up on my sql as i should be and this is giving me fits. More or less, the mod added DB fields to track the time spent and i need to link that to a ticket number but you're saying that it isn't possible, despite the DB being modded by the plugin already? Just making sure I understand. I do appreciate the help greatly.
  • edited February 1
    Oh, I misunderstood then @hybridwarhawk, if you have these fields already, then the only thing you is to modify the query I posted. You would need to link together this table (ost_thread_entry + ost_ticket) and most likely do a sum on total time spent per ticket, something like this:

    SELECT ost_ticket.number AS "Ticket Number", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent" FROM ost_ticket, ost_staff, ost_thread_entry where ost_ticket.staff_id=ost_staff.staff_id AND ost_ticket.ticket_id=ost_thread_entry.thread_id GROUP BY ost_ticket.number;

    *Please do note that I do not have this mod, so in theory, based on what I saw on the GIT, you should have ost_thread_entry.time_spent, I added it and summed up for you, so it _should_ work, but no guarantees :)

    Thanks,
  • @sperrow this is amazing and it is working absolutely perfect!

    If you don't mind, can i pick your brain on a couple other things with this? :)  Is there a way to add in a date range modfier as well. Also, do you happen to know of the top of your head the subject field that could be added into the original query? You rock, this is great!
  • Hmm, looks like thread_id does not match ticket_id in the query, leave it with me and I will take a closer look at the query :) I will add subject too and time constrains.
  • Hello @hybridwarhawk

    Sorry for the long wait, something came up :) But here you got the revised query, had few mistakes here and there, now it will actually show everything, well, at least it should :p

    SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata WHERE 
    ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' GROUP BY ost_ticket.ticket_id;

    The bold part controls the date range modifier. I have added another feature, it will give you html link, so you can view the ticket by clicking on it. Please do change the orange highlighted part to your osticket URL.
  • @sperrow

    This is fantastic! If I wanted to add in the user  that submitted the ticket, how would i go about adding that? it looks like it is ost_user.name but I keep running into issues. Not sure if I have to add in another LEFT JOIN and link ids or where I may be going wrong. Many thanks for all the assistance.
  • edited February 2
    No problem @hybridwarhawk :)
    SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", ost_user.name AS "Created By", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata, ost_user WHERE  ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' AND ost_user.name='Full Name' GROUP BY ost_ticket.ticket_id;

    This will add another column, Created By, if you wish to look for a particular user, then use AND ost_user.name='Full name'
  • @sperrow

    Amazing, working perfectly. One of last question (probably lol). Is there a way to make tickets that were deleted in the helpdesk system to not show in the query? I just noticed there are a couple in there that someone had deleted but are still pulling in the query. Just curious. Going forward i'll make sure tickets are not deleted so it shouldn't be a problem but if there is a way in the query to not show those, that would be great. Thanks a ton again.
  • Hello @hybridwarhawk, how did you delete the ticket? Because if you delete it using the facility, it removes the entry from the database table. You might want to add this then, but it is a wild shot really:

    SELECT ost_ticket.number AS "Ticket Number", ost_ticket__cdata.subject AS "Subject", ost_user.name AS "Created By", CONCAT(ost_staff.firstname," ", ost_staff.lastname) AS "Agents Name", SUM(ost_thread_entry.time_spent) AS "Time Spent", CONCAT("https://your.osticket.com/scp/tickets.php?id=",ost_ticket.ticket_id) AS "Link" FROM ost_ticket LEFT JOIN ost_staff ON ost_ticket.staff_id=ost_staff.staff_id, ost_thread_entry, ost_thread, ost_ticket__cdata, ost_user WHERE  ost_ticket.ticket_id=ost_thread.object_id AND ost_thread.id=ost_thread_entry.thread_id AND ost_ticket.ticket_id=ost_ticket__cdata.ticket_id AND ost_ticket.user_id=ost_user.id AND ost_ticket.created>='2017-01-01' AND ost_ticket.created<='2017-02-01' AND ost_user.name='Full Name' AND ost_ticket.status_id IN ('1','2') GROUP BY ost_ticket.ticket_id;

    Remove or add the extra number depending what you need:
    +----+----------------------+----------+
    | id | name                 | state    |
    +----+----------------------+----------+
    |  1 | Open                 | open     |
    |  2 | Resolved             | closed   |
    |  3 | Closed               | closed   |
    |  4 | Archived             | archived |
    |  5 | Deleted              | deleted  |
    |  6 | Faulty Unit returned | open     |
    |  7 | AR sent              | open     |
    |  8 | Monitoring           | open     |
    |  9 | Feature Request      | open     |
    +----+----------------------+----------+

  • @sperrow

    It would be when you go into the ticket, click the gear icon and then hit delete ticket. I can see where the difference is. I was using the out of the box ticket number system and then changed it to 1, 2, 3, etc. Seems there is just a minor difference between that ticket number and the id. Not a problem at all and it is working as you had mentioned it should be.
Sign In or Register to comment.