[rt-devel] RT Reports

[Alex Rebrik]

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