[rt-devel] RT Reports

[Kogami, Bruce]

Alex,

 

Can you give me some insight on using g.GroupId='74'? I guess what I'm wondering is, how the GroupId relates to which group or user and how I can find out what the GroupId number refers to. If it's a group, what is the name of the group, if it's a user, what is the name of the user?

 

Thanks,

Bruce

 

   _____  

Author: Alex Rebrik <mailto:arebrik at zappos.com> 
Date:  2004-03-09 15:35

2004-03-09 23:35
  -800
UTC

To: rt-devel <mailto:rt-devel at lists.bestpractical.com> 
Subject: [rt-devel] RT Reports

 

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 <http://lists.bestpractical.com/mailman/listinfo/rt-devel> 
 
 
----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 <http://lists.bestpractical.com/mailman/listinfo/rt-devel> 
 
 
---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 <http://lists.bestpractical.com/mailman/listinfo/rt-devel> 
 
 
 
---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 <http://lists.bestpractical.com/mailman/listinfo/rt-devel> 
 
#!/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

 



***************************************************************
This E-mail is confidential.  It should not be read, copied, disclosed or used by any person other than the intended recipient.  Unauthorized use, disclosure or copying by whatever medium is strictly prohibited and may be unlawful.  If you have received this E-mail in error, please contact the sender immediately and delete the E-mail from your system.
***************************************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20040331/a4c7b39e/attachment.html