DailyStat

Daily Stat

Very basic script to report agent performance, which will dispaly the number of Ticket resolved by each RT agent. MySQL & Bash.

Sample Report

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 :

mkdir /usr/local/rt-stat

2. Create SQL Script and bash script:

cat rt-stat.sql

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'`" r_wahyudi@gmail.com
 
 

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

Rianto Wahyudi

- This works fine on RT 3.22

COMMENTS:

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)

MichaelErana

July 22, 2005

Ditto above.

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