[rt-devel] RT Reports

[Kogami, Bruce]

Alex,

Would it be possible to add the number of open and new tickets and the total of each?

Is it a pretty easy addition or a lot of extra SQL queries?

Thanks,
Bruce

-----Original Message-----
From: Alex Rebrik [mailto:arebrik at zappos.com] 
Sent: Wednesday, March 31, 2004 3:33 PM
To: Kogami, Bruce
Cc: rt-devel at lists.bestpractical.com
Subject: Re: [rt-devel] RT Reports

Bruce,

In our case GroupId='74' - is to restrict report to members of a certain 
group - i.e. our customer care dept. This is so that if a supervisor 
(who are abstracted to a different group) do resolve a ticket it won't 
show up on the report.

mysql> desc Groups;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| Name | varchar(200) | YES | | NULL | |
| Description | varchar(255) | YES | | NULL | |
| Domain | varchar(64) | YES | MUL | NULL | |
| Type | varchar(64) | YES | MUL | NULL | |
| Instance | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.02 sec)

The id field in Groups table corresponds to GroupId field in GroupMembers:

mysql> desc GroupMembers;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| GroupId | int(11) | | MUL | 0 | |
| MemberId | int(11) | | | 0 | |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

The simplest way to find out numeric goup id for a group is to look up 
its name via Web UI and then a simple select should suffice:)

mysql> select * from Groups where Name='CLTeam';
+----+--------+-----------------------+-------------+------+----------+
| id | Name | Description | Domain | Type | Instance |
+----+--------+-----------------------+-------------+------+----------+
| 74 | CLTeam | Customer Loyalty Team | UserDefined | | 0 |
+----+--------+-----------------------+-------------+------+----------+
1 row in set (1.09 sec)

Voila, now you know numeric group id and can run a report on a 
particular group of users for your RT installation.

--Alex

P.S. Did anyone ever do a visual DB model for RT? I know the schema 
isn't that complicated to be completely out of human mind grasp, though 
visualizations do help. I think I'll make one of these tonight...



Kogami, Bruce wrote:

> 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 -800 *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.*
>
> *****************************************************************
>



***************************************************************
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.
***************************************************************