What do you use for reports out of RT? For reports out of our banking
system, I can write queries and other reports with a product we have
(Cognos Impromptu) and assume thru an ODBC connection or something I
could connect to the database and generate the information I need and
make the reports a little prettier and easier to read then doing just
printing a query result from the RT web page.
So how do you all generate reports for the boss? Or people interested?
Thanks,
Jonathan
At Thursday 12/15/2005 10:49 AM, Jonathan Jesse wrote:
What do you use for reports out of RT? For reports out of our banking
system, I can write queries and other reports with a product we have
(Cognos Impromptu) and assume thru an ODBC connection or something I could
connect to the database and generate the information I need and make the
reports a little prettier and easier to read then doing just printing a
query result from the RT web page.So how do you all generate reports for the boss? Or people interested?
Thanks,
Jonathan
Hello Jonathan
You can certainly point a reporting tool at the database, but the schema is
a little hard to understand (as it is with many transactional DBs) and so
you’d need to do quite a bit of studying before you could be certain your
reports were showing what you wanted.
You could move some RT data to a data warehouse but that would probably be
a sizeable project and would again require you to learn the RT schema
fairly well.
A quick alternative is to use the spreadsheet download link that appears on
the bottom of the search results page - you can get data into Excel and
perhaps use Impromptu or whatever to make reports from that.
I’d be interested to hear what you decide.
Steve
I am in a similar situation in that I need to generate reports out of RT
(for SOX compliance). We’re using a commercial helpdesk ticketing app now,
but it’s missing some key features that RT has (and RT is much easier to
use and infinitely more flexible). Before I could begin to convert to RT, I
had to make sure I could get the reports out of it that I needed.
My SQL skills are minimal, so I opted to quickly “prototype” the reports
using MS Access and the mySQL ODBC connector. Stephen is right in that the
db schema takes some getting used to. (But definitely doable) I found the
easiest method in Access was to create several (read: many) layered queries
to assemble the ticket. Then, I just base the reports off queries with the
appropriate criteria (e.g. All new/open/stalled tickets; All tickets closed
in the last 7 days; All tickets for an Organization & date range, etc).
Stephen also brings up a good point about the export list feature in to
Excel. But, in my situation, I need the ticket history included in the
report, not just the header info/stats.
(Now I’m using the API to import my users and, hopefully, the ticket history
from the existing app - about 21,000 tickets).
If you think you’re going to use Access, let me know. I’d be happy to share
what I’ve learned, tripped over and worked-around.
Good Luck
Steve
I am in a similar situation in that I need to generate reports out of RT
(for SOX compliance). We’re using a commercial helpdesk ticketing app now,
but it’s missing some key features that RT has (and RT is much easier to
use and infinitely more flexible). Before I could begin to convert to RT, I
had to make sure I could get the reports out of it that I needed.My SQL skills are minimal, so I opted to quickly “prototype” the reports
using MS Access and the mySQL ODBC connector. Stephen is right in that the
db schema takes some getting used to. (But definitely doable) I found the
easiest method in Access was to create several (read: many) layered queries
to assemble the ticket. Then, I just base the reports off queries with the
appropriate criteria (e.g. All new/open/stalled tickets; All tickets closed
in the last 7 days; All tickets for an Organization & date range, etc).Stephen also brings up a good point about the export list feature in to
Excel. But, in my situation, I need the ticket history included in the
report, not just the header info/stats.(Now I’m using the API to import my users and, hopefully, the ticket history
from the existing app - about 21,000 tickets).If you think you’re going to use Access, let me know. I’d be happy to share
what I’ve learned, tripped over and worked-around.Good Luck
Steve
All of this is a lot of work and potentially wasted effort if the
schema changes in future versions of RT. We do all our reporting
with perl scripts that use the RT API. The reports are run from
cron and even devlivered to interested parties as resolved RT
tickets, so that we have a convienient place to store the reports
for later reference.
-Todd
I can understand that, but if you are going to spend time
learning something, your time would be better spent
learning perl/RT API than figuring out the schema. Going
the perl route will serve you many times over, for many
tasks.On Thu, Dec 15, 2005 at 02:12:17PM -0500, Hersker, Steve wrote:
Todd - that certainly sounds a lot more robust and efficient, but I
unfortunately have no in-house Perl skills (and am only just now learning
the basics for my attempt to import data using the API).Would you be willing to share a sample of one of your reporting scripts?
All of this is a lot of work and potentially wasted effort if the
schema changes in future versions of RT. We do all our reporting
with perl scripts that use the RT API. The reports are run from
cron and even devlivered to interested parties as resolved RT
tickets, so that we have a convienient place to store the reports
for later reference.-Todd
I can understand that, but if you are going to spend time
learning something, your time would be better spent
learning perl/RT API than figuring out the schema. Going
the perl route will serve you many times over, for many
tasks.
On the other hand, learning SQL is useful for a lot of
other things too, and you are a lot more likely to find
pretty reporting tools that work natively with SQL databases
than anything that works with RT’s API.
Les Mikesell
les@futuresource.com
Hello!On Thu, 2005-12-15 at 11:52, Les Mikesell wrote:
On Thu, 2005-12-15 at 14:22, Todd Chapman wrote:
I can understand that, but if you are going to spend time
learning something, your time would be better spent
learning perl/RT API than figuring out the schema. Going
the perl route will serve you many times over, for many
tasks.On the other hand, learning SQL is useful for a lot of
other things too, and you are a lot more likely to find
pretty reporting tools that work natively with SQL databases
than anything that works with RT’s API.
My free advice, which is worth exactly what you paid for it:
Using RT’s Perl API is an order of magnitude faster and far, far more
safe – I know the report I wrote for v3.0.1 will work in v3.5.5 (and
most of the stuff I wrote for v2.2 as well). If your knowledge of both
Perl and SQL is sketchy, I’d recommend learning Perl as it’s (a) more
utilitarian, and (b) the API will give you data integrity which, unless
you know SQL to reasonable depth, you can’t guarantee on your own. Is it
worthwhile to make reports fast if you’re not sure they’re accurate?
I know both Perl and SQL to reasonable depth. I’ve got all sorts of SQL
reporting tools handy that I could use if I chose to. I don’t choose to.
Cheers!
–j
Jim Meyer, Geek at Large purp@acm.org
Wow, lots of replies thanks guys… I think I will tackle it two ways.
Thanks again,
JonathanFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Jim Meyer
Sent: Thursday, December 15, 2005 3:39 PM
To: Les Mikesell
Cc: Hersker, Steve; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] question on reporting
Hello!
I completely agree with you. And, I plan on getting in to Perl and the API
as soon as I can (already started to a small extent - using it to import my
users). But, unfortunately I’m under a deadline and was taking the shortest
distance between two points (for me at least).
Would you mind sharing one or two of your Perl report scripts that I could
use as a starting point/example? That would be a huge help in getting to
know both Perl and the API.
Thx
SteveFrom: Todd Chapman [mailto:todd@chaka.net]
Sent: Thu 12/15/2005 3:22 PM
To: Hersker, Steve
Cc: rt-users@lists.bestpractical.com; Jonathan Jesse
Subject: Re: [rt-users] question on reporting
I can understand that, but if you are going to spend time
learning something, your time would be better spent
learning perl/RT API than figuring out the schema. Going
the perl route will serve you many times over, for many
tasks.
Todd - that certainly sounds a lot more robust and efficient, but I
unfortunately have no in-house Perl skills (and am only just now learning
the basics for my attempt to import data using the API).
Would you be willing to share a sample of one of your reporting scripts?
Stephen,
WE have the same problem. Many of our users can deal with the
spreadsheet to Excell then modify scenerio but we also have some users
that feel the query tool is inadequate AND SLOW. We are creating a
Report Link for the home page that will give our users the option of
writing a COGNOS report or using a more powerful SQL tool. We are on
Oracle 9. Developing this as a link allows us to add the link to any
upgrades we download from RT without a lot of fuss.
Kenn
Stephen Turner wrote:
I also need the ticket history in the report for SOX compliance. Maybe
Mr. Vincent can get that done somehow? Or maybe someone knows how to pull
that from the custom fields of the Ticket Query screen?
Aubrey King
Systems Engineer
IP Systems Engineering
Global Crossing, Ltd.On Thu, 15 Dec 2005, Hersker, Steve wrote:
I am in a similar situation in that I need to generate reports out of RT
(for SOX compliance). We’re using a commercial helpdesk ticketing app now,
but it’s missing some key features that RT has (and RT is much easier to
use and infinitely more flexible). Before I could begin to convert to RT, I
had to make sure I could get the reports out of it that I needed.My SQL skills are minimal, so I opted to quickly “prototype” the reports
using MS Access and the mySQL ODBC connector. Stephen is right in that the
db schema takes some getting used to. (But definitely doable) I found the
easiest method in Access was to create several (read: many) layered queries
to assemble the ticket. Then, I just base the reports off queries with the
appropriate criteria (e.g. All new/open/stalled tickets; All tickets closed
in the last 7 days; All tickets for an Organization & date range, etc).Stephen also brings up a good point about the export list feature in to
Excel. But, in my situation, I need the ticket history included in the
report, not just the header info/stats.(Now I’m using the API to import my users and, hopefully, the ticket history
from the existing app - about 21,000 tickets).If you think you’re going to use Access, let me know. I’d be happy to share
what I’ve learned, tripped over and worked-around.Good Luck
Steve-----Original Message-----
From: Stephen Turner [mailto:sturner@MIT.EDU]
Sent: Thursday, December 15, 2005 11:20 AM
To: Jonathan Jesse; rt-users@lists.bestpractical.com
Subject: Re: [rt-users] question on reportingAt Thursday 12/15/2005 10:49 AM, Jonathan Jesse wrote:
What do you use for reports out of RT? For reports out of our banking
system, I can write queries and other reports with a product we have
(Cognos Impromptu) and assume thru an ODBC connection or something I
could
connect to the database and generate the information I need and make the
reports a little prettier and easier to read then doing just printing a
query result from the RT web page.So how do you all generate reports for the boss? Or people interested?
Thanks,
Jonathan
Hello Jonathan
You can certainly point a reporting tool at the database, but the schema
is
a little hard to understand (as it is with many transactional DBs) and so
you’d need to do quite a bit of studying before you could be certain your
reports were showing what you wanted.You could move some RT data to a data warehouse but that would probably be
a sizeable project and would again require you to learn the RT schema
fairly well.A quick alternative is to use the spreadsheet download link that appears
on
the bottom of the search results page - you can get data into Excel and
perhaps use Impromptu or whatever to make reports from that.I’d be interested to hear what you decide.
Steve
Be sure to check out the RT Wiki at http://wiki.bestpractical.com
Download a free sample chapter of RT Essentials from O’Reilly Media at
http://rtbook.bestpractical.comWE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at
http://bestpractical.com/services/training.html
Be sure to check out the RT Wiki at http://wiki.bestpractical.com
Download a free sample chapter of RT Essentials from O’Reilly Media at http://rtbook.bestpractical.com
WE’RE COMING TO YOUR TOWN SOON - RT Training in Amsterdam, Boston and
San Francisco - Find out more at http://bestpractical.com/services/training.html