Very basic script to report agent performance, which will dispaly the number of Ticket resolved by each RT agent. MySQL & Bash.
Subject: [RT] 10 July 2005 --- 7,Stewart Walker 7,Vijay Shetty 15,Manan Patel 16,Arthur Skibinskiy 25,Daniel Chan
Here is the howto
1. Create working directory :
2. Create SQL Script and bash script:
SELECT CONCAT(t.count,',',u.realname) FROM (SELECT Owner, COUNT(Owner) AS count FROM Tickets WHERE Status='resolved' AND Resolved < curdate() and resolved > date_sub(curdate(), INTERVAL 1 day) GROUP BY Owner) AS t LEFT JOIN Users u ON t.owner = u.id ORDER BY t.count;
cat send-report.sh #!/bin/bash cd /usr/local/rt-stat mysql rt3 < rt-stat.sql | grep -v CONCAT | grep -v Nobody | mail -s "[RT] `date '+%a %d-%h-%y'`" firstname.lastname@example.org
Make sure you adjust your mysql parameter here, eg: login/passwd
3. Add to this to crontab
crontab -u rt -l 0 9 * * * /usr/local/rt-stat/send-report.sh
- This works fine on RT 3.22
July 13, 2005
Above Query returns (line breaks added for display):
ERROR 1064 at line 1: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Owner, COUNT(Owner) AS count FROM Tickets WHERE Status='
On mySQL Version:
mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386)
July 22, 2005
July 29, 2005
Anyone able to fix this?
Feb 10, 2006
Works fine on 3.4.4. Can anyone mod this to make a how many Open tickets and how many new tickets each tech has?
I asked on the mailing list, Mike generously contributed this:
Here's a modification of the SQL query to give a current count of new and open tickets for each Owner:
SELECT CONCAT(t.count,',',t.Status,',',u.realname) FROM (SELECT Owner, Status, COUNT(Owner) AS count FROM Tickets WHERE Status = 'new' OR Status = 'open' GROUP BY Owner, Status) AS t LEFT JOIN Users u ON t.owner = u.id ORDER BY u.realname, t.Status;
Works on RT 3.4.4, FCore4, mysql 4.1.11
Here's a postgres version for Top resolvers last 14 days
select t.count || ',' || u.realname FROM (select owner,count(owner) AS count FROM tickets where status='resolved' and resolved < now() and resolved > (now() - interval '14 day') group by owner) AS t LEFT JOIN users u ON t.owner = u.id order by t.count DESC;
May 15, 2006
On MySQL version 4.0 and older doesn't work inner select or subquery for example
select * from (select * from ...);
and must be done by join. See mysql.com manual. http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html