Hello. What’s the easiest way to produce a report that shows the amount of time worked for a group of tickets? For example, I’d like to know how much time was worked for tickets classified as malware in a given time period. Is it also possible to determine the amount of time each analyst worked on tickets for a week or month period?
Brian
Is there a tool to generate reports from RT? I guess I could bluff my
way through doing something with SQL, but I’m lazy.
Randy Millis
Programmer Analyst
Electrical and Computer Engineering
University of Calgary
2500 University Dr. N.W.
Calgary, Alberta
Canada T2N 1N4
Tel: 403-220-4864 Fax: 403-282-6855
rmillis@enel.ucalgary.ca
ftp://ftp.fsck.com/pub/rt/contribOn Wed, Jun 07, 2000 at 08:45:51AM -0600, Randy Millis wrote:
Is there a tool to generate reports from RT? I guess I could bluff my
way through doing something with SQL, but I’m lazy.–
Randy Millis
Programmer Analyst
Electrical and Computer Engineering
University of Calgary
2500 University Dr. N.W.
Calgary, Alberta
Canada T2N 1N4
Tel: 403-220-4864 Fax: 403-282-6855
rmillis@enel.ucalgary.ca
http://www.enel.ucalgary.ca/
rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users
jesse reed vincent — root@eruditorum.org — jesse@fsck.com
pgp keyprint: 50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
I admit that X is the second worst windowing system in the world, but all the
others I’ve used are tied for first.
Jesse wrote:
Is there a tool to generate reports from RT? I guess I could bluff my
way through doing something with SQL, but I’m lazy.
Thanks!
You guys rock!
Is there a tool to generate reports from RT? I guess I could bluff my
way through doing something with SQL, but I’m lazy.
Statistical reports, or alarms about old requests?
Both things should exist for RT1 …
“The trouble with the world is that the stupid are
cocksure and the intelligent are full of doubt.”
Hi,
I am looking for a (Perl-) Script which reminds every user by email of his
open tickets. I know rt-reports from the contrib aerea, but I need a
script for RT2 based on DBI, because I am using Postgres.
Any hints?
Regards,
Elmar
Elmar Knipp wrote:
I am looking for a (Perl-) Script which reminds every user by email of his
open tickets. I know rt-reports from the contrib aerea, but I need a
script for RT2 based on DBI, because I am using Postgres.
contrib/2.0/nag is probably what you seek.
Against my will and better judgment, I have to run weekly reports for
management to review (for regulatory compliance). A list of all open & new
tickets (with details and complete ticket history for each) and all tickets
closed the previous week (again with details and history) are two examples.
Before I can put RT in production, I have to find a way to generate these
reports. Has anyone done anything similar? If anyone just has the generic
SQL statement(s) that puts together the ticket, transactions and
attachments, I’d appreciate it.
Thanks
Steve
Against my will and better judgment, I have to run weekly reports for
management to review (for regulatory compliance). A list of all open & new
tickets (with details and complete ticket history for each) and all tickets
closed the previous week (again with details and history) are two examples.Before I can put RT in production, I have to find a way to generate these
reports. Has anyone done anything similar? If anyone just has the generic
SQL statement(s) that puts together the ticket, transactions and
attachments, I’d appreciate it.
That sounds unpleasant.
You should never try to access RT data by going to the database
directly. The perl API is the preferred method.
One option you might want to consider is writing a program that
can drive a web browser to run the queries and print each ticket.
It would look nicer than text reports, and you wouldn’t have to
worry about the Perl API. Just a thought.
-Todd
Against my will and better judgment, I have to run weekly reports for
management to review (for regulatory compliance). A list of all open & new
tickets (with details and complete ticket history for each) and all tickets
closed the previous week (again with details and history) are two examples.Before I can put RT in production, I have to find a way to generate these
reports. Has anyone done anything similar? If anyone just has the generic
SQL statement(s) that puts together the ticket, transactions and
attachments, I’d appreciate it.That sounds unpleasant.
You should never try to access RT data by going to the database
directly. The perl API is the preferred method.One option you might want to consider is writing a program that
can drive a web browser to run the queries and print each ticket.
It would look nicer than text reports, and you wouldn’t have to
worry about the Perl API. Just a thought.
I had a similar issue, and wound up using direct SQL.
I wanted to find all open tickets in a few selected queues,
where the requestor was the last to correspond, and in which
that correspondance was more than 2 days ago. (E.g. we’re
ignoring the client, so I need to rap some knuckles.)
I thought, go to the query builder, have it tell me the
correct TicketSQL, then use that with the CLI in a cron job.
But I was just unable to figure out the TicketSQL for this.
I would never update the db without using the perl API.
But it seems to me that using direct SQL for reports
is fairly benign, no? Yes, the reports can fail if an
upgrade changes the schema in a backward non-compatible
way, or can become ineffecient if there are new
ways to do things. But overall, this is a reasonable
risk, is it not?
bobg
One thing that has been done very successfully for an RT implementation I have done in the past, is reports generated via MS Access.
I know, I know… the dark one cometh…
The scenario was essentially similar for me, but I was -very- disinterested in making reports happen. Especially for management who were very pro Microsoft. The answer we chose was that a Read-Only account was set up in the MySQL database, and then the managers in question could query the DB using their Microsoft Access clients.
This worked very well, because it allowed them to fully view what was going on in the ticket management system, without them having any ability to modify anything. They could then use all of the gooey goodness (pukes) in MS Access to generate their Crayola Compliant management reports.
The thing that was the biggest pain, was deciphering the schema of the database for RT. We found an old schema diagram, but even then we had to essentially decode it from scratch. (Here’s a suggestion for you Jesse. It would be extremely useful at times to have a full Entity-Relation Model data diagram showing the RT schema and its relationships. As I said there is an older diagram floating around, but its a long way from an ER model).
Regards,
BenR-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com on behalf of Todd Chapman
Sent: Sat 11/5/2005 2:39 AM
To: Hersker, Steve
Cc: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Reports
On Fri, Nov 04, 2005 at 09:37:21AM -0500, Hersker, Steve wrote:
Against my will and better judgment, I have to run weekly reports for
management to review (for regulatory compliance). A list of all open & new
tickets (with details and complete ticket history for each) and all tickets
closed the previous week (again with details and history) are two examples.Before I can put RT in production, I have to find a way to generate these
reports. Has anyone done anything similar? If anyone just has the generic
SQL statement(s) that puts together the ticket, transactions and
attachments, I’d appreciate it.
That sounds unpleasant.
You should never try to access RT data by going to the database
directly. The perl API is the preferred method.
One option you might want to consider is writing a program that
can drive a web browser to run the queries and print each ticket.
It would look nicer than text reports, and you wouldn’t have to
worry about the Perl API. Just a thought.
-Todd
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Be sure to check out the RT Wiki at http://wiki.bestpractical.com
Buy your copy of our new book, RT Essentials, today!
Download a free sample chapter from http://rtbook.bestpractical.com
Hi Steve,
the simpliest method is to define a custom search for a specific queue
within RT with all the Fields you need. Save this search for later
usage! If you do the search, the view you will get gives you at the
bottom a link called (spreadsheet) which will open the result within
Excel. This is only a very simple way but i think it will work for your
needs.
Another way i can explain to you if you need more detailed reports. I
have created during the last weeks a connectivity between the RT
database and Business Objects to create much more better results - but
this makes only sense if you have a Business Objects license.
Torsten
Hersker, Steve schrieb:
One thing that has been done very successfully for an RT implementation I have done in the past, is reports generated via MS Access.
I know, I know… the dark one cometh…
The scenario was essentially similar for me, but I was -very- disinterested in making reports happen. Especially for management who were very pro Microsoft. The answer we chose was that a Read-Only account was set up in the MySQL database, and then the managers in question could query the DB using their Microsoft Access clients.
This worked very well, because it allowed them to fully view what was going on in the ticket management system, without them having any ability to modify anything. They could then use all of the gooey goodness (pukes) in MS Access to generate their Crayola Compliant management reports.
The thing that was the biggest pain, was deciphering the schema of the database for RT. We found an old schema diagram, but even then we had to essentially decode it from scratch. (Here’s a suggestion for you Jesse. It would be extremely useful at times to have a full Entity-Relation Model data diagram showing the RT schema and its relationships. As I said there is an older diagram floating around, but its a long way from an ER model).
Regards,
BenR
I never give anyone direct access (even read-only) because there
is no enforcement of ACLs.
I had a similar issue, and wound up using direct SQL.
I wanted to find all open tickets in a few selected queues,
where the requestor was the last to correspond, and in which
that correspondance was more than 2 days ago. (E.g. we’re
ignoring the client, so I need to rap some knuckles.)I thought, go to the query builder, have it tell me the
correct TicketSQL, then use that with the CLI in a cron job.
But I was just unable to figure out the TicketSQL for this.I would never update the db without using the perl API.
But it seems to me that using direct SQL for reports
is fairly benign, no? Yes, the reports can fail if an
upgrade changes the schema in a backward non-compatible
way, or can become ineffecient if there are new
ways to do things. But overall, this is a reasonable
risk, is it not?bobg
As long as you also want to risk having to redo your
reports if a future RT version has a schema change.
Hi Steve,
the simpliest method is to define a custom search for a specific queue
within RT with all the Fields you need. Save this search for later
usage! If you do the search, the view you will get gives you at the
bottom a link called (spreadsheet) which will open the result within
Excel. This is only a very simple way but i think it will work for your
needs.Another way i can explain to you if you need more detailed reports. I
have created during the last weeks a connectivity between the RT
database and Business Objects to create much more better results - but
this makes only sense if you have a Business Objects license.
There would be a middle way – take the template for search results and
instead of the lines pull in the templates, or parts of them, of ticket
details page. Then build a query for the relevant tickets and they can be
shown with the details you need.
Jan 'Bulb' Hudec <bulb@ucw.cz>
signature.asc (189 Bytes)
I was just looking at the built-in reports, and I couldn’t find
anything on the wiki or list archives about customizing them or
creating new reports.
What’s the most common approach? RT3StatisticsPackage? My needs are
pretty simple. Right now just have four main criteria to report on:
For a user-definable time period, stats on:
- messages received
- replies sent
- tickets / category (custom field)
Thanks,
Steve
Steve Cochran wrote:
I was just looking at the built-in reports, and I couldn’t find
anything on the wiki or list archives about customizing them or
creating new reports.What’s the most common approach? RT3StatisticsPackage? My needs are
pretty simple. Right now just have four main criteria to report on:For a user-definable time period, stats on:
- messages received
- replies sent
- tickets / category (custom field)
It’s probably a god-awful way to do it, but failing any better way I
could find at the time, I threw together some custom perl scripts called
via cron jobs together that end up calling this doQuery($query) :
export RTSERVER="<SERVER_URL>"; export RTUSER="<RTUSER>"; export RTPASSWD="<RTPASS>"; cd <RT_REPORTS_DIR>; echo -e "***\nOutput of Query: $query\n\n" >> <REPORT_LOG_FILE>; ../bin/rt list -i "$query" | ../bin/rt show - -f id,subject,owner,status,created,lastupdated >> <REPORT_LOG_FILE>; echo -e "\n***" >> <REPORT_LOG_FILE>
Apologies for the bad coding - but if you want to clean it up and send
it back 
…
Kind Regards,
Mike Peachey, IT
Tel: +44 114 281 2655
Fax: +44 114 281 2951
Jennic Ltd, Furnival Street, Sheffield, S1 4QT, UK
Comp Reg No: 3191371 - Registered In England