Skip to content

Instantly share code, notes, and snippets.

@nicoletta-maia
Last active March 31, 2022 14:11
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save nicoletta-maia/1b6ba09856b414a323120d7a82db370a to your computer and use it in GitHub Desktop.
Custom reports on the database of osTicket 1.10. Here some simple queries
-- The table `ost_thread_event` contains a log of events like the assignments;
-- please note that the auto-claims on response aren't recorded.
-- For example to get the assignment done between the '2018-06-01' and '2018-07-01' you can use a query like this:
SELECT `TE`.`username`, COUNT(*) AS `Assignments`
FROM
`ost_thread_event` AS `TE`
JOIN
`ost_thread` AS `T`
ON `TE`.`thread_id` = `T`.`id` AND `T`.`object_type` = 'T'
WHERE
`TE`.`state` = 'assigned' AND
`TE`.`timestamp` BETWEEN '2018-06-01' AND '2018-07-01'
GROUP BY `TE`.`username`;
-- Using the columns `ost_thread_event`.`staff_id` and `ost_thread_event`.`team_id` you can also recognize:
-- - the claim events
-- - the assignments to another staff member
-- - the assignments to a team
-- This query sums for every staff member the number of words written (only in reply messages, not in notes)
-- between the '2018-06-01' and '2018-07-01' grouped by day
-- (since there is no function ready to count words in a text in MySQL, this query calculate the number of
-- words as the difference between the length of the body and its length without spaces):
SELECT date(`E`.`created`), `S`.`username`, SUM(LENGTH(`E`.`body`) - LENGTH(REPLACE(`E`.`body`, ' ', '')) + 1) AS `counter`
FROM
`ost_thread_entry` AS `E`
JOIN
`ost_thread` AS `T`
ON `E`.`thread_id` = `T`.`id` AND `T`.`object_type` = 'T'
JOIN
`ost_staff` AS `S`
ON `E`.`staff_id` = `S`.`staff_id`
WHERE
`E`.`type` = 'R' AND
`E`.`created` BETWEEN '2018-06-01' AND '2018-07-01'
GROUP BY `E`.`staff_id`, date(`E`.`created`), `S`.`username`;
-- Please note that `ost_thread_entry`.`body` could contains html characters and this query may be heavy
-- for performance, it may be appropriate to search by reduced time intervals and filter on the staff members
-- This query get the response time for all the tickets created between `2018-07-01` and `2018-07-02`:
SELECT
`T`.`object_id` AS `Ticket id`,
MIN(`T`.`created`) AS `Thread creation`,
MIN(`TE`.`created`) AS `First response`,
TIMESTAMPDIFF(MINUTE, MIN(`T`.`created`), MIN(`TE`.`created`)) AS `Initial response time (minute)`
FROM
`ost_thread` AS `T` JOIN
`ost_thread_entry` AS `TE`
ON `T`.`id` = `TE`.`thread_id` AND `T`.`object_type` = 'T' AND `TE`.`type` = 'R'
WHERE `T`.`created` BETWEEN '2018-07-01' AND '2018-07-02'
GROUP BY `T`.`object_id`;
-- Please note that in the `ost_thread_entry` table there are all the entries;
-- you can use the `type` to choose only the staff responses. That column can have these values:
-- - 'M': customer message
-- - 'R': staff response
-- - 'N': internal note
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment