PavelH
To be honest, I don't have any queries I particularly need. This is for a Grafana dashboard, I was hoping the community would have some prewritten queries to show... well anything interesting.
Anyway, here's a few I'm definitely struggling, for example:
- Ticket helptopic by source
select source, helptopic as helptopic, lastupdate from ost_ticket
ORDER BY lastupdate ASC
I tried to combine a GROUP BY and ORDER BY with no luck, this is the only working query. Trying to find what helptopics usually are chosen by source, either web or email.
- Words Written per staff member (stole this off github)
It's almost there, I think I just need to sum word count per username.
-- 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 (NOW() - INTERVAL 7 DAY) AND NOW()
GROUP BY `E`.`staff_id`, date(`E`.`created`), `S`.`username`
ORDER BY counter DESC;
-- 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
- Tickets per Helptopic
select helptopic as "Help Topic",count(helptopic) as "Count" from ost_ticket
group by helptopic
order by count(helptopic) desc
