Ticket view w/ watchers

RT Users
1

I know it’s not the recommended way, but I’d like to build a view (I’m
using Pg) that displays a ticket summary along with the requestor(s).
I’m building a view so that I can easily use external reporting
software (Crystal Reports) to build a nice set of reports based on the
information in RT.

I did create a view based on creator, but there are plenty of times in
our organization that tickets are created on behalf of another, so I
attempted integrating information from transactions, but it was beyond
my sad sql skills. I know there would be a limit on requestors, and
that’s fine, or if someone knows a better way to integrate rt
information in Crystal, that would be useful, too.

I saw that 3.3 had revamped watcher code – would that make this
simpler?

Thanks
-Micah
Micah J. Cooper
Manager, Computing Services
School of Engineering & Applied Science
Miami University, Oxford OH 45056

smime.p7s (3.63 KB)

2

Well, what I’ve come up with for my view is as follows (let me know if
this looks egregiously wrong):

CREATE VIEW public.eas_qwiktik
AS
SELECT u.name AS requestor, u.organization AS dept, g.instance AS
ticketid, t.subject, t.priority, t.status, t.created, t.resolved,
u2.name AS “owner”
FROM groupmembers gm
JOIN principals p ON gm.memberid = p.id
JOIN users u ON u.id = p.id
JOIN groups g ON g.id = gm.groupid
JOIN tickets t ON g.instance = t.id
JOIN users u2 ON u2.id = t.“owner”
WHERE (g.“type” = ‘Requestor’ AND t.status <> ‘deleted’);

smime.p7s (3.63 KB)