[rt-users] Query in script not working

[Roy El-Hames]

Hi Mathew;

Not sure if you are aware of it, but in case you did not RT gives you a 
very powerful Date functions in Date.pm,
from a perl script you can do the below:

use RT::Date;
my $date = new RT::Date($RT::SystemUser);
$date->SetToNow();
my $now = $date->ISO;
$date->GoBackDays(1);
my $yesterday = $date->ISO;

You can also set to midnight, change zone etc ..look up lib/RT/Date.pm

Roy


Mathew Snyder wrote:
> FWIW, this is the script I came up with:
>
> #!/usr/bin/perl
>
> ###########################################
> #  Name:         daily_transactions.pl
> #  Version:      0.1
> #  Author:       Mathew Snyder
> #  Date:         February 24, 2008
> #  Comments: This is a script which lists
> #                        how many transactions are
> #                        performed per hour.
> ##########################################
>
> use warnings;
> use strict;
> use lib '/usr/local/rt3/lib';
> use lib '/usr/local/rt3/local/lib';
> use lib '/usr/local/lib';
> use RT;
> use RT::Tickets;
> use RT::Users;
> use Date::Parse;
> use Reports::Emails;
> use MIME::Lite;
>
> RT::LoadConfig();
> RT::Init();
>
> my (%userID,%transCount);
> my @date = (localtime(time - 86400))[3 .. 5];
> my $yesterday = join "-", ($date[2] + 1900, (sprintf '%02d',$date[1] + 1),
> (sprintf '%02d', $date[0]));
>
> my $tix = new RT::Tickets(RT::SystemUser);
> $tix->FromSQL("(Queue = 'CustomerCare' OR Queue = 'TechOps') AND LastUpdated =
> '" . $yesterday . "'");
>
> my $users = new RT::Users(RT::SystemUser);
> $users->LimitToPrivileged;
>
> while (my $user = $users->Next) {
>         next if $user->Name eq 'root' || $user->Name eq 'RT_System';
>         $userID{$user->Name} = undef;
> }
>
> while (my $ticket = $tix->Next) {
>         my $transactions = $ticket->Transactions;
>         while (my $transaction = $transactions->Next) {
>                 my $creator = $transaction->CreatorObj;
>                 my $user = $creator->Name;
>                 next unless exists($userID{$user});
>                 next if $transaction->Creator == '1';
>                 (my $tranDate = $transaction->Created) =~ s/\s.*$//;
>                 next unless $tranDate eq $yesterday;
>                 my $tranTime = (str2time($transaction->Created) - 18000);
>                 my $tranHour = (localtime($tranTime))[2];
>                 my $hour = sprintf("%02d", $tranHour);
>                 $hour .= "00";
>                 $transCount{$hour} += 1;
>         }
> }
>
> open TRANSLOG, ">/work_reports/daily/transaction_report_$yesterday.txt";
> print TRANSLOG "Report of the hourly ticket transactions committed on
> $yesterday\n\n";
> printf TRANSLOG "%15s\n", "Trans";
> printf TRANSLOG "%6s%9s\n", "Hour", "Count";
>
> foreach my $time (sort keys %transCount) {
>         printf TRANSLOG "%6s%7s\n", $time, $transCount{$time};
> }
>
> close TRANSLOG;
>
> my $emailSubj = "Ticket Transaction Count for $yesterday";
> my $emailMsg  = "Attached is a log of the transactions committed per hour
> yesterday.  If an hour does not appear it is safe to assume no transactions were
> created during that time.";
>
> # Prepare and send the email which with the report to all necessary parties.
> my $fullEmail    = new MIME::Lite(From          => $emailFrom,
>                                                                   To
> => 'user at company.com',
>                                                                   Bcc
> => 'user2 at company.com',
>                                                                   Subject
> => $emailSubj,
>                                                                   Type
> => "multipart/mixed");
>
> $fullEmail->attach(Type => "TEXT",
>                                    Data => $emailMsg);
>
> $fullEmail->attach(Type                 => "text/plain",
>                                    Path                 =>
> "/work_reports/daily/transaction_report_$yesterday.txt",
>                                    Disposition  => "attachment");
>
> $fullEmail->send("sendmail", "/usr/sbin/sendmail -t");
>
> Stephen Turner wrote:
>   
>> Quoting Mathew Snyder <mathew.snyder at gmail.com>:
>>
>>     
>>> I've got the dates working.  However, the tickets retrieved are still
>>> based on
>>> the day starting at 05:00:00 and ending the following morning at the
>>> same time.
>>> How can I tell it to only return tickets from between 00:00:00 and
>>> 23:59:00?
>>>
>>> Mathew
>>>
>>>
>>>       
>> You are actually getting the tickets you want - the dates are stored in the
>> database as GMT and so appear different by 5 hours from what you see on the
>> screen. It looks a bit strange, but it works fine.
>>
>> BTW - what was the problem that you fixed with the dates?
>>
>> Steve
>>     
>
>