Help with Reports

RT Users
1

How do i get reports lets say for a particular client i want a print out
of support issues that i solved for the whole month how i get that?

Kind Regards,
Phillip
Phillip Leremi
Tel: 011 884 7633
Fax: 011 883 5805
phillip@reportstar.net

2

Phillip Leremi wrote:

How do i get reports lets say for a particular client i want a print out
of support issues that i solved for the whole month how i get that?

Did you ever try to search in ML?
Did you ever try to look on wiki?
Did you ever try spend some time on google?
Exactly this question answered on wiki.
Statistics addon.

	Ruslan.
3

Ruslan U. Zakirov wrote:

Phillip Leremi wrote:

How do i get reports lets say for a particular client i want a print
out of support issues that i solved for the whole month how i get that?

Did you ever try to search in ML?

yes

Did you ever try to look on wiki?

yes

Did you ever try spend some time on google?

yes

Exactly this question answered on wiki.

i didn’t c the answer

Statistics addon.

    Ruslan.

Kind Regards,
Phillip
Phillip Leremi
Tel: 011 884 7633
Fax: 011 883 5805
phillip@reportstar.net

4

yes u are right I will do it myself but the thing is I don’t know which field
contains the requesters name?On Thursday 15 April 2004 09:06 am, Helmut Lichtenberg wrote:

On Thu, Apr 15, 2004 at 09:00:37AM -0400, phillip wrote:

Thanks that solves half of my problem so how do I get the requstor name
into the report and and get all the tickets for the whole month?

Somebody has to make himself comfortable with the database structure and
create adequate sql statements.

I would prefer, if you do it. :^)

If would be nice if you finally could provide your polished reports to
rt-users.

Helmut

5

Thanks that solves half of my problem so how do I get the requstor name into
the report and and get all the tickets for the whole month?On Thursday 15 April 2004 08:44 am, Helmut Lichtenberg wrote:

On Thu, Apr 15, 2004 at 07:47:50AM -0400, phillip wrote:

I’m trying to write my own report directly . But what i want is to see
report containing only the name of the requester, the timeworked total
per month, the owners of each ticket by name not by number as in one of
the reports that i generated. so please help.

That’s basic sql. One example:

Select Tickets.id,
Tickets.TimeWorked,
Tickets.created,
Tickets.Priority,
Users.name
from tickets, users
where tickets.owner = users.id;

HTH
Helmut

6

This came up a couple of weeks ago:

http://lists.fsck.com/pipermail/rt-users/2004-April/022405.html

Steve

At Thursday 4/15/2004 09:15 AM, phillip wrote:

7

Hi Phillip,On Thu, 2004-04-15 at 23:00, phillip wrote:

Thanks that solves half of my problem so how do I get the requstor name into
the report and and get all the tickets for the whole month?

Perhaps something like this?

SELECT COUNT(Tickets.id) as resolvecount, Users.RealName,
sum(Tickets.TimeWorked) FROM Transactions, Users, Tickets WHERE
Tickets.id=Transactions.Ticket AND Users.id = Transactions.Creator AND
Transactions.Created >= ‘2004-03-01’ and Transactions.Created <=
‘2004-03-31’ Transactions.NewValue=‘resolved’ GROUP BY Users.Realname
ORDER BY resolvecount DESC;

That should generate a list of all users who have resolved tickets
during the month of March in descending order of how many tickets they
resolved and include their name and the sum of how many minutes they put
in the timeworked field.

This is basically a report from my Batch Stats reports available via the
wiki at;
http://wiki.bestpractical.com/index.cgi?RT3BatchStats

As far as I can tell, if time is added to an individual transaction,
then it is automatically summed into the ticket row. If someone knows
this is wrong then please email me.

Carl.

8

Thanks that solves half of my problem so how do I get the requstor name into
the report and and get all the tickets for the whole month?

Missed the requestor bit…

Try this;

SELECT Tickets.id as Id, U2.RealName as Owner, Users.RealName as
Requestor, Tickets.TimeWorked as TimeWorked, Tickets.Created as Created
FROM Tickets, Groups, GroupMembers, Users INNER JOIN Users as U2
ON(Tickets.Owner=U2.Id) WHERE Tickets.Created >= ‘2004-03-01’ and
Tickets.Created <= ‘2004-03-31’ and Groups.Domain = ‘RT::Ticket-Role’
and Groups.Type=‘Requestor’ and Groups.Instance=Tickets.id and
Groups.id=GroupMembers.GroupId and GroupMembers.MemberId=Users.Id ORDER
BY Tickets.Id;

Although I suspect it should only be tried by specially qualified stunt
DBAs.

Carl.

9

I have decided to use my clients company names to sort out my reporting. I
have created a custom field named company name but the problem is that i get
see this field on i run the folllowing command :-

SELECT *
FROM CustomFieldValues;

So how do make sure that i get all the custom field I created.On Thursday 15 April 2004 10:16 pm, Carl Makin wrote:

On Thu, 2004-04-15 at 23:00, phillip wrote:

Thanks that solves half of my problem so how do I get the requstor name
into the report and and get all the tickets for the whole month?

Missed the requestor bit…

Try this;

SELECT Tickets.id as Id, U2.RealName as Owner, Users.RealName as
Requestor, Tickets.TimeWorked as TimeWorked, Tickets.Created as Created
FROM Tickets, Groups, GroupMembers, Users INNER JOIN Users as U2
ON(Tickets.Owner=U2.Id) WHERE Tickets.Created >= ‘2004-03-01’ and
Tickets.Created <= ‘2004-03-31’ and Groups.Domain = ‘RT::Ticket-Role’
and Groups.Type=‘Requestor’ and Groups.Instance=Tickets.id and
Groups.id=GroupMembers.GroupId and GroupMembers.MemberId=Users.Id ORDER
BY Tickets.Id;

Although I suspect it should only be tried by specially qualified stunt
DBAs.

Carl.

10

When we create custom fields on RT3 database on with table do their go cause i
have created custom fields but i can’t find out on which table their are?

11

Ok I have found out where they are. There are in the table CustomFields but
the field that i what has a long list of columns and how do i see what each
column contains. cause i have tryed the following code but it doesn’t work.

mysql> select Name From CustomFields;
| Name |
| Which type of Network? |
| Quantity |
| Type of Sale |
| What type of Server? |
| What kind of support? |
| Tel |
| Company Name |
| Fax |
| Contact Person |
9 rows in set (0.00 sec)

mysql> select Name.Company Name From CustomFields;
ERROR 1109: Unknown table ‘Name’ in field listOn Tuesday 20 April 2004 06:02 am, phillip wrote:

When we create custom fields on RT3 database on with table do their go
cause i have created custom fields but i can’t find out on which table
their are?

The rt-users Archives

RT Developer and Administrator training is coming to LA, DC and Frankfurt
this spring and summer. http://bestpractical.com/services/training.html

Sign up early, as class space is limited.

12

mysql> select Name.Company Name From CustomFields;
ERROR 1109: Unknown table ‘Name’ in field list

You want:

SELECT * FROM CustomFields WHERE Name = "Company Name";

You may want to use the ID of the custom field to find out the
values each ticket associates with the field:

SELECT * FROM TicketCustomFieldValues WHERE CustomField = 7;

Thanks,
/Autrijus/

13

Hello,

Here’s what I found in CreateTicket function (file lib/RT/Interface/Web.pm):

foreach my $arg (%ARGS) {

    if ($arg =~ /^CustomField-(\d+)(.*?)$/) {

        next if ($arg =~ /-Magic$/);

        $create_args{"CustomField-".$1} = $ARGS{"$arg"};

    }

}

Does that mean that custom fields MUST have special names, i.e. start with
CustomField- in order to be processed correctly when a new ticket is
created?

Thanks,

Dimitry Faynerman

14

Hello,

I want to add some lines to ja.po file.
Should I change the original file from /lib/RT/I18N or I’m supposed to make
changes in some local directory?

Thanks
Dimitry Faynerman

15

Hello,

I want to add some lines to ja.po file.
Should I change the original file from /lib/RT/I18N or I’m supposed to make
changes in some local directory?

If they’re corrections to the core japanese translation, please send
them to rt-bugs@fsck.com and we’ll get them included in the next release
of RT.

16

If they’re corrections to the core japanese translation, please send
them to rt-bugs@fsck.com and we’ll get them included in the next release
of RT.

No they’re not corrections, just some new stuff.
So could anybody please give me an answer?

Thanks

17

No they’re not corrections, just some new stuff.
So could anybody please give me an answer?

Sure. This will do:

$RTPATH/local/po/YourOrganizationName/de.po

Thanks,
/Autrijus/

18

Ahhhh thanks!
I used $RTPATH/local/po/ja.po before and it didn’t work.
Didn’t know I was supposed to create a subdir for organization nameOn Wed, Apr 21, 2004 at 07:08:19AM -0700, Dimitry Faynerman wrote:

No they’re not corrections, just some new stuff.
So could anybody please give me an answer?

Sure. This will do:

$RTPATH/local/po/YourOrganizationName/de.po

Thanks,
/Autrijus/

19

Ahhhh thanks!
I used $RTPATH/local/po/ja.po before and it didn’t work.

Hmm, according to the Principle of Least Surprise, maybe this should
Just Work, too. Jesse, what do you think?

Thanks,
/Autrijus/