I have written a perl script that does a time until resolution and tracks the number of days a ticket remains open. It could be a great starting point for you to modify and create a report that you are looking for. Keith Rabindra Pandey wrote:

<div class="moz-text-flowed" style="font-family: -moz-fixed">Hi,

I am fairly new to otrs world. I want to create a report of all tickets that have been untouched for last 3 days. Untouched is defined as a ticket which have no activity during last 3 days. Any mail sent from OTRS by system is not accounted as activity.

I want create report by writing mysql script and my otrs version is 1.2.3

Any help or guidance will be greatly appreciated.

Thanks

</div>

#!/usr/bin/perl #Description # This script will produce a way to get time to resolution for otrs tickets #Modules use strict; use warnings; use DBI; use Getopt::Long qw(:config pass_through); use Data::Dumper; use Date::Business; #vars my $dbh; my $sql; my $sth; my $i; my $j; my @loh; my %tickets; my %ttr; my $ddiff; my $format='tab'; my $help=0; my $headers=0; my $queue="Desktop"; my $start='20080401'; my $end = `/bin/date +%Y%m%d`; chomp $end; #Fetch options GetOptions ('format=s' => \$format, 'start=i' => \$start, 'end=i' => \$end, 'noheaders' =>\$headers, 'queue=s' =>\$queue, 'help|?' => \$help); #make sure that $start and $end are set to 8 digit values or bail help_msg() unless ($end =~m/^\d{8}$/)&&($start=~m/^\d{8}$/); #make sure that the end date is set to 11:59 pm so you get the activity for the whole day $end = $end."235959"; help_msg() if $ARGV[0] || $help == 1; help_msg() unless ($format eq 'tab') || ($format eq 'csv')|| ($format eq 'html'); #Help message sub help_msg { print "\notrs.pl\n\nDescription:\n\tThis script runs against the OTRS ticket tracking database calculates time to resolution. "; print "By default the script queries tickets in the Desktop queue.\n\nOptions:"; print "\n\t--queue\tSets the queue name for the report.\n\t\t\tCheck your ORTS installation for a list of valid queue names\n\t\t\t Default queue is Desktop\n"; print "\n\t--format\tSets the output format (default format is tabbed).\n\t\t\tValid formats are: tab csv html\n"; print "\n\t--start\t\tSets the start date for the report (default value is 04/01/2008).\n\t\t\tValid format is YYYYMMDD\n"; print "\n\t--end\t\tSets the end date for the report (default value is the current date).\n\t\t\tValid format is YYYYMMDD\n"; print "\n\t--noheaders\tCreates a single line of output devoid of headers and titles.\n\t\t\tOutput will be in the format start time end time total tickes ticket count and percent of total"; print "\n\t\t\tfor each of the 5 normal ranges\n"; print "\n\t--help\t\tDisplays this help message.\n"; print "\nUsage:\n\tTo run the default report (20080401 until now) in tabbed output:\n\t\tprompt> otrs.pl\n"; print "\tTo create a report formatted for csv output\n\t\tprompt> otrs.pl --format=csv\n"; print "\tTo create a report formatted for html output\n\t\tprompt> otrs.pl --format=html\n"; print "\tTo create a report from 01/01/2009 until now in csv output\n\t\tprompt> otrs.pl --start=20090101 --format=csv\n"; print "\tTo create a report from 01/01/2009 to 03/31/2009 at 23:59:59 in html\n\t\tprompt> otrs.pl --start=20090101 --end=20090331 --format=html\n"; print "\tTo create a report as a single line of output in csv formatting\n\t\tprompt> otrs.pl --format=csv --noheaders\n"; print "\tTo query the queue spam and return csv output.\n\t\tprompt> otrs.pl --format=csv --queue=spam\n"; exit; } #logic $dbh = DBI->connect('dbi:mysql:otrs','otrsreport','q5epR-Ez') or die "Connection Error: $DBI::errstr\n"; #test does the queue exist $sql="select count(*) from queue where name='$queue'"; $sth = $dbh->prepare($sql); $sth->execute or die "SQL Error: $DBI::errstr\n"; my @test1; @test1 = $sth->fetchall_arrayref(); if ( $test1[0][0][0] == 0 ) { print "Error: queue $queue does not exist. Check your available queues and run again.\n"; exit; } $sql = "select min(a.id) as uid, a.ticket_id, CASE d.name WHEN 'merged' THEN 'closed' WHEN 'closed successful' THEN 'closed' WHEN 'closed unsuccessful' THEN 'closed' WHEN 'pending reminder' THEN 'closed' WHEN 'pending auto close+' THEN 'closed' WHEN 'pending auto close-' THEN 'closed' ELSE 'open' END as status, cast(a.change_time as date) as day, cast(a.change_time as time) as time from ticket_history as a, queue as c, ticket_state as d, ticket e where a.queue_id = c.id and a.state_id = d.id and a.ticket_id = e.id and c.name='$queue' and d.name in ('new','closed successful','closed unsuccessful','open','pending reminder','pending auto close+','pending auto close-','merged') and e.create_time >= $start and e.create_time <= $end group by a.ticket_id, d.name, a.change_time order by a.ticket_id, a.change_time"; $sth = $dbh->prepare($sql); $sth->execute or die "SQL Error: $DBI::errstr\n"; @loh = $sth->fetchall_arrayref({}); for $i ( 0 .. $#{$loh[0]} ) { $j = $i; $j--; unless ( exists $tickets{$loh[0][$i]{ticket_id}} ) { $tickets { $loh[0][$i]{ticket_id} } = [ {day => $loh[0][$i]{day} , status => $loh[0][$i]{status}}]; } else { if ( $loh[0][$i]{status} ne $loh[0][$j]{status} ){ push @{ $tickets{$loh[0][$i]{ticket_id}} }, {day => $loh[0][$i]{day} , status => $loh[0][$i]{status}}; } } } #print Dumper(\%tickets); #ttr for $i ( keys %tickets ) { my ($var1, $var2, $day1, $day2) = ( '0', '0', '0', '0'); if ( $#{$tickets{$i}} == 0 ) # ticket is open with no close state so use todays date # need to check state some exist only as closed in the history table { if ( $tickets{$i}[0]{day} eq "open" ) { $var1=$tickets{$i}[0]{day}; $var1 =~ s/-//g; $day1 = new Date::Business(DATE => $var1 ); $day2 = new Date::Business(); $ddiff = $day2->diffb($day1); $ttr{$i} = $ddiff; } #else start day and end day are equal $ttr{$i} = 0; } elsif ( $#{$tickets{$i}} == 1 ) # We know that there are only 2 states so lets do the math and go to the next ticket { #need to find out if the first state is open or closed #if first state is open then do bellow otherwise we need to do some additional checking if ( $tickets{$i}[0]{status} eq 'open' ) { $var1=$tickets{$i}[0]{day}; $var2=$tickets{$i}[1]{day}; $var1 =~ s/-//g; $var2 =~ s/-//g; $day1 = new Date::Business(DATE => $var1 ); $day2 = new Date::Business(DATE => $var2 ); $ddiff = $day2->diffb($day1); $ttr{$i} = $ddiff; } else { #did it move queues #do sql here my ($sth1, $sth2, @return1, @return2, $sql1, $sql2, $tempval); $sql1="select max(id) from ticket_history where ticket_id = $i"; $sth1 = $dbh->prepare($sql1); $sth1->execute or die "SQL Error: $DBI::errstr\n"; @return1 = $sth1->fetchall_arrayref(); $sql2 = "select b.name from ticket_history a, queue b where a.id=$return1[0][0][0] and a.queue_id=b.id"; $sth2 = $dbh->prepare($sql2); $sth2->execute or die "SQL Error: $DBI::errstr\n"; @return2 = $sth2->fetchall_arrayref(); #check if not do below if ( $return2[0][0][0] eq $queue ) { $var1 = $tickets{$i}[$j]{day}; $var1 =~ s/-//g; $day1 = new Date::Business(DATE => $var1 ); $day2 = new Date::Business(); $ddiff = $day2->diffb($day1); $ttr{$i} += $ddiff; } } } else # More than one complete set of state changes need a cumlative tab of days open { if ( $tickets{$i}[0]{status} eq 'open') { $ttr{$i} = 0; # start with a zero value and add to it my $no_close=0; if ( $#{$tickets{$i}}%2 == 0 ) { # ok the there is no close date $no_close=1; } for $j ( 0 .. $#{$tickets{$i}}) { if ( $j == $#{$tickets{$i}} ) { if ( $no_close == 1 ) #Need to add a db call to see if the ticket was moved into another queue #if ticket was moved use that move date as the end date #else assume ticket is still open { #did it move queues #do sql here my ($sth1, $sth2, @return1, @return2, $sql1, $sql2, $tempval); $sql1="select max(id) from ticket_history where ticket_id = $i"; $sth1 = $dbh->prepare($sql1); $sth1->execute or die "SQL Error: $DBI::errstr\n"; @return1 = $sth1->fetchall_arrayref(); $sql2 = "select b.name from ticket_history a, queue b where a.id=$return1[0][0][0] and a.queue_id=b.id"; $sth2 = $dbh->prepare($sql2); $sth2->execute or die "SQL Error: $DBI::errstr\n"; @return2 = $sth2->fetchall_arrayref(); #check if not do below if ( $return2[0][0][0] eq $queue ) { $var1 = $tickets{$i}[$j]{day}; $var1 =~ s/-//g; $day1 = new Date::Business(DATE => $var1 ); $day2 = new Date::Business(); $ddiff = $day2->diffb($day1); $ttr{$i} += $ddiff; } } else { $var2=$tickets{$i}[$j]{day}; $var2 =~ s/-//g; $day2 = new Date::Business(DATE => $var2 ); $ddiff = $day2->diffb($day1); $ttr{$i} += $ddiff; } } else { if ( $j%2 == 0 ) { $var1 = $tickets{$i}[$j]{day}; $var1 =~ s/-//g; $day1 = new Date::Business(DATE => $var1 ); } else { $var2=$tickets{$i}[$j]{day}; $var2 =~ s/-//g; $day2 = new Date::Business(DATE => $var2 ); $ddiff = $day2->diffb($day1); $ttr{$i} += $ddiff; } } } } } } #print Dumper( \%ttr ); #count the number of keys in %ttr my $total_keys=keys(%ttr); #tickets 1 day or less # 2 days # 3 days # 4 days # 5 days or more my @summary = (0) x 5; for $i ( keys %ttr ) { if ( $ttr{$i} <= 1 ) { ++$summary[0]; } elsif ( $ttr{$i} == 2 ) { ++$summary[1]; } elsif ( $ttr{$i} == 3 ) { ++$summary[2]; } elsif ( $ttr{$i} == 4 ) { ++$summary[3]; } else { ++$summary[4]; } } #set params for tab delimited output change if other my $header="\n"; my $title=""; my $endtitle="\n"; my $linebreak="\n"; my $begintable=""; my $endtable="\n"; my $beginrow=""; my $endrow="\n"; my $delimiter="\t"; my $footer="\n"; if ($format eq 'csv') { $delimiter=","; } elsif ($format eq 'html') { $header="<html>\n"; $title="<h2>"; $endtitle="</h2>\n"; $linebreak="<br>\n"; $begintable="<table border=1 cellspacing=0 cellpadding=10>"; $endtable="</table>\n"; $beginrow="<tr><td>"; $endrow="</td></tr>\n"; $delimiter="</td><td>"; $footer="</html>\n"; } #printable date format for $start and $end my $date_pattern = qr'(\d{4})(\d{2})(\d{2})(\d{2})?(\d{2})?(\d{2})?'; my ($start_year,$start_month,$start_day,$start_hour,$start_minute,$start_second)=(0,0,0,0,0,0); my ($end_year,$end_month,$end_day,$end_hour,$end_minute,$end_second)=(0,0,0,0,0,0); ($start_year,$start_month,$start_day,$start_hour,$start_minute,$start_second)=($start=~m/$date_pattern/); ($end_year,$end_month,$end_day,$end_hour,$end_minute,$end_second)=($end=~m/$date_pattern/); #Normal formatting looks like # # Title = OTRS Time To Resolution for $start to $end # Total tickets = xxxx # # Days to Resolution <1 2 3 4 5> # Total Tickets x x x x x # Percentage of tickets y y y y y # # No headers looks like this with specified delimiters # # start_date end_date total_tickets count_for_<1 count_for_2 .... count_for_5> %_for_<1 %_for_2 %_for_5> if ( $headers == 0 ) { print "$header"; print "$title"."OTRS Time To Resolution for queue $queue $start_month/$start_day/$start_year "; print "to $end_month/$end_day/$end_year "; print "$end_hour\:$end_minute "if (defined $end_minute); print "$endtitle"; print "Total Tickets = $total_keys $linebreak"; print "$linebreak"; print "$begintable"."$beginrow"."Days to Resolution"."$delimiter"; print "$delimiter" if ($format eq 'tab'); print "<1"."$delimiter"."2"."$delimiter"."3"."$delimiter"."4"."$delimiter"."5>"."$endrow"; print "$beginrow"."Total Tickets"."$delimiter"; print "$delimiter"."$delimiter" if ($format eq 'tab'); for $i (0 .. 3) { print "$summary[$i]"."$delimiter"; } print "$summary[4]"."$endrow"; print "$beginrow"."Percentage of Tickets"."$delimiter"; print "$delimiter" if ($format eq 'tab'); for $i (0 .. 3) { printf("%.1f",(($summary[$i]/$total_keys)*100)); print "%"."$delimiter"; } printf("%.1f",(($summary[4]/$total_keys)*100)); print "%"."$endrow"."$endtable"; print "$footer"; } else { print "$beginrow"; print "$start_month/$start_day/$start_year"."$delimiter"."$end_month/$end_day/$end_year"."$delimiter"."$total_keys"."$delimiter"; for $i (0 .. 3) { print "$summary[$i]"."$delimiter"; } print "$summary[4]"."$delimiter"; for $i (0 .. 3) { printf("%.1f",(($summary[$i]/$total_keys)*100)); print "%"."$delimiter"; } printf("%.1f",(($summary[4]/$total_keys)*100)); print "%"."$endrow"; }