[rt-users] Managerial reports

[Justin Killen]

After thinking about this for a bit, I figured the query is in the database somewhere and it's probably easy enough to update via a script/cron job, so I wrote one (Perl):




#!/usr/bin/perl

use strict;
use warnings;
use MIME::Base64;
use DateTime;
use DBI;


my $date = DateTime->now();
my $desired_dow = 6;
$date->subtract(days => ($date->day_of_week - $desired_dow) % 7);

my $to_date_string = $date->ymd;

$date->subtract(days => 6);

my $from_date_string = $date->ymd;

#open(FH, '>', 'attributes.dmp') or die("Unable to open dump file: $!");
#binmode FH;

my $dbh = DBI->connect("DBI:mysql:database=rt4;", "rt_user", "PASSWORDHERE");

my $sth = $dbh->prepare("SELECT * FROM Attributes WHERE id = 33");

my $upSth = $dbh->prepare("UPDATE Attributes SET Content = ? WHERE id = 33");

$sth->execute;

while(my $ref = $sth->fetchrow_hashref()) {
        my $content = decode_base64($ref->{'Content'});

#       print FH "$content\n\n";

        if($content =~ /(.*Resolved >= ')(\d{1,4}-\d{1,2}-\d{1,2})(' AND Resolved <= ')(\d{1,4}-\d{1,2}-\d{1,2})('.*)/s) {
                my ($pre, $date1, $mid, $date2, $post) = ($1, $2, $3, $4, $5);

                if($date1 ne $from_date_string || $date2 ne $to_date_string) {
                        my $new_content = $pre . $from_date_string . $mid . $to_date_string . $post;

                        my $encoded_value = encode_base64($new_content);

#                       print FH $encoded_value;

                        $upSth->execute($encoded_value);
                }
        }
}

close FH;


It will update my saved search (id# 33) to always been sun-sat of last week.  It'd admittedly a bit of a hack; it would be nice if altering an existing search was an option within the REST API.

-Justin
> -----Original Message-----
> From: Christian Loos [mailto:cloos at netcologne.de]
> Sent: Wednesday, May 07, 2014 12:00 AM
> To: Justin Killen; rt-users at lists.bestpractical.com
> Subject: Re: Managerial reports
> 
> Am 05.05.2014 20:39, schrieb Justin Killen:
> > Often times in IT, issues will come into support staff and resolved
> > without management ever knowing about it.  I have been tasked with
> > providing management with a report that would show tickets closed within
> > a certain date range, preferably restricted to a specific user.
> >
> >
> >
> > Are there any existing extensions/reports that would fit this need?  I
> > tried Activity Reports, but the Activity Detail report has quite a lot
> > of static in it, and the ticket Title doesn't appear anywhere.  I think
> > RTx::Statistics might works as well, but it doesn't seem to be supported
> > within RT 4.2 yet.
> >
> >
> >
> > Any suggestions?
> >
> >
> >
> > -Justin
> 
> 
> http://bestpractical.com/docs/rt/4.2/dashboards.html