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@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@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@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@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 :slight_smile:

–Alex

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@zappos.comDate: 2004-03-09 15:35

2004-03-09 23:35
-800
UTC

To: rt-devel mailto:rt-devel@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 :slight_smile:

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

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@zappos.comDate: 2004-03-09 15:35

2004-03-09 23:35
-800
UTC

To: rt-devel mailto:rt-devel@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 :slight_smile:

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

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,

Alex,

Thank you very much for that walk through. If you ever do a DB model for RT, could you post it or send me a copy? As you said, it’s not very hard but having visualizations really does help.

BruceFrom: Alex Rebrik [mailto:arebrik@zappos.com]
Sent: Wednesday, March 31, 2004 3:33 PM
To: Kogami, Bruce
Cc: rt-devel@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@zappos.com
*Date: * 2004-03-09 15:35 -800 *To: *rt-devel
mailto:rt-devel@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 :slight_smile:

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

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…

http://wiki.bestpractical.com/cgi-bin/index.cgi?FAQ

PGP.sig (186 Bytes)

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,
BruceFrom: Alex Rebrik [mailto:arebrik@zappos.com]
Sent: Wednesday, March 31, 2004 3:33 PM
To: Kogami, Bruce
Cc: rt-devel@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@zappos.com
*Date: * 2004-03-09 15:35 -800 *To: *rt-devel
mailto:rt-devel@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 :slight_smile:

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