Managerial reports

Justin_Killen · May 5, 2014, 6:39pm

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

Marko_Cupac2 · May 6, 2014, 10:19am

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.

I am using advanced search to find all tickets resolved in certain
period (one month), and am formatting it according to my needs.

Query:
Resolved > ‘2014-04-01’ AND Resolved < ‘2014-05-01’

Formatting:
‘id’,
‘CustomField.{Kompanija}’,
‘Subject’,
‘Requestors’,
‘CreatedBy’,
‘Created’,
‘OwnerName’,
‘Resolved’,
‘TimeWorked’

I am downloading this as .tsv, importing it into spreadsheet and
sending to my manager once a month.
Marko Cupać

Loos_Christian · May 7, 2014, 6:59am

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

Justin_Killen · May 7, 2014, 7:42pm

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

Loos_Christian · May 7, 2014, 8:24pm

This isn’t necessary as RT search supports relative dates:

Chris

Christian Loos
Smallworld GIS Operations
NETCOLOGNE Gesellschaft für Telekommunikation mbH
Am Coloneum 9 | 50829 Köln
Tel: 0221 2222-276 | Fax: 0221 2222-7276 | Mobil: 0177 8888276

www.netcologne.de http://www.netcologne.de/

[NetCologneGmbH] Geschäftsführung:
Jost Hermanns
Mario Wilhelm
Vorsitzender des Aufsichtsrates:
Dr. Andreas Cerbe
HRB 25580, AG Köln

Diese Nachricht (inklusive aller Anhänge) ist vertraulich. Sollten Sie diese Nachricht versehentlich erhalten haben,
bitten wir, den Absender (durch Antwort-E-Mail) hiervon unverzüglich zu informieren und die Nachricht zu löschen.
Die E-Mail darf in diesem Fall weder vervielfältigt noch in anderer Weise verwendet werden.From: Justin Killen [jkillen@allamericanasphalt.commailto:jkillen@allamericanasphalt.com]
Sent: Wednesday, May 07, 2014 09:42 PM Mitteleuropäische Zeit
To: Loos, Christian; rt-users@lists.bestpractical.com
Subject: RE: Managerial reports

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@netcologne.de]
Sent: Wednesday, May 07, 2014 12:00 AM
To: Justin Killen; rt-users@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

Dashboards - RT 4.2.17 Documentation - Best Practical