January 2006 - otrs - OTRS mailing lists

After Update 1.3 to 2.0.4 queue_standard_response missed
by lsimail 05 Jan '06

05 Jan '06

Hello everyone, I installed on one test machine OTRS 2.0.4. On the other product machine I upgraded otrs - database (1.3 to 2.0.4) like in "UPGRADING"-File. By comparing both otrs DBs on test and product machines, I noticed, that otrs - DB on product machine missed the table "queue_standard_response". Furthermore there are the tables "ticket_link" and "charset" on the product machine, that missed on the test machine. I wonder, if it's correct or I probably made a mistake. Thank you very much!

1 0

Notification for follow-ups
by Florian 05 Jan '06

05 Jan '06

Hi I'm using otrs for a few months and it's working fine except the notification : i get notifications for new tickets and notes but not for follow-ups added by agents... (I selected all the notifications in Preferences) Any idea which option it could be ? or how i can modify this in the code ? Thanks for your help ! -- Florian

1 0

New changes to open
by keith.d.turner＠gmail.com 04 Jan '06

04 Jan '06

A fast question, When does a ticket change from "new" to "open"? I told my employer that I thought it was when there was an owner other than the system, but no response. That didn't seem to be it though. Is it when a ticket has an owner and unlocks? OTRS really needs a separate Users Manual. The online manual is pretty good at answering configuration questions, but a brief explanation and best usage for each feature would make training non-administrating non-customer user far easier. Thanks. Keith

3 2

No queue list in Auto Response <-> Queue
by Colby W. 04 Jan '06

04 Jan '06

I am running OTRS 2.0.4 upgraded from 2.0.3 and I have run into a problem. When I click on the Auto Response <-> Queue link in the Admin section I do not get a list of queues with their auto response options. Restarting Apache and MySQL doesn't help and there are no errors in the otrs.log file when I access /index.pl?Action=AdminQueueAutoResponse Is this a bug with OTRS or something wrong on my end? ---- Colby Williams SysOp Engineer IKANO Communications http://www.ikano.com/

1 0

Calculating avg. ticket open time by queue
by Davis, Gary H 04 Jan '06

04 Jan '06

Is there a way to calculate the average time between states in OTRS? For example: we would like to know the average time between "open" and "closed successfully" for all tickets submitted to the "development" queue. Any help would be appreciated.... Thanks Gary Davis PSIC Lab Manger

2 1

RE: [otrs] Calculating avg. ticket open time by queue
by Seigafuse, Mike 04 Jan '06

04 Jan '06

Here is a query to return average ticket close by category (which is set using freefield1). It should be relatively easy to adapt this to your needs... select ifnull(freetext1,'Unclassified'), avg(TO_DAYS(t.change_time) - TO_DAYS(t.create_time)) from ticket t, ticket_state ts where t.ticket_state_id = ts.id and ts.id in (2,10) and DATE_FORMAT(t.create_time,'%Y') = DATE_FORMAT(now(),'%Y') group by freetext1; I actually use this to feed a plot routine for my system that returns the data in the form of a graph For more comprehensive reporting (beyond a simple graph) we have implemented more of an SLA type of report and I've attached part of the SQL for that (we do it by queue/geography). It is implemented outside of the OTRS database since we didn't want to mess with adding new tables and impact our ability to easily upgrade, etc. Here is the table structure for that: Table bangalore_sla in database oc Field name Type Allow nulls? Key Default value Extras tn <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=0> varchar(50) No None month <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=1> tinyint(4) No None 0 year <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=2> smallint(6) No None 0 submitter <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=3> varchar(50) No None closed_by <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=4> varchar(50) No None subject <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=5> tinytext Yes None queue <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=6> varchar(200) No None type <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=7> varchar(150) No None category <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=8> varchar(150) No None status <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=9> varchar(100) No None create_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=10> datetime No None 0000-00-00 00:00:00 resp_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=11> datetime Yes None close_time <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=12> datetime Yes None hours_to_resolve <http://palinux03.fcg.com:10000/mysql/edit_field.cgi?db=oc&table=bangalo re_sla&idx=13> decimal(7,2) Yes None Here is the relevant part of the script we run to refresh the SLA tables... sladir='/home/oc_sla' date=`date -I` logfile=$sladir/refresh_oc_sla_$date.log # # Refresh SLA data # mysql -u $user -h $host -D otrs < $sladir/bangalore.sql > $logfile Hope it helps someone - have fun. Mike _____ From: otrs-bounces(a)otrs.org [mailto:otrs-bounces@otrs.org] On Behalf Of Davis, Gary H Sent: Wednesday, January 04, 2006 12:29 PM To: otrs(a)otrs.org Subject: [otrs] Calculating avg. ticket open time by queue Is there a way to calculate the average time between states in OTRS? For example: we would like to know the average time between "open" and "closed successfully" for all tickets submitted to the "development" queue. Any help would be appreciated.... Thanks Gary Davis PSIC Lab Manger This email may contain material that is confidential, privileged and/or attorney work product for the sole use of the intended recipient. Any review, reliance or distribution by others or forwarding without express permission is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.

1 0

How many tanle are required for External Backends ?
by Florent Guiliani 04 Jan '06

04 Jan '06

Hi all, If you configure an external backend strangely OTRS 2.0.4 will make request on other tables than the only customer one. I've got a PostgresSQL database where OTRS is installed. My Customers are in an Oracle database so I've changed $Self->{CustomerUser} in Config.pm to override the internal default one. While searching and displaying current customers is OK, creating failed with: Software error: Can't call method "fetchrow_array" on an undefined value at /home/otrs//Kernel/System/DB.pm line 477. In fact OTRS try to select valid table in Oracle not in PostgresQL where the table is located. Where is the catch ? Here the apache logs: And in the apache logs: [Wed Jan 4 15:40:47 2006] -e: DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 15 in 'SELECT id FROM <*>valid WHERE name = 'valid'') [for Statement "SELECT id FROM valid WHERE name = 'valid'"] at /home/otrs//Kernel/System/DB.pm line 432. ERROR: OTRS-CGI-10 Perl: 5.8.0 OS: linux Time: Wed Jan 4 15:40:47 2006 Message: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 15 in 'SELECT id FROM <*>valid WHERE name = 'valid''), SQL: 'SELECT id FROM valid WHERE name = 'valid'' Traceback (27021): Module: Kernel::System::DB::GetValidIDs (v1.47) Line: 680 Module: Kernel::System::CustomerUser::DB::CustomerSearch (v1.36) Line: 178 Module: Kernel::System::CustomerUser::DB::CustomerUserAdd (v1.36) Line: 340 Module: Kernel::System::CustomerUser::CustomerUserAdd (v1.22) Line: 293 Module: Kernel::Modules::AdminCustomerUser::Run (v1.37 ) Line: 260 Module: Kernel::System::Web::InterfaceAgent::Run (v1.8) Line: 651 Module: ModPerl::ROOT::ModPerl::Registry::home_otrs_bin_cgi_2dbin_index_2epl::handler (v) Line: 48 Module: (eval) (v1.80) Line: 187 Module: ModPerl::RegistryCooker::run (v1.80) Line: 187 Module: ModPerl::RegistryCooker::default_handler (v1.80) Line: 159 Module: ModPerl::Registry::handler (v1.99) Line: 16 [Wed Jan 04 15:40:47 2006] [error] 27021: ModPerl::Registry: [Wed Jan 4 15:40:47 2006] -e: Can't call method "fetchrow_array" on an undefined value at /home/otrs//Kernel/System/DB.pm line 477.! Here my Config.pm: package Kernel::Config; sub Load { my $Self = shift; $Self->{DatabaseHost} = 'localhost'; $Self->{Database} = 'otrs'; $Self->{DatabaseUser} = '*********'; $Self->{DatabasePw} = '********'; $Self->{DatabaseDSN} = "DBI:Pg:dbname=$Self->{Database};"; $Self->{Home} = '/home/otrs'; $Self->{SecureMode} = 1; $Self->{'Ticket::Frontend::CustomerInfoCompose'} = 1; $Self->{'Ticket::Frontend::CustomerInfoZoom'} = 1; $Self->{'Ticket::Frontend::CustomerInfoQueue'} = 0; $Self->{FQDN} = 'support.perinfo.biz'; $Self->{AdminEmail} = 'fguiliani(a)perinfo.com'; $Self->{Organization} = 'Perinfo'; $Self->{DefaultLanguage} = 'fr'; $Self->{DefaultCharset} = 'iso-8859-15'; $Self->{CheckEmailAddresses} = 0; $Self->{SessionMaxTime} = 30*24*60*60; $Self->{SessionMaxIdleTime} = 3*24*60*60; $Self->{'TimeWorkingHours'} = { Mon => [ 9,10,11,12,13,14,15,16,17,18 ], Tue => [ 9,10,11,12,13,14,15,16,17,18 ], Wed => [ 9,10,11,12,13,14,15,16,17,18 ], Thu => [ 9,10,11,12,13,14,15,16,17,18 ], Fri => [ 9,10,11,12,13,14,15,16,17,18 ], Sat => [ ], Sun => [ ], }; $Self->{'TimeVacationDays'} = { 1 => { 1 => 'Nouvel an', }, 5 => { 1 => '1er mai', }, 7 => { 14 => '14 juillet', }, 11 => { 11 => '11 novembre', }, 12 => { 25 => 'Noël', 26 => 'Saint Etienne', }, }; $Self->{SwitchToUser} = 1; $Self->{SpellCheckerDictDefault} = 'francais'; $Self->{PreferencesGroups}->{SpellDict}->{Data} = { 'francais'=> 'Francais', 'english' => 'English', 'deutsch' => 'Deutsch', }; $Self->{CustomerGroupSupport} = 1; $Self->{CustomerPanelLogoutURL} = 'http://www.perinfo.com'; $ENV{ORACLE_HOME} = '/oracle/8.1.7'; $ENV{NLS_LANG} = "french_france.we8iso8859p1"; $ENV{LD_LIBRARY_PATH} = "/oracle/8.1.7/lib"; $Self->{CustomerUser} = { Name => 'BDD_GESCAR', Module => 'Kernel::System::CustomerUser::DB', Params => { DSN => 'DBI:Oracle:sid=perorcl;host=192.100.0.11;port=1521;', User => '********', Password => '**********', Table => 'otrs_customer', }, CustomerKey => 'login', CustomerID => 'customer_id', CustomerValid => 'valid_id', CustomerUserListFields => ['first_name', 'last_name', 'email'], CustomerUserSearchFields => ['login', 'last_name', 'customer_id'], CustomerUserSearchPrefix => '', CustomerUserSearchSuffix => '*', CustomerUserSearchListLimit => 250, CustomerUserPostMasterSearchFields => ['email'], CustomerUserNameFields => ['salutation', 'first_name', 'last_name'], CustomerUserEmailUniqCheck => 1, Map => [ [ 'UserSalutation', 'Salutation', 'salutation', 1, 0, 'var', '', 0 ], [ 'UserFirstname', 'Firstname', 'first_name', 1, 1, 'var', '', 0 ], [ 'UserLastname', 'Lastname', 'last_name', 1, 1, 'var', '', 0 ], [ 'UserLogin', 'Username', 'login', 1, 1, 'var', '', 0 ], [ 'UserPassword', 'Password', 'pw', 0, 1, 'var', '', 0 ], [ 'UserEmail', 'Email', 'email', 0, 1, 'var', '', 0 ], [ 'UserCustomerID', 'CustomerID', 'customer_id', 0, 1, 'var', '', 0 ], [ 'UserComment', 'Comment', 'comments', 1, 0, 'var', '', 0 ], [ 'UserPhone', 'Telephone', 'phone', 1, 0, 'var', '', 0 ], [ 'UserFax', 'Fax', 'fax', 1, 0, 'var', '', 0 ], [ 'ValidID', 'Validité', 'valid_id', 0, 1, 'int', '', 0 ], ], Selections => { UserSalutation => { 'M.' => 'M.', 'Mme.' => 'Mme.', 'Mlle.' => 'Mlle.', }, }, }; } use strict; use vars qw(@ISA $VERSION); use Kernel::Config::Defaults; push (@ISA, 'Kernel::Config::Defaults'); $VERSION = '$Revision: 1.16 $'; $VERSION =~ s/^\$.*:\W(.*)\W.+?$/$1/; 1; Florent,

1 0

Notification about new tickets
by Marc Grimme 04 Jan '06

04 Jan '06

Hello, I'm using otrs 2.0.4 and have a question about notification of new tickets. When I create a new ticket via Email and PostMasterPop3 fetches it, every agent who is in the group of the queue the Email-Address points to gets a notification. But when a customer creates a new ticket nobody gets notified. I had a look in the code and found out that $self->{GroupObject} is the reason for that behaviour. When a Customer creates a ticket $self->{GroupObject} is an instance of Kernel::System::CustomerGroup and when PostMaster creates a ticket it is an instance of Kernel::System::Group (in Kernel::System::Ticket.pm). As far as I understand it the ticket tries to send notifications to all members of the group of the queue and gets the agent/user objects from the context of $self->{GroupObject}. Now if that is a Kernel::System::CustomerGroup no agent/user will be found and no notification will be issued. But I cannot understand why agents will be notified when a customer creates a ticket via web and all agents will be notified otherwise. Or is this designed by concept and I just got something wrong or can I change that behaviour? Thanks for you help. Regards Marc. -- Gruss / Regards, Marc Grimme Phone: +49-89 121 409-54 http://www.atix.de/ ** ATIX - Ges. fuer Informationstechnologie und Consulting mbH Einsteinstr. 10 - 85716 Unterschleissheim - Germany

1 1

Can't select myself as owner
by Alexander Scholler 04 Jan '06

04 Jan '06

Hi Eilif, > I have to press "all" and then I am able to > choose myself as owner. Only these agents are listed in the owner-selection (without pressing "all") that have activated the selected queue as one of their "My Queues". Bye, Alex

1 0

no escalation emails
by Alexander Scholler 04 Jan '06

04 Jan '06

Hi Jochen, >I run OTRS 2.0.4 under Windows Server 2003 and my problem is that >I did not receive any emails when a ticket gets escalated. A ticket can only escalate if it's unlocked. To whom should such an escalation mail be send? As far as I know this feature doesn't exist. But a mail can be send when a ticket is unlocked automatically. For that, the queue the ticket is in must have set an unlock timeout. Further the unlock-Cronjob must be activated and your personal preferences must be set to activated unlock-mail-notification. How to set a cron job (planned tasks?) under M$-Windows? Don't ask me, consult the docs and mailing lists. Bye, Alex

1 0