https://rt-wiki.bestpractical.com/index.php?title=DailyStat&feed=atom&action=historyDailyStat - Revision history2024-03-29T11:18:30ZRevision history for this page on the wikiMediaWiki 1.37.2https://rt-wiki.bestpractical.com/index.php?title=DailyStat&diff=795&oldid=prevAdmin: 2 revisions imported2016-04-06T20:08:44Z<p>2 revisions imported</p>
<p><b>New page</b></p><div>= Daily Stat =<br />
<br />
Very basic script to report agent performance, which will dispaly the number of Ticket resolved by each RT agent. [[MySQL]] &amp; Bash.<br />
<br />
==== Sample Report ====<br />
<br />
Subject: [RT] 10 July 2005<br />
---<br />
7,Stewart Walker<br />
7,Vijay Shetty<br />
15,Manan Patel<br />
16,Arthur Skibinskiy<br />
25,Daniel Chan<br />
<br />
==== Here is the howto ====<br />
<br />
1. Create working directory :<br />
<br />
mkdir /usr/local/rt-stat<br />
<br />
<br />
2. Create SQL Script and bash script:<br />
<br />
cat rt-stat.sql<br />
<br />
<br />
SELECT CONCAT(t.count,',',u.realname) FROM (SELECT Owner, COUNT(Owner) AS count FROM Tickets WHERE Status='resolved' AND Resolved &lt; curdate() and resolved &gt; date_sub(curdate(), INTERVAL 1 day) GROUP BY Owner) AS t LEFT JOIN Users u ON t.owner = u.id ORDER BY t.count;<br />
<br />
<nowiki>cat send-report.sh<br />
#!/bin/bash<br />
cd /usr/local/rt-stat<br />
mysql rt3 &lt; rt-stat.sql | grep -v CONCAT | grep -v Nobody | mail -s "[RT] `date '+%a %d-%h-%y'`" r_wahyudi@gmail.com<br />
<br />
</nowiki><br />
<br />
Make sure you adjust your mysql parameter here, eg: login/passwd<br />
<br />
3. Add to this to crontab<br />
<br />
crontab -u rt -l<br />
0 9 * * * /usr/local/rt-stat/send-report.sh<br />
<br />
<br />
<br />
Rianto Wahyudi<br />
<br />
- This works fine on RT 3.22<br />
<br />
= COMMENTS: =<br />
<br />
==== July 13, 2005 ====<br />
<br />
Above Query returns (line breaks added for display):<br />
<br />
ERROR 1064 at line 1: You have an error in your SQL syntax.<br />
Check the manual that corresponds to your MySQL<br />
server version for the right syntax to use near<br />
'SELECT Owner, COUNT(Owner) AS count FROM Tickets<br />
WHERE Status='<br />
<br />
<br />
On mySQL Version:<br />
<br />
mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386)<br />
<br />
[[MichaelErana]]<br />
<br />
==== July 22, 2005 ====<br />
<br />
Ditto above.<br />
<br />
==== July 29, 2005 ====<br />
<br />
Anyone able to fix this?<br />
<br />
==== Feb 10, 2006 ====<br />
<br />
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?<br />
<br />
I asked on the mailing list, Mike generously contributed this:<br />
<br />
Here's a modification of the SQL query to give a current count of new and open tickets for each Owner:<br />
<br />
SELECT CONCAT(t.count,',',t.Status,',',u.realname)<br />
FROM (SELECT Owner, Status, COUNT(Owner) AS count<br />
FROM Tickets<br />
WHERE Status = 'new' OR Status = 'open'<br />
GROUP BY Owner, Status) AS t<br />
LEFT JOIN Users u ON t.owner = u.id ORDER BY u.realname, t.Status;<br />
<br />
<br />
Works on RT 3.4.4, [[FCore4]], mysql 4.1.11<br />
<br />
Here's a postgres version for Top resolvers last 14 days<br />
<br />
select t.count || ',' || u.realname FROM (select owner,count(owner) AS count FROM tickets where status='resolved' and resolved &lt; now() and resolved &gt; (now() - interval '14 day') group by owner) AS t LEFT JOIN users u ON t.owner = u.id order by t.count DESC;<br />
<br />
==== May 15, 2006 ====<br />
<br />
On [[MySQL]] version 4.0 and older doesn't work inner select or subquery for example<br />
<br />
select * from (select * from ...);<br />
<br />
<br />
and must be done by join. See mysql.com manual. http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html</div>Admin