[rt-users] cannot display requestor or requestors attributes in rt CLI when specifying fields

[Chris Herrmann]

Hi all,

First post so apologies in advance if I miss something. Long time user, but have only just started playing with the CLI. What I'm trying to do is setup a bash script that uses the CLI to export data from RT on a regular basis.

Environment details:
- RT 3.8.7
- Ubuntu x64 2.6.32-26-server
- Apache/2.2.14
- Perl v5.10.1 (*) built for x86_64-linux-gnu-thread-multi
- Modules / Plugins:
o RTFM 
o RTx::EmailCompletion
o RT::Extension::MergeUsers
- MySQL 5.1.41-3
Sorry not sure what versions of these modules & plugins.

Originally I had another problem which is that I couldn't get a bash script to execute an RT cli, pass in variables, and email the results as an attachment to a specified address but that's now sorted... except that if I cannot get the email address associated with a ticket requestor.

To illustrate:
rt> show ticket/71900
Date: Wed Jan 12 08:21:05 2011
From: chrisdotherrrmannatfaredgedotcomdotau
X-Queue: helpdesk
Subject: [rt #ticket/71900] New Service Call  -  Default Wallpaper too large
rt>

but if I try and get the requestor email (which is me):

rt> list "id = 71900" -f id,requestor,requestors,subject
id      Subject Requestors
71900   New Service Call - Default Wallpaper too large     ARRAY(0x7fbf6db32a58)
              
rt> list "id = 71900" -f id,requestor,subject
id      Subject
71900   New Service Call - Default Wallpaper too large

rt> list "id = 71900" -f id,requestors,subject
id      Subject Requestors
71900   New Service Call - Default Wallpaper too large     ARRAY(0x7fbf6dd46788)

So... the "requestors" field returns "ARRAY(0x......)" whereas the "requestor" field is just ignored. The term "Requestor" is used if you run:

rt list "id = 71900"

but the results are obscured because they're too long to fit on the screen properly.

 If I use "from" instead (because this is what the "show ticket/71900"  calls it... I get the same result as if I'd used "requestor". In fact if I use any random non-reserved word in there it's ignored, which leads me to suspect that "requestor" is not actually what I'm looking for. I originally used "requestors" because this is what the export spreadsheet function from the GUI calls the field - and indeed if you export a spreadsheet from a list of tickets in the GUI it correctly exports the email address of the requestor (but ignores the settings relating to which fields to export - it exports every field in it's own order that does not match the order of the column headings, but that's a separate issue...).

I've also tried things like "requestors.email" but this returns:

rt> list "id = 71900" -f id,requestors.email,subject
rt: list: No valid field list in '-f id,requestors.email,subject'.
rt: For help, run 'rt help list'.
rt: For help, run 'rt help ticket'.
                                                                                                                                                                                              rt>
If I try to use the CC or AdminCC fields from the cli they experience the same issue.

Does anyone have any ideas on things I could try? Alternatively, is there some nifty way that RT can be configured to produce a friendly CSV style file and email it on a regular schedule including custom field data? If anyone's interested what I've got so far is attached below.

I started looking at doing this direct from a database but my head started hurting - for example there's no simple way of returning the current requestor - you have to traverse all relevant transactions and work it out that way (and if there is > 1 requestor make a decision about which one to choose). So if you have a db script that will do this then I'll gratefully look & test it but I probably won't venture down the path of writing it myself just now...

Regards,

Chris Herrmann
Far Edge Technology

p. 02 84251400
m. 0403 393309
http://www.faredge.com.au   



startmonth=`date +%m`; export startmonth
startyear=`date +%Y`; export startyear
endyear=$startyear; export endyear
friendlymonth=`date +%b`
reportperiod=$friendlymonth" "$startyear
if [ $startmonth = 12 ];then
	endyear=$(($startyear+1))
	endmonth=1
else
	endyear=$startyear
	endmonth=$(($startmonth+1))
fi
export endyear
startdate=1/$startmonth/$startyear ; export startdate
enddate=1/$endmonth/$endyear ; export enddate
touch blankreport #doing this because Mutt expects data to be in the message

RTUSER=reportinguser; export RTUSER
RTSERVER=http://myrtserver/; export RTSERVER
RTPASSWD=reportinguserpass; export RTPASSWD
rt list "queue = 'myrtqueue' and created > '$startdate' and created < '$enddate' and (status !='rejected' AND status != 'deleted')" -f id,subject,requestor,created,CF-firstname,CF-surname,CF-phone,CF-addressline1,CF-addressline2,CF-state,CF-postcode,CF-typeofdevice,CF-dateofpurchase,CF-ReceiptNo,CF-tickettype > /tmp/$startyear$startmonth"CallReport.txt"
mutt -s "Monthly Queue Results" -a /tmp/$startyear$startmonth"MontlyCallReport.txt" -- chrisdotherrrmannatfaredgedotcomdotau < blankreport
rm -f blankreport
rm -f /tmp/$startyear$startmonth"MonthlyCallReport.txt"