[rt-devel] RT Reports

[Alex Rebrik]

Hello List,

here are some (simple?) SQL queries that we run against our RT database 
to generate progress reports and track people's work. Hope this is 
useful to someone else out there, and if you have any suggestions on how 
to improve these let me know. These are wrapped in a simple shell script 
which use date(1) command to feed range of dates / times into  SQL 
statement, beware that this works for *BSD date(1); Solaris, Linux, and 
others may and do behave differently. It would be better to wrap these 
in Perl instead of shell, but I hacked 'em together rather quickly, so ...


---daily progress report---

#!/usr/local/bin/bash
END=`date +%Y-%m-%d`
START=`date -v-1d +%Y-%m-%d`

# We're in PST, hence -8 against GMT timestamp

/usr/local/bin/mysql rt3 -t -e\
 "SELECT Name AS User, RealName AS 'Full Name', COUNT(*) AS Resolved
 FROM Transactions t, Users u, GroupMembers g
 WHERE g.GroupId='74' AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator = u.id AND t.Creator = g.MemberId
 GROUP by Name;
 SELECT COUNT(*) AS 'Total Resolved'
 FROM Transactions t, GroupMembers g
 WHERE g.GroupId='74' AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator = g.MemberId;"|\
/usr/bin/mail -s "CSRT Daily Report - $START" EMAIL at GOES.HERE


----same weekly----

#!/usr/local/bin/bash
END=`date -v1d +%Y-%m-%d`
START=`date -v1d -v-1m +%Y-%m-%d`
MONTH=`date -v-1m +%B\ %Y`

/usr/local/bin/mysql rt3 -t -e\
 "SELECT Name AS User, RealName AS 'Full Name', COUNT(*) AS RESOLVED
 FROM Transactions t, Users u, GroupMembers g
 WHERE g.GroupId='74'
 AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator =  u.id
 AND t.Creator =  g.MemberId
 GROUP by Name;

 SELECT COUNT(*) AS 'Total Resolved'
 FROM Transactions t, GroupMembers g
 WHERE g.GroupId='74' AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator = g.MemberId;"|\
/usr/bin/mail -s "CSRT Weekly Report - Week of $WEEK" EMAIL at GOES.HERE


---same monthly---

#!/usr/local/bin/bash
END=`date -v1d +%Y-%m-%d`
START=`date -v1d -v-1m +%Y-%m-%d`
MONTH=`date -v-1m +%B\ %Y`

/usr/local/bin/mysql rt3 -t -e\
"SELECT Name AS User, RealName AS 'Full Name', COUNT(*) AS Resolved
 FROM Transactions t, Users u, GroupMembers g
 WHERE g.GroupId='74' AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator = u.id AND t.Creator = g.MemberId
 GROUP by Name;
 SELECT COUNT(*) AS 'Total Resolved'
 FROM Transactions t, GroupMembers g
 WHERE g.GroupId='74' AND t.Type='Status'
 AND t.NewValue='resolved'
 AND DATE_SUB(t.Created, INTERVAL 8 HOUR) BETWEEN '$START' AND '$END'
 AND t.Creator = g.MemberId;"|\
/usr/bin/mail -s "CSRT Monthly Report - $MONTH" clt-report at zappos.com



---count of "new" tickets by Queue (we do these at EOB every day)---

#!/usr/local/bin/bash
TODAY=`date +%Y-%m-%d`

/usr/local/bin/mysql rt3 -t -e\
"SELECT q.Name AS Queue, count(*) AS 'New Tickets',
DATE_SUB(MIN(t.Created), INTERVAL 8 HOUR) AS 'Start Time'
FROM Tickets t, Queues q
WHERE t.Status = 'new'
AND t.Queue = q.id
AND q.Name != 'SPAM'
GROUP by q.Name
ORDER BY 'Start Time';" |\
/usr/bin/mail -s "CSRT New Tickets Report - $TODAY" clt-report at zappos.com

#!/usr/local/bin/bash
END=`date -vsun +%Y-%m-%d`
START=`date -vmon -v-1w +%Y-%m-%d`
WEEK=`date -vmon -v-1w +%B\ %d,\ %Y`



Now, if only I could figure out how to track RT users' login times, I'd 
have it all :)

--Alex