Admin: 2 revisions imported

2016-04-06T20:08:44Z

2 revisions imported

New page

= 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;

<nowiki>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

</nowiki>

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

DailyStat - Revision history

Admin: 2 revisions imported